Query very slow because of called function

[Janross]

I'm having trouble with a query that's prohibitively slow. On my free-standing
office computer it's fine (well, 2-4 seconds), but on the client's network, it
takes at least 5 minutes to run. Obviously not workable!

I know where the problem is, I just don't know how to fix it. The query calls
a function, and I assume it gets slow because the function runs on every
record.

So--is there a way to rewrite the function so it's quicker?
Is there a way to rewrite the query so it doesn't depend on the function?
Is there another way to speed things up?

The function and the SQL are below.

Here's why I'm doing it:
The client runs reports that include categorizing clients by age. The age
ranges they use are determined by their funders, and different funders have
different groupings; plus, a funder might change the way they group ages in
mid-year. The client doesn't want to have to call me back every time the age
groups change. So, I put in a little lookup table for age ranges, and then
wrote a function to get the age range based on age and the type of report being
run.

It's pretty cool actually, and works just fine, except for that little problem
of there being time to go get coffee while the thing runs. And since they use
this report frequently, I can't just tell them to go drink coffee.

Basics: Access XP; split database, front end on the c drive; I've indexed
every field I can think of.

SQL (actually it has many more fields than this but I just left the basics).
Note that the agecalc() function works fine; doesn't slow down the other
queries it's used in. It's the agerange() function that's the problem.
SELECT DISTINCT [call members].callID,
agecalc([dob],nz([cases].[closedate],dateend())) AS age, [call members].DOB,
IIf(Not IsNull([age]),agerange("united way",[age]),"Unknown/Unreported") AS
agegroup, cases.caseID
FROM ([call members] INNER JOIN calls ON [call members].callID = calls.callID)
INNER JOIN cases ON calls.callID = cases.callID
WITH OWNERACCESS OPTION;

Function agerange(grouptype As String, agevar As Single)
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from agegroups where agegrptype='" _
& grouptype & "' and groupstart <= " & agevar & " and groupend >=" &
agevar)
If rs.RecordCount <> 0 Then
agerange = rs!grouptext
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Thanks for any help you can offer.

Jan
Jan Stempel
Stempel Consulting

[Salad]

Janross wrote:

I'm having trouble with a query that's prohibitively slow. On my free-standing
office computer it's fine (well, 2-4 seconds), but on the client's network, it
takes at least 5 minutes to run. Obviously not workable!

I know where the problem is, I just don't know how to fix it. The query calls
a function, and I assume it gets slow because the function runs on every
record.

So--is there a way to rewrite the function so it's quicker?
Is there a way to rewrite the query so it doesn't depend on the function?
Is there another way to speed things up?

The function and the SQL are below.

Here's why I'm doing it:
The client runs reports that include categorizing clients by age. The age
ranges they use are determined by their funders, and different funders have
different groupings; plus, a funder might change the way they group ages in
mid-year. The client doesn't want to have to call me back every time the age
groups change. So, I put in a little lookup table for age ranges, and then
wrote a function to get the age range based on age and the type of report being
run.

It's pretty cool actually, and works just fine, except for that little problem
of there being time to go get coffee while the thing runs. And since they use
this report frequently, I can't just tell them to go drink coffee.

Basics: Access XP; split database, front end on the c drive; I've indexed
every field I can think of.

SQL (actually it has many more fields than this but I just left the basics).
Note that the agecalc() function works fine; doesn't slow down the other
queries it's used in. It's the agerange() function that's the problem.
SELECT DISTINCT [call members].callID,
agecalc([dob],nz([cases].[closedate],dateend())) AS age, [call members].DOB,
IIf(Not IsNull([age]),agerange("united way",[age]),"Unknown/Unreported") AS
agegroup, cases.caseID
FROM ([call members] INNER JOIN calls ON [call members].callID = calls.callID)
INNER JOIN cases ON calls.callID = cases.callID
WITH OWNERACCESS OPTION;

Function agerange(grouptype As String, agevar As Single)
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from agegroups where agegrptype='" _
& grouptype & "' and groupstart <= " & agevar & " and groupend >=" &
agevar)
If rs.RecordCount <> 0 Then
agerange = rs!grouptext
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Thanks for any help you can offer.

Jan
Jan Stempel
Stempel Consulting

See if this may help. Create a query...
SELECT DISTINCT [call members].callID,
agecalc([dob],nz([cases].[closedate],dateend())) AS age, _
[callmembers].DOB,
cases.caseID
FROM ([call members] INNER JOIN calls ON _
[call members].callID = calls.callID)
INNER JOIN cases ON calls.callID = cases.callID
WITH OWNERACCESS OPTION;

Now create a second query that uses the above query with the additional
column that calcs/calls the agerange function.

Also, can you link the group type to the grouptypes table? That would
be much easier than your function. I think if you really looked at your
table structures, the BETWEEN operator in help, Inner/Left/Right joins
you could come up with a better scheme.

[Eric Schittlipz]

"Janross" <ja*****@aol.com> wrote in message
news:20***************************@mb-m15.aol.com...

I'm having trouble with a query that's prohibitively slow. On my
free-standing
office computer it's fine (well, 2-4 seconds), but on the client's
network, it
takes at least 5 minutes to run. Obviously not workable!

I know where the problem is, I just don't know how to fix it. The query
calls
a function, and I assume it gets slow because the function runs on every
record.

So--is there a way to rewrite the function so it's quicker?
Is there a way to rewrite the query so it doesn't depend on the function?
Is there another way to speed things up?

The function and the SQL are below.

Here's why I'm doing it:
The client runs reports that include categorizing clients by age. The age
ranges they use are determined by their funders, and different funders
have
different groupings; plus, a funder might change the way they group ages
in
mid-year. The client doesn't want to have to call me back every time the
age
groups change. So, I put in a little lookup table for age ranges, and
then
wrote a function to get the age range based on age and the type of report
being
run.

It's pretty cool actually, and works just fine, except for that little
problem
of there being time to go get coffee while the thing runs. And since they
use
this report frequently, I can't just tell them to go drink coffee.

Basics: Access XP; split database, front end on the c drive; I've
indexed
every field I can think of.

SQL (actually it has many more fields than this but I just left the
basics).
Note that the agecalc() function works fine; doesn't slow down the other
queries it's used in. It's the agerange() function that's the problem.
SELECT DISTINCT [call members].callID,
agecalc([dob],nz([cases].[closedate],dateend())) AS age, [call
members].DOB,
IIf(Not IsNull([age]),agerange("united way",[age]),"Unknown/Unreported")
AS
agegroup, cases.caseID
FROM ([call members] INNER JOIN calls ON [call members].callID =
calls.callID)
INNER JOIN cases ON calls.callID = cases.callID
WITH OWNERACCESS OPTION;

Function agerange(grouptype As String, agevar As Single)
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from agegroups where agegrptype='"
_
& grouptype & "' and groupstart <= " & agevar & " and groupend >="
&
agevar)
If rs.RecordCount <> 0 Then
agerange = rs!grouptext
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Thanks for any help you can offer.

Jan
Jan Stempel
Stempel Consulting

As you have established, any function which opens and closes a recordset for
each line is going to be very slow. The query would run much faster if you
were comparing values in tables and you could use your lookup table directly
in the query. In this example, I have:
tblContacts: ConID, ConFirst, ConLast, ConDOB
tblAgeGroups: AgeID (long int), AgeName, AgeMin, AgeMax (min and max values
in years)
NB In this table I have a row with AgeID=0 and min and max ages set to -1 -
the name of this age group is "unknown"
Now in one query without calling any module functions, I get a list of
contacts and the age group they are in:

SELECT ConID, ConFirst, ConLast, ConDOB, AgeID, AgeName
FROM tblContacts, tblAgeGroups
WHERE
(DateDiff("yyyy",[ConDOB],Now()) +
Int(Format(Now(),"mmdd")<Format([ConDOB],"mmdd"))>=[AgeMin])
AND
(DateDiff("yyyy",[ConDOB],Now()) +
Int(Format(Now(),"mmdd")<Format([ConDOB],"mmdd"))<=[AgeMax] )
OR
(ConDOB Is Null AND AgeID=0)

Obviously the grouping and counting is the next step - but can you see how
this approach would help you? If so, I could e-mail a sample database if
you use a genuine address - I don't post my real name or email much anymore.

[janross]

That did it exactly! Thanks so much. I struggled with this for a long
time and the sort-of-subquery approach (with minor tweaking) definitely
did the trick.

I would have replied last week when I actually solved the problem, but
for some reason this post has never showed up in my newsreader (had to
find it in Google), and then Google wouldn't let me post any
replies....Finally that part was solved.

Thanks also to Salad, although that approach didn't actually solve the
problem.

Jan