Microsoft KB Archive/821936

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

Article ID: 821936

Article Last Modified on 8/6/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition

-





SYMPTOMS
The performance of a query that is run against an Open Database Connectivity (ODBC) data source may significantly decrease if the query contains a user-defined function.



CAUSE
This problem may occur when all the following conditions are true:
 * The query is run against an ODBC data source.
 * The query contains a user-defined function in the WHERE clause.
 * The function returns a different data type than the data type of the column that the function is compared to.



WORKAROUND
To work around this problem, make the user-defined function return the same data type as the column in the WHERE clause. If you do not declare the data type, the function returns the default Variant data type. For example, the following function returns the default Variant data type: Function EnterQuantity(intVal As Integer) EnterQuantity = intVal End Function To modify the default Variant data type to Integer, use the following function: Function EnterQuantity(intVal As Integer) As Integer EnterQuantity = intVal End Function



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section of this article.



Steps to Reproduce the Problem
 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.  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  Save the module as Module1, and then close the Microsoft Visual Basic Editor. Create a new query that is named qryNoReturnType.  In Microsoft SQL Editor, add the following statement: SELECT dbo_sales.stor_id, dbo_sales.qty FROM dbo_sales WHERE (((dbo_sales.stor_id)=&quot;6380&quot;) AND ((dbo_sales.qty)=EnterQuantity(5))); </li> To turn on ODBC tracing, follow these steps: <ol style="list-style-type: lower-alpha;"> Click Start, click Run, type odbcad32.exe in the Open box, and then click OK.</li> In the ODBC Data Source Administrator dialog box, click the Tracing tab, and then click Start Tracing Now.</li></ol> </li> Run the qryNoReturnType query.</li> To stop ODBC tracing, follow these steps: <ol style="list-style-type: lower-alpha;"> In the ODBC Data Source Administrator dialog box, click the Tracing tab.</li> Click Stop Tracing Now.</li></ol> </li>  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: &quot;SELECT &quot;stor_id&quot;,&quot;qty&quot;,&quot;dbo&quot;.&quot;sales&quot;.&quot;stor_id&quot;,&quot;dbo&quot;.&quot;sales&quot;.&quot;ord_num&quot;,&quot;dbo&quot;.&quot;sales&quot;.&quot;title_id&quot; FROM &quot;dbo&quot;.&quot;sales&quot; WHERE (&quot;stor_id&quot; = '6380' ) \ 0&quot; </li> Create a new qryWithReturnType query.</li>  In the SQL Editor, add the following statement: SELECT dbo_sales.stor_id, dbo_sales.qty FROM dbo_sales WHERE (((dbo_sales.stor_id)=&quot;6380&quot;) AND ((dbo_sales.qty)=EnterQuantity2(5))); </li> Start ODBC tracing. Run the qryWithReturnType query.</li> To stop ODBC tracing, follow these steps: <ol style="list-style-type: lower-alpha;"> In the ODBC Data Source Administrator dialog box, click the Tracing tab.</li> Click Stop Tracing Now.</li></ol> </li>  Examine the tracing log file.

The criteria for the Quantity field is passed to the driver as follows: &quot;SELECT &quot;dbo&quot;.&quot;sales&quot;.&quot;stor_id&quot;,&quot;dbo&quot;.&quot;sales&quot;.&quot;ord_num&quot;,&quot;dbo&quot;.&quot;sales&quot;.&quot;title_id&quot; FROM &quot;dbo&quot;.&quot;sales&quot; WHERE ((&quot;stor_id&quot; = '6380' ) AND (&quot;qty&quot; = ? ) ) \ 0&quot; </li></ol>

Additional query words: custom ACC2000 ACC2002

Keywords: kbtshoot kbbug kbperformance KB821936

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.