BUG: Parameterized Queries with JET OLE DB Provider May Run Slow (252883)


The information in this article 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.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

240412 HOWTO: ExecuteParameterized Command Multiple Times with ATL OLEDB Consumer Templates



For more information on how to enable JET's debugging functions search MSDN for "JETSHOWPLAN".
Modification Type:MajorLast Reviewed:10/15/2002
Keywords:kbBug kbDatabase kbDSupport kbJET KB252883
©2002 Microsoft Corporation. All rights reserved.