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 3 4701
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.
"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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: ensnare |
last post by:
I'm attempting to create a threaded comment system involving PHP /
MySQL. Currently, I have a table called comments which looks like:
Table Comments: (comment_id, comment_root_id,...
|
by: Robert |
last post by:
I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the...
|
by: VPaul |
last post by:
Is there a web page out there like this one ...
http://www.sql-tutor.com/sql_tutor/database.asp that would allow me to
test some SQL to a DB2? In particular to an AS/400. I am learning and find...
|
by: Willem |
last post by:
Hi there,
I'm sort of new with doing much record manipulation with queries. Up
till now I've been programming VBA and doing record looping to get my
results. This works fine but tends to get...
|
by: Matthias Haffke |
last post by:
Ok, this is a tricky question for the pro's:
My access sheet:
line, id a, id b, val%
----------------
1, a, ac, 0.04
2, a, ac, 0.28
3, a, ac, 0.015
4, a, ac, 0.205
|
by: Rolan |
last post by:
There must be a way to enhance the performance of a query, or find a
plausible workaround, but I seem to be hitting a wall. I have tried a
few tweaks, however, there has been no improvement.
...
|
by: Jean |
last post by:
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 =...
|
by: Andy_Khosravi |
last post by:
My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive...
|
by: Geoff |
last post by:
I need to produce a report based on a query.
Cost is a calculated field and its value is dependent on another field, in
the query, called Session.
There are 5 different Session codes each...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |