472,799 Members | 1,611 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,799 software developers and data experts.

Query very slow because of called function

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
Nov 13 '05 #1
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.
Nov 13 '05 #2
"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.
Nov 13 '05 #3
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

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
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,...
3
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...
8
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...
2
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...
3
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
15
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. ...
15
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 =...
11
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...
4
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...
3
isladogs
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...
0
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...
0
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...
2
isladogs
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...
0
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 ...
0
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...
5
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...
0
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=()=>{
2
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.