Article ID: 252883 - View products that this article applies to.
This article was previously published under Q252883
This article has been archived. It is offered "as is" and will no longer be updated.
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.
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:
The only resolution to this problem is to use a stored querydef query instead of prepared queries.
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
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:
(https://support.microsoft.com/kb/256986/EN-US/ )Description of the Microsoft Windows Registry
Steps to Reproduce Behavior
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/240412/EN-US/ )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".
Article ID: 252883 - Last Review: January 10, 2015 - Revision: 1.0