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.