Query performance decreases when the criteria in a Microsoft JET 4.0 query contains a user-defined function (821936)

MORE INFORMATION

Steps to Reproduce the Problem

1. Create a new Microsoft Access 2002 database (.mdb) with a table that is linked to the Dbo.sales table in the Pubs database on the computer that is running Microsoft SQL Server.
   
   Notice that Access names the linked table dbo_sales.
2. Create a new module, and then paste the following procedures in the module:

   Function EnterQuantity(intVal As Integer)
       EnterQuantity = intVal
   End Function
   
   Function EnterQuantity2(intVal As Integer) As Integer
       EnterQuantity2 = intVal
   End Function
   

3. Save the module as Module1, and then close the Microsoft Visual Basic Editor.
4. Create a new query that is named qryNoReturnType.
5. In Microsoft SQL Editor, add the following statement:

   SELECT dbo_sales.stor_id, dbo_sales.qty
   FROM dbo_sales
   WHERE (((dbo_sales.stor_id)="6380") AND ((dbo_sales.qty)=EnterQuantity(5)));
   

6. To turn on ODBC tracing, follow these steps:
   1. Click Start, click Run, type odbcad32.exe in the Open box, and then click OK.
   2. In the ODBC Data Source Administrator dialog box, click the Tracing tab, and then click Start Tracing Now.
7. Run the qryNoReturnType query.
8. To stop ODBC tracing, follow these steps:
   1. In the ODBC Data Source Administrator dialog box, click the Tracing tab.
   2. Click Stop Tracing Now.
9. Examine the tracing log file.
   
   Notice that the query is not optimized because the criteria for the Quantity field is not passed to the driver:

   "SELECT "stor_id","qty","dbo"."sales"."stor_id","dbo"."sales"."ord_num","dbo"."sales"."title_id" 
   FROM "dbo"."sales" 
   WHERE ("stor_id" = '6380' ) \ 0"
   

10. Create a new qryWithReturnType query.
11. In the SQL Editor, add the following statement:

    SELECT dbo_sales.stor_id, dbo_sales.qty
    FROM dbo_sales
    WHERE (((dbo_sales.stor_id)="6380") AND ((dbo_sales.qty)=EnterQuantity2(5)));
    

12. Start ODBC tracing. Run the qryWithReturnType query.
13. To stop ODBC tracing, follow these steps:
    1. In the ODBC Data Source Administrator dialog box, click the Tracing tab.
    2. Click Stop Tracing Now.
14. Examine the tracing log file.
    
    The criteria for the Quantity field is passed to the driver as follows:

    "SELECT "dbo"."sales"."stor_id","dbo"."sales"."ord_num","dbo"."sales"."title_id" 
    FROM "dbo"."sales" 
    WHERE (("stor_id" = '6380' ) AND ("qty" =  ? ) ) \ 0"