Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 311159 - Last Review: October 12, 2005 - Revision: 1.4
FIX: Inefficient Use of LIKE Clause with Jet OLEDB Provider
This article was previously published under Q311159
When you execute a SQL query using the Microsoft Jet OLEDB Provider 4.0 and you apply a LIKE clause to a linked SQL Server table, the LIKE clause is not properly forwarded by the Microsoft Jet query processor to SQL Server. This results in a less efficient overall query, because all of the rows are first retrieved from SQL Server locally by Jet.
Note that after all rows are retrieved locally by Jet, the LIKE clause is applied internally to the final result before sending the result back to the client, so the final result is the same set of rows.
To resolve this problem, install the latest Microsoft Jet 4.0 service pack.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
239114Â
(http://kbalertz.com/Feedback.aspx?kbNumber=239114/
)
How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was fixed in Jet 4.0 Service Pack 6 (SP6).
Steps to Reproduce Behavior
- Create a new Microsoft Access database named "C:\DB1.MDB".
- Open C:\DB1.MDB in Microsoft Access and create a linked table to the authors table located in the pubs database on your SQL Server server. Keep the default linked table name of dbo_authors.
- Run the following query using the Microsoft Jet OLEDB Provider 4.0 against the Access database C:\DB1.MDB.
SELECT * from dbo_authors WHERE state LIKE 'M%'
If you monitor the back-end SQL Server server using the SQL Server Profiler utility, you will see that the following SQL statements are sent to SQL Server, indicating that the Microsoft Jet query optimizer is applying the LIKE criteria after retrieving all of the rows from the
authors table. Note that all noncritical SQL statements have been removed, and comments have been added to describe the various SQL statements sent by Microsoft Jet.
-- This is sent first by Jet to fetch all key values.
-- Note that WHERE state LIKE 'M%' is not applied here.
SELECT au_id, state FROM authors
-- This retrieves a single row using the first key value retrieved by the
-- above SQL statement. Jet will apply the LIKE clause internally
-- before retrieving the first row.
SELECT au_id,au_lname,au_fname,phone,address,city,state,zip,contract
FROM dbo.authors WHERE au_id = @P1
If you run the identical SQL query using the Microsoft Access ODBC Driver, the LIKE clause is properly forwarded to SQL Server in the first SQL statement, resulting in a much more efficient overall query.
APPLIES TO
- Microsoft OLE DB Provider for Jet 4.0
| kbhotfixserver kbqfe kbbug kbfix kbqfe KB311159 |
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate