Slow query using a global variable

[antifashionpimp]

Hello,

I have the following query that I set up as a test, and it runs fine:

SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID = PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29') AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;

I then set up two global variables ( a String and a Date) and respective functions to return them – ReturnE( ) and ReturnKW( ). Now my query looks like this, but takes ages to run:

SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON [STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE( ) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];

My two public functions that return the global variables look like this:

Public gstrE As String 'global variable: contains E used for query
Public gdatKW As Date

Public Function ReturnE ()
ReturnE = gstrE
End Function

Public Function ReturnKW ()
ReturnKW = gdatKW
End Function


The tables are actually Views set up from an ODBC Data source. Can anyone please tell me why these global variables are causing the traffic jam?

Thanks in advance
J

 

[Pat Hartman]

The problem is that the ODBC database engine cannot process your VBA functions so Jet must retrieve ALL the data from the table on the server and apply the criteria locally. There are two ways to get around this problem.
1. Reference form fields rather than functions for criteria. Jet will evaluate the form field and send the entire query to the server for processing. Jet cannot evaluate the function because it doesn't know if the function will change from record to record.
Where somefield = Forms!yourform!somefield;
2. Create the SQL string in VBA and run it or use it to replace the existing RecordSource for a form or report.

strSQL = "...."
strSQL = strSQL & "Where somefield = " & yourFunc()

 

[antifashionpimp]

Thanks for your help, Pat

I got a suggestion from someone: add return types for my functions. This seemed to elude me from the start.

It works much faster now.

 

[Pat Hartman]

I got a suggestion from someone: add return types for my functions.

They were talking about specifically defining the data type of the value returned by the function. However, although this is definitely an improvement, it would not help with your query problem since having the function at all is what is causing all the data to be transferred from the server to the local PC and that is what is making the query take so long.

Public Function ReturnE () [b]As String[/b]
    ReturnE = gstrE
End Function

 

[antifashionpimp]

Hi,

So you are saying that if I get the variables directly from the forms controls, like you suggested, and do away with the functions, the query might even be faster?

Regards,
J

 

[Pat Hartman]

There is no "might" about it. The userdefined functions are forcing Jet to request EVERY row in the table from the server so the function (which SQL Server doesn't understand because it doesn't know VBA) can be evaluated locally.

If you refer to a form control to obtain your variable, Jet can evaluate that PRIOR to sending the query to the server so that it will send a literal value i.e. If the form field contains the value 4356 the criteria becomes "Where SomeField = 4356" because Access evaluates Forms!YourForm!SomeField to have a current value of 4356 and pops that into the query before sending it to the server. So the server processes the query and returns ONLY the records where SomeField = 4356 rather than potentially thousands of records.

 

[antifashionpimp]

I gave it a go with referencing the form's controls, and it showed an improvement.
Thanks!