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.PROB LEMNR
WHERE (((STATUSHISTOR IE.STATUSDATUM) <#1/1/2005#) AND
((PROBLEM_DE.DA TENBEREICH)='SP MO') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29')
AND ((PROBLEM_DE.ER LSTAND)<>"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 (((STATUSHISTOR IE.STATUSDATUM) <ReturnKW( ) ) AND
((PROBLEM_DE.DA TENBEREICH)='SP MO') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE(
) ) AND ((PROBLEM_DE.ER LSTAND)<>"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**********@h otmail.com> wrote in message
news:11******** ************@g4 3g2000cwa.googl egroups.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.PROB LEMNR
WHERE (((STATUSHISTOR IE.STATUSDATUM) <#1/1/2005#) AND
((PROBLEM_DE.DA TENBEREICH)='SP MO') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29')
AND ((PROBLEM_DE.ER LSTAND)<>"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 (((STATUSHISTOR IE.STATUSDATUM) <ReturnKW( ) ) AND
((PROBLEM_DE.DA TENBEREICH)='SP MO') AND
(((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE(
) ) AND ((PROBLEM_DE.ER LSTAND)<>"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("Quer y1").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**********@h otmail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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(varIte m) & ", "
Next
'remove comma/space at end and surround result in ()
str = "(" & Left(str,Len(st r)-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**********@h otmail.com> wrote in
news:11******** *************@g 47g2000cwa.goog legroups.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*********@Se eSig.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********@bwa y.net.invalid> wrote:

"Jean" <je**********@h otmail.com> wrote in
news:11******* **************@ g47g2000cwa.goo glegroups.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.