Link to home
Create AccountLog in
Avatar of Ryan
RyanFlag for United States of America

asked on

Nested Subquery efficiency

Harfang showed me the light with subqueries, then I quickly drowned myself with them using nested subqueries.
Basically these 2 queries sum up the points per event teamAbbrev rank them, then assign points based on that rank.

The problem is, the 2nd query I have listed, which is used in the first query takes about 5seconds to run. The 1st query takes somewhere around 10minutes. And currently I have 1/5 of the data that there will be.

How can I make this faster? I have indexed every field that was relevant, and all field data types are the best they can be.
qryTeamPtsScaled:
 
SELECT Q1.D1, Q1.TeamAbbrev, Q1.EventAbbrev, tblPts.Pts AS Scale, Q1.Championship
FROM qryTeamPtsSub AS Q1, tblPts
WHERE (((
  SELECT COUNT(*) FROM qryTeamPtsSub AS Q2 
  WHERE Q2.Raw >= [Q1].[raw] AND Q2.D1 =  Q1.D1 AND  Q1.EventID = Q2.EventID)=[tblPts].[position]) AND tblPts.ScoreID=3)
ORDER BY Q1.D1, Q1.TeamAbbrev, Q1.EventAbbrev;
 
qryTeamPtsSub:
SELECT tblTeam.D1, tblTeam.TeamAbbrev, Sum(qryTeamRaw.Pts) AS Raw, tblEvent.EventAbbrev, tblEvent.EventID, tblEvent.Championship
FROM tblTeam INNER JOIN 
  (tblEvent INNER JOIN 
    (tblRace INNER JOIN 
      (tblRider INNER JOIN qryTeamRaw ON tblRider.RiderID = qryTeamRaw.RiderID)
       ON tblRace.RaceID = qryTeamRaw.RaceID)
    ON tblEvent.EventID = tblRace.EventID) 
  ON tblTeam.TeamAbbrev = tblRider.TeamAbbrev
GROUP BY tblTeam.D1, tblTeam.TeamAbbrev, tblEvent.EventAbbrev, tblEvent.EventID, tblEvent.Championship;

Open in new window

Avatar of CMYScott
CMYScott
Flag of United States of America image

at first glance..

change the 'SELECT COUNT(*)' to a specific field... one that is NOT going to be NULL

COUNT(*) is easy, but I believe its slower than counting a specific field.  I would suppose it would be compounded if any of those fields allowed nulls, or were string type fields instead of some numeric type.  

In general - since the sub query is being executed for every row, that's where you want to look first to get the most gains.
ASKER CERTIFIED SOLUTION
Avatar of Ryan
Ryan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ryan

ASKER

The make table worked fine. I just have to do a drop table query first, then make table, then the query that uses the new table.

Cut it from 10min to 10 seconds.
Avatar of modus_operandi
modus_operandi

Closed, 500 points refunded.
modus_operandi
EE Moderator