"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.
