Microsoft KB Archive/252883

= BUG: Parameterized Queries with JET OLE DB Provider May Run Slow =

Article ID: 252883

Article Last Modified on 10/15/2002

-

APPLIES TO


 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q252883



SYMPTOMS
Running prepared queries on an Access database using OLE DB may be significantly slower than running the same code using ODBC. Note that this does not apply to stored QueryDefs queries that are a part of the Access database.



CAUSE
One of the advantages of using prepared queries is that the query is compiled only once by the Microsoft Jet database engine. Further executions are very fast since Jet has already generated an execution plan for the query. However, the OLE DB provider for Jet doesn't take advantage of this optimization. Instead, it submits the prepared query to be re-compiled each time you re-query. When you use OLE DB, this requery is achieved in the following three steps:


 * 1) Call IRowset::Close to close the rowset returned by ICommand::Execute.
 * 2) Change the parameter data.
 * 3) Call ICommand::Execute to create a new rowset based on the new parameters provided in step 2.



RESOLUTION
The only resolution to this problem is to use a stored querydef query instead of prepared queries.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Steps to Reproduce Behavior

 * 1) Create two similar projects, one with OLE DB and another with ODBC to run parameterized queries on an Access database.
 * 2) Add several iterations of re-querying to your projects.
 * 3) Enable the JET's ShowPlan debugging function JETSHOWPLAN in the registry.
 * 4) Run each project with JETSHOWPLAN enabled and then open the resulting log file in Notepad.

Notice that in the case of ODBC, the query is compiled only once and therefore only one execution plan is found in the log file. In case of OLE DB, multiple execution plans are found.

