Slow Query

  • Thread starter Thread starter Chaplain Doug
  • Start date Start date
C

Chaplain Doug

I have a query that is extremely slow (takes 59 seconds to
complete). The columns that seems to cause the slowness
are:

Inactive: IIf(IsDate(LastAssignmentDate([StudentID])) And
DateDiff("d",Date(),LastAssignmentDate([StudentID]))<-
60,True,False)

Grad: Diploma([StudentID])

When I put a Criteria of "False" for these columns the
query goes very slow. When there is no criteria, it is
fast (virtually instantaneous). LastAssignmentDate and
Diploma are public functions that reside in a module.
What am I doing wrong or how may I remedy this slow query
situation? Thanks for the help.
 
I have a query that is extremely slow (takes 59 seconds to
complete). The columns that seems to cause the slowness
are:

Inactive: IIf(IsDate(LastAssignmentDate([StudentID])) And
DateDiff("d",Date(),LastAssignmentDate([StudentID]))<-
60,True,False)

Grad: Diploma([StudentID])

When I put a Criteria of "False" for these columns the
query goes very slow. When there is no criteria, it is
fast (virtually instantaneous). LastAssignmentDate and
Diploma are public functions that reside in a module.
What am I doing wrong or how may I remedy this slow query
situation? Thanks for the help.
Click to expand...

Any time you use an IIF or a user-defined function in a calculated
field with criteria, it's going to be slow - Access must do a full
table scan and cannot use any indexes. How is LastAssignmentDate
calculated? Is it possible to do this calculation in the query
directly rather than calling a function?
 
I have a query that is extremely slow (takes 59 seconds to
complete). The columns that seems to cause the slowness
are:

Inactive: IIf(IsDate(LastAssignmentDate([StudentID])) And
DateDiff("d",Date(),LastAssignmentDate([StudentID]))<-
60,True,False)

Grad: Diploma([StudentID])

When I put a Criteria of "False" for these columns the
query goes very slow. When there is no criteria, it is
fast (virtually instantaneous). LastAssignmentDate and
Diploma are public functions that reside in a module.
What am I doing wrong or how may I remedy this slow query
situation? Thanks for the help.
Click to expand...

Hi Chaplain Doug,

I am not sure why the difference.

At the least, your LastAssignmentDate function
should always return a date (so you don't
have to test IsDate)...just return #1/1/9999#
if you cannot determine a date.
 
Dear John:

Long time no talk. Thanks for the reply. I basically
moved the calculations that take place in my user defined
function right into the query. It did not change the
speed of the query appreciably. I am confused as to why
without criteria the query completes quickly
(instantaneously) and with criteria it takes 59 seconds.
The calculations are still being performed with or without
the criteria and the proper values are being displayed.
-----Original Message-----
I have a query that is extremely slow (takes 59 seconds to
complete). The columns that seems to cause the slowness
are:

Inactive: IIf(IsDate(LastAssignmentDate([StudentID])) And
DateDiff("d",Date(),LastAssignmentDate([StudentID]))<-
60,True,False)

Grad: Diploma([StudentID])

When I put a Criteria of "False" for these columns the
query goes very slow. When there is no criteria, it is
fast (virtually instantaneous). LastAssignmentDate and
Diploma are public functions that reside in a module.
What am I doing wrong or how may I remedy this slow query
situation? Thanks for the help.
Click to expand...

Any time you use an IIF or a user-defined function in a calculated
field with criteria, it's going to be slow - Access must do a full
table scan and cannot use any indexes. How is LastAssignmentDate
calculated? Is it possible to do this calculation in the query
directly rather than calling a function?




.
Click to expand...
 
Hi Doug,

I think that the main reason you are seeing a drastic
time difference when you enter the criteria is that it
forces access to evaluate the expression results for all
records prior to displaying the results. Without the
criteria, Access only evaluates the function results for
a subset of the records at a time as you scroll through
them, this it can display the first batch of records very
quickly.

To speed up your query, you may want to evaluate your
functions to see if they can be made more efficient. You
may also want to explore the possibility of using
subqueries rather than functions to set your criteria, if
possible.

If you aren't familiar with subqueries, you could post
the text of your functions and we could see if that would
be possible.

-Ted Allen
 
Dear John:

Long time no talk. Thanks for the reply. I basically
moved the calculations that take place in my user defined
function right into the query. It did not change the
speed of the query appreciably. I am confused as to why
without criteria the query completes quickly
(instantaneously) and with criteria it takes 59 seconds.
The calculations are still being performed with or without
the criteria and the proper values are being displayed.
Click to expand...

I see Ted's got a better answer than I would have come up with to both
parts of the question... <g>

Please post the entire SQL and the VBA code of the function. Ted's
suggestion of a Subquery is probably right on the mark.
 
Thanks Ted. I will post the VBA for the functions
tomorrow when I get to work. I would like to know how to
do a subquery. Thanks for the ongoing help.
-----Original Message-----
Hi Doug,

I think that the main reason you are seeing a drastic
time difference when you enter the criteria is that it
forces access to evaluate the expression results for all
records prior to displaying the results. Without the
criteria, Access only evaluates the function results for
a subset of the records at a time as you scroll through
them, this it can display the first batch of records very
quickly.

To speed up your query, you may want to evaluate your
functions to see if they can be made more efficient. You
may also want to explore the possibility of using
subqueries rather than functions to set your criteria, if
possible.

If you aren't familiar with subqueries, you could post
the text of your functions and we could see if that would
be possible.

-Ted Allen
-----Original Message-----
I have a query that is extremely slow (takes 59 seconds to
complete). The columns that seems to cause the slowness
are:

Inactive: IIf(IsDate(LastAssignmentDate([StudentID])) And
DateDiff("d",Date(),LastAssignmentDate([StudentID]))<-
60,True,False)

Grad: Diploma([StudentID])

When I put a Criteria of "False" for these columns the
query goes very slow. When there is no criteria, it is
fast (virtually instantaneous). LastAssignmentDate and
Diploma are public functions that reside in a module.
What am I doing wrong or how may I remedy this slow query
situation? Thanks for the help.
.
Click to expand...
.
Click to expand...
 
These are the functions being performed:

Public Function LastAssignmentDate(StudentID) As String
'StudentID is the unique StudentID field of the student.
On Error GoTo LAD_Err
Dim strSQL As String
strSQL = "[StudentID]=" + Str(Nz(StudentID, 0))
LastAssignmentDate = Nz(DMax("[GDate]", "[Grades
Query]", strSQL), "1/1/1990")
LAD_Exit:
Exit Function

LAD_Err:
MsgBox Error$
Resume LAD_Exit

End Function

Public Function DiplomaDate(StudentID) As String
'StudentID is the unique StudentID field of the student.
On Error GoTo DD_Err
Dim strSQL As String
strSQL = "[StudentID]=" + Str(Nz(StudentID, 0)) + " AND
([LessonTitle] Like '*Diploma*')"
DiplomaDate = Nz(DLookup("[GDate]", "[Grades Query]",
strSQL), "None")
DD_Exit:
Exit Function

DD_Err:
MsgBox Error$
Resume DD_Exit

End Function
Public Function Diploma(StudentID) As Boolean
'StudentID is the unique StudentID field of the student.
On Error GoTo D_Err
If IsDate(DiplomaDate(StudentID)) Then
Diploma = True
Else
Diploma = False
End If
D_Exit:
Exit Function

D_Err:
MsgBox Error$
Resume D_Exit

End Function

*****************************
Here are the columns that slow up the query when I specify
a criteria:

Inactive: IIf(IsDate(LastAssignmentDate([StudentID])) And
DateDiff("d",Date(),LastAssignmentDate([StudentID]))<-
60,True,False)

Grad: Diploma([StudentID])
 
Sorry I wasn't able to respond last week, I was off on
Fri.

It looks like subqueries will likely work in place of
your custom functions. But, I noticed that the lookups
in your custom functions appear to be drawing from a
saved query. I think that it would be better to draw
from the source tables if possible. Could you post a
little info on how the lookup data (assignment date,
diploma date) is stored in your tables. If the SQL for
the grades query isn't too long and complex you may just
want to post that.

I'll keep an eye out for your post.

-Ted Allen
 
Back
Top