Global variables are slowing down the query

[Jean]

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];

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

[Jean]

Me again...should maybe just add my Public functions that return the
global variables

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

[Allen Browne]

It's true. In Access 2000 and later, any user-defined VBA function acts like
an anchor.

Could you avoid this situation by reading the values from controls on a form
instead of using variables?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jean" <je**********@hotmail.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.com...

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];

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

[Jean]

Yes, I guess I could use that method.

Originally, I have the user select the string in a ComboBox, and the
date(s) from the second column in a ListBox. I have to iterate through
the date ListBox, as it is in Extended mode and a user can select
multiple entries.

But the point is: How do I reference the value of a form's control -
directly in SQL? Or does one do it in the QBE grid? I have never seen
this done so I would appreciate it if you could tell me.

Thanks for your support so far!

[Allen Browne]

In the query design grid, typically in the Criteria row, you can refer to a
text box on an open form like this:
[Forms].[Form1].[Text0]

This becomes part of the SQL statement like this:
WHERE Table1.Field1 = [Forms].[Form1].[Text0]

Ideally you want to declare this as a parameter to the query, to ensure the
data type is interpreted correctly.

If you are comfortable with writing SQL, you could create the SQL string
dynamically, concatenating the value from the form or variable directly into
the SQL string, e.g.:
Dim strSql As String
strSql = "SELECT Table1.* FROM Table1 WHERE Table1.Field1 = " &
MyVariable & ";"

Then you can assign this to the RecordSource of a form:
Me.RecordSource = strSql
or to a saved query:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jean" <je**********@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Yes, I guess I could use that method.

Originally, I have the user select the string in a ComboBox, and the
date(s) from the second column in a ListBox. I have to iterate through
the date ListBox, as it is in Extended mode and a user can select
multiple entries.

But the point is: How do I reference the value of a form's control -
directly in SQL? Or does one do it in the QBE grid? I have never seen
this done so I would appreciate it if you could tell me.

Thanks for your support so far!

[Salad]

Jean wrote:

Yes, I guess I could use that method.

Originally, I have the user select the string in a ComboBox, and the
date(s) from the second column in a ListBox. I have to iterate through
the date ListBox, as it is in Extended mode and a user can select
multiple entries.
Since you were initially talking about slow queries, when you have a
multiselect listbox, you may want to go through all of the items and
create a string.

For each varItem in Me.ListBoxName.ItemsSelected
str = str & Me.ListBoxName.ItemData(varItem) & ", "
Next
'remove comma/space at end and surround result in ()
str = "(" & Left(str,Len(str)-2) & ")"

Let's assume the string now contains a bunch of IDs
(1,2,3,4,5)

Creating a dynamic query like Allen demonstrated, you could do something
like
strSQL = "Select * from Employee where ID In " & str
Me.Recordsource = strSQL

The ID In (1,2,3,4,5) method for your criteria will be extremely fast in
your query.
But the point is: How do I reference the value of a form's control -
directly in SQL? Or does one do it in the QBE grid? I have never seen
this done so I would appreciate it if you could tell me.
Oftentimes you create a query in the QBE grid and reference using the
Forms!FormName!ControlName. Sometimes you create the query in the grid,
and viewSQL from the menu and copy/paste that to a module for refining.

Thanks for your support so far!

[David W. Fenton]

"Jean" <je**********@hotmail.com> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

Me again...should maybe just add my Public functions that return
the global variables

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

Well, for one, both of your functions have no return type declared.
Your first should be defined as:

Public Function ReturnE() As String

and your second as:

Public Function ReturnKW() As Date

Furthermore, your query would be greatly improved by declaring
parameters for these functions, so that the query processor wouldn't
have to figure out the data types at runtime.

In the QBE view, you can define parameters from the QUERY menu --
PARAMETERS is on that menu.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

It's true. In Access 2000 and later, any user-defined VBA function
acts like an anchor.

Could you avoid this situation by reading the values from controls
on a form instead of using variables?

But the functions being used don't have types declared, and the
query itself does not have parameters defined. Changing either of
those to explicit data types is likely to cause the query to be
compiled differently, and my bet is that it will lead to the whole
thing being processed more efficiently.

Perhaps it has not occurred to most people to declare parameters for
functions? I do the same for references to controls on forms, and it
seems to me to speed things up.

But the functions should definitely have return types, instead of
being generic variants.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jean]

Thank you all for your support. I am so full of new ideas now, and
really appreciate your feedback and experiences.

I first of all tried David's suggestion - and it notably did the
trick! I guess I was a bit slow on catching on that I had no return
type defined for my functions.

,,In the QBE view, you can define parameters from the QUERY menu --
PARAMETERS is on that menu."

,,Changing either of those to explicit data types is likely to cause
the query to be
compiled differently, and my bet is that it will lead to the whole
thing being processed more efficiently."
I still have not applied parameters to my query (and dont need to
anymore), but I will look up on this topic in the future.

Salad and Alan, I have to build SQL strings anyway later in my program
that are based on this query. I just thought that if I cannot get this
query optimised, then all the future stuff will be even slower. I am
using the method that Salad suggested in order to return values from
the ListBox.

Once again, thanks for all your input.

Kind Regards,

J

P.S. I am using Google Groups Beta - how do I add quoted text to my
post with the ">" charater appended to the rows?

[Steve Jorgensen]

On Tue, 09 Aug 2005 15:49:35 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Jean" <je**********@hotmail.com> wrote in
news:11*********************@g47g2000cwa.googlegr oups.com:

Me again...should maybe just add my Public functions that return
the global variables

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

Well, for one, both of your functions have no return type declared.
Your first should be defined as:

Public Function ReturnE() As String

and your second as:

Public Function ReturnKW() As Date

Furthermore, your query would be greatly improved by declaring
parameters for these functions, so that the query processor wouldn't
have to figure out the data types at runtime.

In the QBE view, you can define parameters from the QUERY menu --
PARAMETERS is on that menu.

Not only would the query not have to determine the types at run-time, I'm
betting it's so slow now because it calls the function multiple times.
Whenever JET has to make calls into a user-defined function, there's a big
time hit.

[Trevor Best]

Steve Jorgensen wrote:

Not only would the query not have to determine the types at run-time, I'm
betting it's so slow now because it calls the function multiple times.
Whenever JET has to make calls into a user-defined function, there's a big
time hit.

IME if the function has no parameters Access will call it only once.
Although I've not used one in the criteria so that may be the difference.

[Steve Jorgensen]

On Wed, 10 Aug 2005 08:08:06 +0100, Trevor Best <no****@nospam.invalid> wrote:

Steve Jorgensen wrote:

Not only would the query not have to determine the types at run-time, I'm
betting it's so slow now because it calls the function multiple times.
Whenever JET has to make calls into a user-defined function, there's a big
time hit.

IME if the function has no parameters Access will call it only once.
Although I've not used one in the criteria so that may be the difference.

My experience matches yours. I'm just not sure if one can rely on that rule
in all cases.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:su********************************@4ax.com:

On Tue, 09 Aug 2005 15:49:35 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Jean" <je**********@hotmail.com> wrote in
news:11*********************@g47g2000cwa.googleg roups.com:

Me again...should maybe just add my Public functions that return
the global variables

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

Well, for one, both of your functions have no return type
declared. Your first should be defined as:

Public Function ReturnE() As String

and your second as:

Public Function ReturnKW() As Date

Furthermore, your query would be greatly improved by declaring
parameters for these functions, so that the query processor
wouldn't have to figure out the data types at runtime.

In the QBE view, you can define parameters from the QUERY menu --
PARAMETERS is on that menu.

Not only would the query not have to determine the types at
run-time, I'm betting it's so slow now because it calls the
function multiple times. Whenever JET has to make calls into a
user-defined function, there's a big time hit.

No, it will only call it once, since it isn't going to return a
different value. It would only get called multiple times if it were
accepting a value from each row as an input.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:90********************************@4ax.com:

On Wed, 10 Aug 2005 08:08:06 +0100, Trevor Best
<no****@nospam.invalid> wrote:

Steve Jorgensen wrote:

Not only would the query not have to determine the types at
run-time, I'm betting it's so slow now because it calls the
function multiple times. Whenever JET has to make calls into a
user-defined function, there's a big time hit.

IME if the function has no parameters Access will call it only
once. Although I've not used one in the criteria so that may be
the difference.

My experience matches yours. I'm just not sure if one can rely on
that rule in all cases.

If it's in the criteria and it doesn't take an argument why in the
world would the optimizer call it more than once?

Also, having a function in the criteria won't cause all the
processing to be client-side (instead of server-side), since once
the functions have been evaluated, the result can be sent to the
server as the criteria to filter on.

It is only when you have Access functions that *can't* be processed
before sending to the server that everything gets pulled to the
client and processed there. An example would be sorting on a
function. Since the function is client-side, all the data has to be
pulled and sorted. And if there are criteria on a function, same
thing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Jean" <je**********@hotmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

Thank you all for your support. I am so full of new ideas now, and
really appreciate your feedback and experiences.

I first of all tried David's suggestion - and it notably did the
trick! I guess I was a bit slow on catching on that I had no
return type defined for my functions.
Well, there was an implicit AS VARIANT there. That slows things down
in any context, since VARIANT is the most complex data type you can
have.
,,In the QBE view, you can define parameters from the QUERY menu
-- PARAMETERS is on that menu."

,,Changing either of those to explicit data types is likely to
cause the query to be
compiled differently, and my bet is that it will lead to the whole
thing being processed more efficiently."
Yes, definitely. By defining parameters you're telling the Jet query
optimizer up front that the results of those parameters can be
evaluated first before sending them off to the server for
processing. Once you have non-variant return types on your
functions, the query processor would probably be able to figure that
out, too, but you're speeding things up by taking that task away
from the query optimizer and telling it off the bat that it can
evaluate the functions before sending the request to the server.
I still have not applied parameters to my query (and dont need to
anymore), but I will look up on this topic in the future.

It might speed it up even more. Even if it does not, you would
probably doing yourself a favor by declaring the parameters because
you're making life easier for the query optimizer. By doing so,
you're also eliminating the remote possibility of the query
optimizer guessing wrong. Secondly, you're going to speed up the
execution of the query after a compact of the front end, which
discards the compiled optimization.

In general, though, I don't use parameters in saved queries, because
most of my dynamic recordsources are defined in code, where I
resolve the criteria before writing the SQL.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

David W. Fenton wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:90********************************@4ax.com:

On Wed, 10 Aug 2005 08:08:06 +0100, Trevor Best
<no****@nospam.invalid> wrote:

IME if the function has no parameters Access will call it only
once. Although I've not used one in the criteria so that may be
the difference.

My experience matches yours. I'm just not sure if one can rely on
that rule in all cases.

If it's in the criteria and it doesn't take an argument why in the
world would the optimizer call it more than once?

I didn't say it would, I said I never tried it. I wouldn't expect it to
but then also IME Access doesn't always do things the way I expect.