Query too slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query as the source of a combo box whose SQL is:

SELECT tblStudent.StudentID, tblClass.ClassID, tblClass.Status,
tblGrade.SuspendDate
FROM tblClass INNER JOIN (tblStudent INNER JOIN tblGrade ON
tblStudent.StudentID = tblGrade.StudentID) ON tblClass.ClassID =
tblGrade.ClassID
WHERE (((tblStudent.StudentID)=[forms]![frmSelectStudent]![txtStudentID])
AND ((tblClass.Status)="O") AND ((tblGrade.SuspendDate)>Format(Now(),"Short
Date"))) OR
(((tblStudent.StudentID)=[forms]![frmSelectStudent]![txtStudentID]) AND
((tblClass.Status)="O") AND ((tblGrade.SuspendDate) Is Null));

Making this simple -- I am checking for records with O in them and a date
greater than today or the same and the date null.

This runs very slow.
Any ideas.
Thanks
 
Stanley

Are the front-end and back-end on the same PC, or separated over a LAN or
over a WAN?

How "fast" are the machines on which this runs?

How "fast" is the network over which this runs (if on a network)?

Are the tables indexed on the fields used to join and/or select and/or sort?

Good luck

Jeff Boyce
Microsoft Office/Access MVP
 
Assuming that SuspendDate is a DateField, I would drop the format. I would also
use the Date() function instead of the Now function and finally make sure you
have indexes on tblClass.Status, tblGrade.SuspendDate, tblStudent.StudentID,
tblClass.ClassID, tblGrade.ClassID and tblStudent.StudentID (although the last
four should be there if you've set up relationships between the relevant tables).

SELECT tblStudent.StudentID, tblClass.ClassID, tblClass.Status,
tblGrade.SuspendDate
FROM tblClass INNER JOIN (tblStudent INNER JOIN tblGrade ON
tblStudent.StudentID = tblGrade.StudentID) ON tblClass.ClassID =
tblGrade.ClassID
WHERE (tblStudent.StudentID=[forms]![frmSelectStudent]![txtStudentID]
AND tblClass.Status="O" AND tblGrade.SuspendDate>Date()) OR
(tblStudent.StudentID=[forms]![frmSelectStudent]![txtStudentID] AND
tblClass.Status="O" AND tblGrade.SuspendDate Is Null)


If that doesn't help. Are you running this over a network? If so, that could
be the bottleneck. Are you other queries slow?
 
I'll add to what Jeff and John said.

How slow? Stopwatch or hour glass slow?

How many records are returned to the combo box. Combo boxes start running
out of steam after a few thousand records and die around 64k records. Does
the SQL run well by itself and only shows the problem in the combo box?

"(tblGrade.SuspendDate) Is Null" is a performance problem. Indexes don't
track null values. Sometimes it's better to put a default value of some bogus
date like #1/1/1950# and update existing nulls in records with that date.
Then instead of the Where clause saying (tblGrade.SuspendDate) Is Null it
would be (tblGrade.SuspendDate) = #1/1/1950# and an index would quickly find
the records.

Of course this could break other parts of the database such as where you are
looking for students who have been suspended in the past.
 
(...). Indexes don't
track null values.
Click to expand...



Not by default, with Jet 4.0. You have to specify IGNORE NULL when you
CREATE INDEX to not have entries for the null in the index.

CREATE [UNIQUE] INDEX indexName
ON tableName (columnName1 [, columnName2 [,...] ] )
[WITH (PRIMARY | DISALLOW NULL | IGNORE NULL)]
 
Hi,

push the OR like this:

WHERE tblStudent.StudentID=[forms]![frmSelectStudent]![txtStudentID]
AND tblClass.Status="O"
AND (tblGrade.SuspendDate>Format(Now(),"Short Date"))
OR
tblGrade.SuspendDate Is Null);

Actually, your OR clause, at "top level" of the where clause, is poorly
optimizeable, while at a "lower" level as shown, it is evaluated only if the
two first condition are both true... and thus, could be faster. That is
because Jet has a tendency to evaluate the WHERE clause "as we typed it",
from left to right. And ANDing first, ORing last is generally faster.


If that is not enough, it *may* help (may not too, depends on many factors)
to use a subquery to apply the WHERE clause before the JOIN:

SELECT *
FROM tblStudent
WHERE tblStudent.StudentID=forms![frmSelectStudent]![txtStudentID]



saved as q1 then use q1 instead of tblStudent in your query (and it is
useless to keep the corresponding condition in the original WHERE clause,
since the filtering has already been done in q1).

Use the same thing for tblClass ="O"




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top