Non-Jet OLEDB backends?

[Riley DeWiley]

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to
retarget. I do not want to rewrite my source code. I do not want to pay
royalties. I want better performance. The application is single user, local
access only, but needs high performance. Fancy features are not required in
general ...

Can anyone recommend a new backend?

RDeW

[Rick Brandt]

"Riley DeWiley" <ri***********@gmail.com> wrote in message
news:10*************@corp.supernews.com...

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to retarget. I do not want to rewrite my source code. I do not want to pay
royalties. I want better performance. The application is single user, local access only, but needs high performance. Fancy features are not required in general ...

Can anyone recommend a new backend?

In that environment I doubt that you would find *anything* faster than Jet.
Perhaps you should re-examine the design of your data structures, queries,
and indexes. Performance problems are nearly always due to poor design,
not the data engine, and while there are many valid reasons to dump Jet in
favor of another data engine, performance is rarely near the top of that
list.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Steve Jorgensen]

Too slow locally, with a single user, or too slow over a LAN or with many
users. If it's local with a single user, JET is just as fast as most any
database you'll find. If you want something else that will be fast shared
with multiple users, I recommend PostgreSQL or Firebird. If the OLEDB drivers
are not yet stable for these, using the OLEDB provider for ODBC, and using the
ODBC drivers work fine. I've done that with PostgreSQL on several occasions.

On Mon, 1 Nov 2004 02:13:08 -0800, "Riley DeWiley" <ri***********@gmail.com>
wrote:

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to
retarget. I do not want to rewrite my source code. I do not want to pay
royalties. I want better performance. The application is single user, local
access only, but needs high performance. Fancy features are not required in
general ...

Can anyone recommend a new backend?

RDeW

[Nick Landsberg]

Riley DeWiley wrote:

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to
retarget. I do not want to rewrite my source code. I do not want to pay
royalties. I want better performance. The application is single user, local
access only, but needs high performance. Fancy features are not required in
general ...

Can anyone recommend a new backend?

RDeW

To add to what others have mentioned ...

Some questions:

What performance is required? What is it today?
(Specific numbers, not just "it seems slower than
it should be.")

First approximation: do you have the appropriate
indices set?

Second approximation:

In my experience, perceived performance problems
are hardly ever due to any back-end DBMS, but are
probably due to the access code doing something
"unwise." For example:

- An application which issued 44 queries to populate
44 fields on a screen.
- Another application which queried the meta-data
to find out the field names and attributes and then
formulated the SQL on the fly to query for the
values.

--
"It is impossible to make anything foolproof
because fools are so ingenious"
- A. Bloch

[Riley DeWiley]

The big problem is in inserting into a junction table, J, that joins two
other tables A and B.
I enforce referential integrity through the join from both sides. I don't
really need to. Will I save a lot of perf by turning it off?

When the junction table approaches about 1 million records, the act of
inserting a new record into it seems to take almost 0.25 - 0.50 seconds per
record.

I have previously noticed massive size bloat with this database, which I cut
down at runtime with frequent compact+repair operations. By "massive" I mean
the database grows to 1.5 gig, after compaction, it is about 40 megs.

Must go but will send more info later as needed.

Thanks to all for assistance.

RDeW
"Nick Landsberg" <SP*************@SPAMworldnetTRAP.att.net> wrote in message
news:00******************@bgtnsc05-news.ops.worldnet.att.net...

Riley DeWiley wrote:

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to retarget. I do not want to rewrite my source code. I do not want to pay
royalties. I want better performance. The application is single user, local access only, but needs high performance. Fancy features are not required in general ...

Can anyone recommend a new backend?

RDeW

To add to what others have mentioned ...

Some questions:

What performance is required? What is it today?
(Specific numbers, not just "it seems slower than
it should be.")

First approximation: do you have the appropriate
indices set?

Second approximation:

In my experience, perceived performance problems
are hardly ever due to any back-end DBMS, but are
probably due to the access code doing something
"unwise." For example:

- An application which issued 44 queries to populate
44 fields on a screen.
- Another application which queried the meta-data
to find out the field names and attributes and then
formulated the SQL on the fly to query for the
values.

--
"It is impossible to make anything foolproof
because fools are so ingenious"
- A. Bloch

[Tony Toews]

"Riley DeWiley" <ri***********@gmail.com> wrote:

The big problem is in inserting into a junction table, J, that joins two
other tables A and B.
I enforce referential integrity through the join from both sides. I don't
really need to. Will I save a lot of perf by turning it off?
Yes, you would save some performance. Access automatically creates indexes when a
relationship is created.
When the junction table approaches about 1 million records, the act of
inserting a new record into it seems to take almost 0.25 - 0.50 seconds per
record.
Given such high volumes I'd suggest removing all indexes and then adding them back
with done.
I have previously noticed massive size bloat with this database, which I cut
down at runtime with frequent compact+repair operations. By "massive" I mean
the database grows to 1.5 gig, after compaction, it is about 40 megs.

Ah, yes, that problem. This seems to be more of a problem with Jet 4.0 than it is
with Jet 3.5 for reasons known only to Microsoft. One thing to double check is that
you are running the latest version of msjet40.dll. The two latest versions are

Jet 4.0 SP 8 - 4.0.8015.0
Microsoft Security Bulletin MS04-014 - 4.0.8618.0

This has been mentioned a few times in the newsgroups but I don't recall any
solutions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[david epsom dot com dot au]

Generally speaking, any dBase compatible system (for example,
CodeBase, http://www.sequiter.com/) will tend to be faster
than Jet. This is for three reasons: 1) Each table and index
is a separate file. This means that when you really want to
deal with just one table, the file is smaller. 2) The records
are fixed length. 3) The system only uses direct locking,
instead of opening a lock file.

But if you want top performance, why are you using OLEDB?

In any case, you need to sort out your indexing and your
bloating.

And of course you need to review your database actions,
just to be sure that you aren't accidentally doing anything
like connecting in shared mode to a full-table recordset
every time you want to do an append :~)

(david)

PS - please post back and let us know how you get on :~)

"Riley DeWiley" <ri***********@gmail.com> wrote in message
news:10*************@corp.supernews.com...

The big problem is in inserting into a junction table, J, that joins two
other tables A and B.
I enforce referential integrity through the join from both sides. I don't
really need to. Will I save a lot of perf by turning it off?

When the junction table approaches about 1 million records, the act of
inserting a new record into it seems to take almost 0.25 - 0.50 seconds per record.

I have previously noticed massive size bloat with this database, which I cut down at runtime with frequent compact+repair operations. By "massive" I mean the database grows to 1.5 gig, after compaction, it is about 40 megs.

Must go but will send more info later as needed.

Thanks to all for assistance.

RDeW
"Nick Landsberg" <SP*************@SPAMworldnetTRAP.att.net> wrote in message news:00******************@bgtnsc05-news.ops.worldnet.att.net...

Riley DeWiley wrote:

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to retarget. I do not want to rewrite my source code. I do not want to pay royalties. I want better performance. The application is single user, local access only, but needs high performance. Fancy features are not

required

in general ...

Can anyone recommend a new backend?

RDeW

To add to what others have mentioned ...

Some questions:

What performance is required? What is it today?
(Specific numbers, not just "it seems slower than
it should be.")

First approximation: do you have the appropriate
indices set?

Second approximation:

In my experience, perceived performance problems
are hardly ever due to any back-end DBMS, but are
probably due to the access code doing something
"unwise." For example:

- An application which issued 44 queries to populate
44 fields on a screen.
- Another application which queried the meta-data
to find out the field names and attributes and then
formulated the SQL on the fly to query for the
values.

--
"It is impossible to make anything foolproof
because fools are so ingenious"
- A. Bloch

[Larry Linson]

If you are using classic ADO (ActiveX Data Objects) and want to improve
performance in a single-user, local-access-only environment, you can easily
replace Jet with the MSDE (Microsoft Data Engine, now called Microsoft SQL
Server Desktop Edition) that is included with Microsoft Visual C++. You may
want to obtain the less-than-US$50 Developer version of SQL Server to take
advantage of its excellent administrative tools during development -- those
are not included with the free MSDE. Like Jet, MSDE is limited to 2 GB of
data, and is purposely limited in the number of concurrent batch processes
that can be active without delays being inserted. But the good news is that,
if those limitations are a problem, there will be little (more likely no)
work required to replace MSDE with full SQL Server.

You have gotten some excellent advice here on improving the way you access
the database before changing the engine. No matter what database engine you
use, "novice" approaches to design of your database and implementation of
access can bring the database engine to its knees. Thoughtful (and
therefore, good) database design and design of the code that accesses the
data can make vast (not just half-vast) improvements.

Larry Linson
Microsoft Access MVP
"Riley DeWiley" <ri***********@gmail.com> wrote in message
news:10*************@corp.supernews.com...

I am developing in c++, OLEDB, and targeting the Jet backend. I have
realized late in the project that the Jet backend is too slow, and I need to retarget. I do not want to rewrite my source code. I do not want to pay
royalties. I want better performance. The application is single user, local access only, but needs high performance. Fancy features are not required in general ...

Can anyone recommend a new backend?

RDeW

[Riley DeWiley]

Comments in the text
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:p5********************************@4ax.com...

"Riley DeWiley" <ri***********@gmail.com> wrote:

The big problem is in inserting into a junction table, J, that joins two
other tables A and B.
I enforce referential integrity through the join from both sides. I don't
really need to. Will I save a lot of perf by turning it off?
Yes, you would save some performance. Access automatically creates

indexes when a relationship is created.

When the junction table approaches about 1 million records, the act of
inserting a new record into it seems to take almost 0.25 - 0.50 seconds perrecord.
Given such high volumes I'd suggest removing all indexes and then adding

them back with done.

Can you expand on this a bit? The junction table comprises three fields of
32 bit integers. Two are used as keys into other tables, the third is a
count. The table is indexed with the two keys combined into one unique key.
Are you suggesting dropping the index temporarily? Can this even be done? I
can assume that I will make only one insertion or update per unique record
"at a pass", that is, I insert or update, say, 500 *different* records
at-a-shot.

I am an experienced windows programmer but something of a database naif ....
I suppose it shows ...

[Tony Toews]

"Riley DeWiley" <ri***********@gmail.com> wrote:

When the junction table approaches about 1 million records, the act of
inserting a new record into it seems to take almost 0.25 - 0.50 seconds per
record.

I have previously noticed massive size bloat with this database, which I cut
down at runtime with frequent compact+repair operations. By "massive" I mean
the database grows to 1.5 gig, after compaction, it is about 40 megs.

BTW does performance improve after you compact the database?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"Riley DeWiley" <ri***********@gmail.com> wrote:

Comments in the text
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:p5********************************@4ax.com.. .

"Riley DeWiley" <ri***********@gmail.com> wrote:

>The big problem is in inserting into a junction table, J, that joins two
>other tables A and B.
>I enforce referential integrity through the join from both sides. I don't
>really need to. Will I save a lot of perf by turning it off?
Yes, you would save some performance. Access automatically creates

indexes when a

relationship is created.

>When the junction table approaches about 1 million records, the act of
>inserting a new record into it seems to take almost 0.25 - 0.50 secondsper >record.

Given such high volumes I'd suggest removing all indexes and then adding

them back

with done.

Can you expand on this a bit? The junction table comprises three fields of
32 bit integers. Two are used as keys into other tables, the third is a
count. The table is indexed with the two keys combined into one unique key.
Are you suggesting dropping the index temporarily? Can this even be done?

Yes, you can go throught the indexes collection and delete the appropriate indexes.
However you'd also have to delete the relationships for that table too as
relationships automatically have indexes.

Also this assumes you are the only user in the MDB or that table. Otherwise if the
table is in use you couldn't delete the indexes or relationships.
I
can assume that I will make only one insertion or update per unique record
"at a pass", that is, I insert or update, say, 500 *different* records
at-a-shot.

Ah, ok. I had assumed that you were adding a million records at a time. If you
are adding only 500 records at a time then deleting/recreating indexes would likely
take a lot longer.

FWIW MSDE is essentially SQL Server Lite and may give you better performance at this
somehigh high volume of records.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote:

And of course you need to review your database actions,
just to be sure that you aren't accidentally doing anything
like connecting in shared mode to a full-table recordset
every time you want to do an append :~)
Excellent point.
PS - please post back and let us know how you get on :~)

Agreed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Riley DeWiley]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:55********************************@4ax.com...

"Riley DeWiley" <ri***********@gmail.com> wrote:

I have previously noticed massive size bloat with this database, which I cutdown at runtime with frequent compact+repair operations. By "massive" I meanthe database grows to 1.5 gig, after compaction, it is about 40 megs.
BTW does performance improve after you compact the database?

Tony

MMmmmm ... seems to, but I have no data, and couldn't swear to it. Why? --
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm