The query engine has eight major components:
The algorithms that the optimizer uses depend on the accuracy of the statistics provided by the underlying engine. For example, the statistics that some ODBC drivers return may not be accurate. This can cause the optimizer to choose a less-than-optimal execution plan. However, if the whole query is sent to the ODBC server for processing (the usual case), the optimizer’s execution plan is irrelevant.
In the native Jet database engine, statistics can become out of date over time. Statistics become out of date if transactions are performed and then rolled back, or if your machine is turned off before the database is closed. The problem in the latter situation occurs because the statistics are cached in memory, and shutting off the machine without closing the database doesn’t allow the statistics to be written out to disk.
To update the statistics in a Microsoft Jet database, you must compact the database. Compacting the database may also speed up queries because the process writes all the data in tables in contiguous pages, which makes scanning sequential pages much faster than when the database is fragmented.