Ranking query running EXTREMELY slow

[Sean G.]

Hello,

I have a ranking query which is based off of two other
queries. Inside I rank employee hours by overall then by
staff level. Everything works except for the fact that it
is really slow, even sorting the query takes a few
minutes... Does anybody have any suggestions? Here is one
of the lines of code:
StfLvlHoursrank: (Select Count(*) from
qry_Employee_AllHours_Sums where [TotalHours] >
[AllHoursrank].[TotalHours] and
[StaffLevel]=[AllHoursrank].[StaffLevel])+1

I'm not sure if I should consildate queries, but I have had
more dependent queries than this before.

Any help would be greatly appreciated

 

[Michel Walsh]

Hi,

That results in a SELECT query in the SELECT clause... and the "sub-query"
is itself based on a ... query, qry_Employee_...

I would try to move that into a JOIN, I mean, bring the qry_Employee_... in
the upper part, give it a decent alias to avoid all that ugly typing, add
the criteria through WHERE criteria,ie, allhoursrank.TotalHours with, as
criteria, < nicealias.TotalHours, also nicealias.StaffLevel with the
criteria =AllHoursrank.StaffLevel (or make that an inner join), THEN, make a
GROUP with all the required fields, and add COUNT(*) in the main SELECT
clause.:

------------------------
SELECT f1, f2, f3, ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel

WHERE a.TotalHours < q.TotalHours

GROUP BY f1, f2, f3, ...
---------------------------


or even faster:

-----------------------------
SELECT a.pk, LAST(a.f1), LAST(a.f2), LAST(a.f3), ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel
AND a.TotalHours < q.TotalHours

GROUP BY a.pk
------------------------------


where pk is the primary key of table AllHoursRank. Can use FIRST, MIN, or
MAX, instead of LAST, in this particular case, since all the records of
table aliased "a", of a given group would be, at most, for a given field,
the same duplicated values.




Hoping it may help,
Vanderghast, Access MVP

 

[Sean G.]

Thank you very much for your response, I will try that!

-----Original Message-----
Hi,

That results in a SELECT query in the SELECT clause... and the "sub-query"
is itself based on a ... query, qry_Employee_...

I would try to move that into a JOIN, I mean, bring the qry_Employee_... in
the upper part, give it a decent alias to avoid all that ugly typing, add
the criteria through WHERE criteria,ie,

allhoursrank.TotalHours with, as

criteria, < nicealias.TotalHours, also nicealias.StaffLevel with the
criteria =AllHoursrank.StaffLevel (or make that an inner join), THEN, make a
GROUP with all the required fields, and add COUNT(*) in the main SELECT
clause.:

------------------------
SELECT f1, f2, f3, ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel

WHERE a.TotalHours < q.TotalHours

GROUP BY f1, f2, f3, ...
---------------------------


or even faster:

-----------------------------
SELECT a.pk, LAST(a.f1), LAST(a.f2), LAST(a.f3), ..., COUNT(*) As Rank

FROM AllHoursrank As a INNER JOIN
qry_Employee_AllHours_Sums As q
ON a.StaffLevel = q.StaffLevel
AND a.TotalHours < q.TotalHours

GROUP BY a.pk
------------------------------


where pk is the primary key of table AllHoursRank. Can use FIRST, MIN, or
MAX, instead of LAST, in this particular case, since all the records of
table aliased "a", of a given group would be, at most, for a given field,
the same duplicated values.




Hoping it may help,
Vanderghast, Access MVP

Hello,

I have a ranking query which is based off of two other
queries. Inside I rank employee hours by overall then by
staff level. Everything works except for the fact that it
is really slow, even sorting the query takes a few
minutes... Does anybody have any suggestions? Here is one
of the lines of code:
StfLvlHoursrank: (Select Count(*) from
qry_Employee_AllHours_Sums where [TotalHours] >
[AllHoursrank].[TotalHours] and
[StaffLevel]=[AllHoursrank].[StaffLevel])+1

I'm not sure if I should consildate queries, but I have had
more dependent queries than this before.

Any help would be greatly appreciated

.