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