I'm evaluating the query performance against Jet DB.

I have compared two different DB environments:

1) Jet 3.51 Driver against Access database (either 97 or 2000)
2) Jet 4.0 Driver against Access database (either 97 or 2000)

I noticed that the DB performance for Jet 4.0 Engine was much slower
than for Jet 3.51 Engine (i.e. Jet 4.0 - 40 seconds while Jet 3.51 - 11
seconds). The DB performance against Access 97 or Access 2000 didn't
make any difference. Only the Jet Engine caused the performance
differences.

Do you know why Jet 4.0 is slower? What should I do in optimizing DB
performance?

Thank you, Jake

Jet 4.0 implemented Unicode. This means the DB is roughly twice as large for
the same amount of data, it takes up twice as much RAM, it takes twice as
long to fetch from disk and the string resolutions take twice as long to
parse--or there abouts. Does this answer your question?

--
William (Bill) Vaughn
President Beta V Corporation
www.betav.com

There are many possible reasons for poor performance with Jet 4.0:

1. Check the DSN
If your data source name (DSN) was created with an older version of the
Microsoft Jet ODBC driver, the default buffer size may still be 512 KB. The
default buffer size of Jet 4.0 is 2048 KB and with anything less than that,
your application may be spending most of its time swapping virtual memory.

2. Is the database (.mdb) file on a network share?

The Microsoft Access ODBC Driver that ships with MDAC 2.1 or later
interprets the PageTimeout setting in milliseconds, whereas the MDAC 2.0
versions of the Access ODBC Driver interpret PageTimeout in seconds. Modify
the default value of PageTimeout from 5 to 5000 when creating the DSN, or
modify the value through the connection string. When the database file is
placed on a network share, having a low timeout value (such as 5
milliseconds) causes excessive network traffic and can degrade the
performance. For additional information, click the article number below to
view the article in the Microsoft Knowledge Base:

http://support.microsoft.com/support/kb/articles/Q246/5/60.ASP

3. Is the database in Access 9x format?

The Jet 4.0 engine uses Unicode strings internally. Because Access 9x
databases are not Unicode, the new Jet driver is slowed due to the
conversions on all strings coming from and going to the Jet database.
Because Access 2000 databases store strings in Unicode, no conversion is
necessary; for optimum performance, convert all databases to Access 2000.

4. Are you certain that your database is not damaged?

Although a damaged database may be able to open successfully, data access
is much slower due to damaged indexes or table rows. Run the Repair and
Compact Database utilities in the Microsoft Access development environment
to repair a damaged database. Programmatically, you can use the Jet and
Replication Objects (JRO) method CompactDatabase, the Jet OLE DB provider's
IJetCompact::Compact method, or the Data Access Objects (DAO)
CDaoDatabase::CompactDatabase method to accomplish this.

For more other reasons, please check the following article:
http://support.microsoft.com/support/kb/articles/Q240/4/34.asp.

Regards,