Why would opening a database with DAO first making access with ODBC or OleDB much faster?

Viewed 92

I have been following this tutorial on MSDN to access the sample Northwind Access 97 and Access 2000 databases.

The code is mostly from the tutorial and relatively complex to re-create here, but my question is easy to state. Opening and closing the database first with DAO speeds up subsequent access using ODBC and OleDB by ~20-fold...why?

Test 1: Repeatedly run (200 times) the following SQL query against the Access 97 and Access 2000 databases, reading all records in the returned data:

"SELECT ProductID, UnitPrice, ProductName FROM Products " +
"WHERE UnitPrice > ? " +
"ORDER BY UnitPrice DESC;"

Results (ODBC) 1:

  • Access 97: 200 cycles, reading 75 records per cycle took 22875ms
  • Access 2000: 200 cycles, reading 75 records per cycle took 12125ms

Results (OleDb) 1:

  • Access 97: 200 cycles, reading 75 records per cycle took 21656ms
  • Access 2000: 200 cycles, reading 75 records per cycle took 11578ms

Now make the following change to the code:

private void OpenDbWithDAO(string strDatabase)
{
    // Dummy open and close the target database...
    DAO.DBEngine dbEngine = new DAO.DBEngine();
    DAO.Database db = dbEngine.OpenDatabase(strDatabase, false, false);
    db.Close();
    // This consumes ~15ms for each database opened
}

Test 2: Exactly the same as test 1, except first call OpenDbWithDAO(PATH).

Results (ODBC) 2:

  • Access 97: 200 cycles, reading 75 records per cycle took 922ms
  • Access 2000: 200 cycles, reading 75 records per cycle took 610ms

Results (OleDb) 2:

  • Access 97: 200 cycles, reading 75 records per cycle took 625ms
  • Access 2000: 200 cycles, reading 75 records per cycle took 390ms

Is this normal?

UPDATE

Added timing for the dummy open/close of the database. For both the Access 97 and 2000 databases, the time to open/close the databases is ~30ms.

0 Answers
Related