FIX: Inefficient Use of LIKE Clause with Jet OLEDB Provider (311159)


The information in this article applies to:

  • Microsoft OLE DB Provider for Jet 4.0
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.

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 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

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

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Microsoft Access database named "C:\DB1.MDB".
  2. 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.
  3. 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.
Modification Type:MinorLast Reviewed:10/12/2005
Keywords:kbHotfixServer kbQFE kbbug kbfix kbQFE KB311159
©2004 Microsoft Corporation. All rights reserved.