Select the product you need help with

FIX: Inefficient Use of LIKE Clause with Jet OLEDB Provider

Article ID: 311159 - View products that this article applies to.

This article was previously published under Q311159

SYMPTOMS

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.

Back to the top | Give Feedback

RESOLUTION

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://support.microsoft.com/kb/239114/ )

How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

Back to the top | Give Feedback

STATUS

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).

Back to the top | Give Feedback

MORE INFORMATION

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.

Back to the top | Give Feedback

Properties

Article ID: 311159 - Last Review: October 12, 2005 - Revision: 1.4

APPLIES TO

Microsoft OLE DB Provider for Jet 4.0

kbhotfixserver kbqfe kbbug kbfix kbqfe KB311159

Retired KB Content Disclaimer

This 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.

Back to the top | Give Feedback