Very Slow Query

[Chaplain Doug]

Access 2002. This is a repost with some updates. Once
you are on the third of fourth page of the newgroup you
are history. Anyhow, here goes.

I have a query that takes a long time to complete, namely
41 seconds. This same query when no criteria is specified
is instantaneous. The two colimns that cause the delay:

Inactive: DateDiff("d",Date(),Nz(DMax("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

Grad: Nz(DLookUp("[GDate]","[Grades Query]","[StudentID]
="+Str(Nz([Students.StudentID],0))+" AND ([LessonTitle]
Like '*Diploma*')"),"#1/1/1990#")>#1/1/1990#

Please note that most of the stuff in these expressions
were done in a function. I moved the code from functions
to the actual query. This saved me 20 seconds. When I
set the criteria for these columns to False (or anything
else) the query takes 41 seconds to complete. Without
criteria it is instantly done. By the way, there are
2,650 records to be processed by this query. PLEASE
HELP! Thanks.

 

[Albert D. Kallal]

The basic trick in optimizing sql is to come up with something that can
MUNCH and CRUNCH the sql you give.

Unfortunately, DMax is one of those "standalone" functions that can't really
be optimized by the query system. So, try re-writing the condition as sql,
and see how that runs. It should run very fast.

Also, you are executing a dmax on a query called [Grades Query]? Does that
have to be? Again, if you can eliminate that query, you can speed things up.

Inactive: DateDiff("d",Date(),Nz(DMax("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

I would try builder an extra column, like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by dsnd [gdate])

The above assume that each student will NOT have more then one GDate of the
same. If that CAN happen, then you need to extend the order by like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by [gdate] desc,ID)

Now, build your Inactive
Inactive: ( (Date() - nz(LastGDate,#01/01/1990#) < -60)

I also think the above reads easier also.

The above idea can be applied to getting rid of your grades query also. If
you can eliminate that query on a query...you also will make gains...

 

[Chaplain Doug]

Thanks Albert. I will try it and report.

-----Original Message-----
The basic trick in optimizing sql is to come up with something that can
MUNCH and CRUNCH the sql you give.

Unfortunately, DMax is one of those "standalone" functions that can't really
be optimized by the query system. So, try re-writing the condition as sql,
and see how that runs. It should run very fast.

Also, you are executing a dmax on a query called [Grades Query]? Does that
have to be? Again, if you can eliminate that query, you can speed things up.

Inactive: DateDiff("d",Date(),Nz(DMax("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

I would try builder an extra column, like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by dsnd [gdate])

The above assume that each student will NOT have more then one GDate of the
same. If that CAN happen, then you need to extend the order by like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by [gdate] desc,ID)

Now, build your Inactive
Inactive: ( (Date() - nz(LastGDate,#01/01/1990#) < -60)

I also think the above reads easier also.

The above idea can be applied to getting rid of your grades query also. If
you can eliminate that query on a query...you also will make gains...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

 

[Chaplain Doug]

I tried the SQL namely,

SELECT TOP 1 [GDate] FROM [Grades Query] WHERE StudentID=
[StudentID] ORDER BY [GDate] DESC

The query returned the same value for each student, namely
the most recent date entered into the database. Thus, if
last student say Jones had an entry of 6/19/2004 then
everybody had 6/19/2004 as the returned value from this
query, even though each had a top date different from
6/19/2004 in the table. Why so? By the way, I
added ",GradeID" after DESC and it still gave the same
redundant result. Thanks for the help.

-----Original Message-----
The basic trick in optimizing sql is to come up with something that can
MUNCH and CRUNCH the sql you give.

Unfortunately, DMax is one of those "standalone" functions that can't really
be optimized by the query system. So, try re-writing the condition as sql,
and see how that runs. It should run very fast.

Also, you are executing a dmax on a query called [Grades Query]? Does that
have to be? Again, if you can eliminate that query, you can speed things up.

Inactive: DateDiff("d",Date(),Nz(DMax("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

I would try builder an extra column, like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by dsnd [gdate])

The above assume that each student will NOT have more then one GDate of the
same. If that CAN happen, then you need to extend the order by like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by [gdate] desc,ID)

Now, build your Inactive
Inactive: ( (Date() - nz(LastGDate,#01/01/1990#) < -60)

I also think the above reads easier also.

The above idea can be applied to getting rid of your grades query also. If
you can eliminate that query on a query...you also will make gains...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

 

[Michel Walsh]

Hi,


If you want the latest information, by studentID, rather than using a
TOP 1 query, see http://www.mvps.org/access/queries/qry0020.htm and choose
the method (four are presented) that inspired you the most.

Hoping it may help,
Vanderghast, Access MVP

I tried the SQL namely,

SELECT TOP 1 [GDate] FROM [Grades Query] WHERE StudentID=
[StudentID] ORDER BY [GDate] DESC

The query returned the same value for each student, namely
the most recent date entered into the database. Thus, if
last student say Jones had an entry of 6/19/2004 then
everybody had 6/19/2004 as the returned value from this
query, even though each had a top date different from
6/19/2004 in the table. Why so? By the way, I
added ",GradeID" after DESC and it still gave the same
redundant result. Thanks for the help.

-----Original Message-----
The basic trick in optimizing sql is to come up with something that can
MUNCH and CRUNCH the sql you give.

Unfortunately, DMax is one of those "standalone" functions that can't really
be optimized by the query system. So, try re-writing the condition as sql,
and see how that runs. It should run very fast.

Also, you are executing a dmax on a query called [Grades Query]? Does that
have to be? Again, if you can eliminate that query, you can speed things up.

Inactive: DateDiff("d",Date(),Nz(DMax("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

I would try builder an extra column, like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by dsnd [gdate])

The above assume that each student will NOT have more then one GDate of the
same. If that CAN happen, then you need to extend the order by like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by [gdate] desc,ID)

Now, build your Inactive
Inactive: ( (Date() - nz(LastGDate,#01/01/1990#) < -60)

I also think the above reads easier also.

The above idea can be applied to getting rid of your grades query also. If
you can eliminate that query on a query...you also will make gains...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

 

[Ted Allen]

Hi Doug,

I posted a response to your June 24 thread this morning
before I saw this one. I had questioned the need to use
the [Grades Query] and asked if there is a way to
eliminate this. It will likely be better if your
subqueries can draw from the source tables directly.

I hadn't taken a stab at the subquery syntax while
waiting to hear back, but in looking at Albert's query it
seems like it should be correct. But, I did notice that
the SQL that you posted did not have the correct syntax
in trying to link the StudentID.

The subquery needs to state that the StudentID in the
source (in your case the grades query) must match the
StudentID of the current record (Students.StudentID in
the sql that Albert posted), which would be done by
setting StudentID = Students.StudentID. Also, it did not
have the parenthesis around it, which is required for
subqueries. In the query builder, this subquery text
would be typed, or pasted, in the same spot you would
normally place a formula for a calculated field if it is
being used to return a field value (as in this case) or
in the criteria row if being used to set criteria for
another field.

If you can post back with some info on the tables that
the data is coming from, we can help you with subquery
syntax using those. Also, if you can post back with a
short description of what you want to accomplish, there
may be other ways of doing it.

For example, if you don't really need to know the latest
date, but just want to return report on all ID's that had
some type of entry in the last 60 days, you could use the
IN statement and then a simple subquery to return all
ID's with an entry in the date field within 60 days of
the current date. Of course that could be further
combined with other criteria.

Hope that helps.

-Ted Allen

-----Original Message-----
I tried the SQL namely,

SELECT TOP 1 [GDate] FROM [Grades Query] WHERE StudentID=
[StudentID] ORDER BY [GDate] DESC

The query returned the same value for each student, namely
the most recent date entered into the database. Thus, if
last student say Jones had an entry of 6/19/2004 then
everybody had 6/19/2004 as the returned value from this
query, even though each had a top date different from
6/19/2004 in the table. Why so? By the way, I
added ",GradeID" after DESC and it still gave the same
redundant result. Thanks for the help.

-----Original Message-----
The basic trick in optimizing sql is to come up with something that can
MUNCH and CRUNCH the sql you give.

Unfortunately, DMax is one of those "standalone" functions that can't really
be optimized by the query system. So, try re-writing

the
condition as sql,

and see how that runs. It should run very fast.

Also, you are executing a dmax on a query called

[Grades
Query]? Does that

have to be? Again, if you can eliminate that query, you can speed things up.

Inactive: DateDiff("d",Date(),Nz(DMax ("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

I would try builder an extra column, like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by dsnd [gdate])

The above assume that each student will NOT have more then one GDate of the
same. If that CAN happen, then you need to extend the order by like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by [gdate] desc,ID)

Now, build your Inactive
Inactive: ( (Date() - nz(LastGDate,#01/01/1990#) < -60)

I also think the above reads easier also.

The above idea can be applied to getting rid of your grades query also. If
you can eliminate that query on a query...you also will make gains...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

.

 

[Dale Fye]

Doug,

Can you post the entire SQL of your query and of [Grades Query]?

Like Albert says, DMAX can be very slow when operating on lots of records.
On the other hand, using MAX in a subquery can be very fast, so you might be
able to create two subqueries (save them as independant queries). I think
this first query gets at your Inactive criteria, but am not quite sure since
the arguments for the DateDiff function are somewhat confusing. What this
query does is selects everyone from your [Grades Query] and their
Max(GDate), for those where the the Max(GDate) value is within the preceding
60 days.

SELECT StudentID, MAX(GDate) as MaxDate
FROM [Grades Query]
GROUP BY StudentID
HAVING DateDiff("d", MAX(GDate), Date()) < 60

Save this as qry_MaxDate

SELECT DISTINCT StudentID
FROM [Grades Query]
WHERE [LessonTitle] Like "*Diploma*"
AND [GDate] > #1/1/1990#

Save this as qry_Graduated

Now, link these two queries to your main table with left joins. Then use
the appropriate values of ISNULL(qry_MaxDate.StudentID) and
ISNULL(qry_Graduated.StudentID) as your criteria. Depending on what [Grades
Query] does, you might be able to go directly to the source table and
accomplish both of these with a single result set.

The down side of this is that it will not be an updateable query.

HTH
Dale

I tried the SQL namely,

SELECT TOP 1 [GDate] FROM [Grades Query] WHERE StudentID=
[StudentID] ORDER BY [GDate] DESC

The query returned the same value for each student, namely
the most recent date entered into the database. Thus, if
last student say Jones had an entry of 6/19/2004 then
everybody had 6/19/2004 as the returned value from this
query, even though each had a top date different from
6/19/2004 in the table. Why so? By the way, I
added ",GradeID" after DESC and it still gave the same
redundant result. Thanks for the help.

-----Original Message-----
The basic trick in optimizing sql is to come up with something that can
MUNCH and CRUNCH the sql you give.

Unfortunately, DMax is one of those "standalone" functions that can't really
be optimized by the query system. So, try re-writing the condition as sql,
and see how that runs. It should run very fast.

Also, you are executing a dmax on a query called [Grades Query]? Does that
have to be? Again, if you can eliminate that query, you can speed things up.

Inactive: DateDiff("d",Date(),Nz(DMax("[GDate]","[Grades
Query]","[StudentID]="+Str(Nz
([Students.StudentID],0))),"1/1/1990"))<-60

I would try builder an extra column, like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by dsnd [gdate])

The above assume that each student will NOT have more then one GDate of the
same. If that CAN happen, then you need to extend the order by like:

LastGDate: (select top 1 [GDate] from [Grades Query] where StudentID =
Students.StudentID order by [gdate] desc,ID)

Now, build your Inactive
Inactive: ( (Date() - nz(LastGDate,#01/01/1990#) < -60)

I also think the above reads easier also.

The above idea can be applied to getting rid of your grades query also. If
you can eliminate that query on a query...you also will make gains...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.