Results 1 to 3 of 3
  1. #1
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

    Improving Speed on a Top N query

    This post is the result of assisting a poster with a requirement to select the Top N records per group. This was a learning exercise in which the speed was improved tremendously by using a Ranking Query.

    The original solution was a traditional:
    Code:
       traditional Top 5
    SELECT [running lines].horse, [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].[Date]
    FROM [running lines]
    WHERE ((([running lines].[date]) In
     (
     select top 5 XX.[Date]  from
       [running lines] as XX
         WHERE XX.horse  = [running lines].horse 
         Order By XX.[Date] desc     ))
     )
    GROUP BY [running lines].horse, [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].[Date]
    ORDER BY [running lines].horse, [running lines].[Date] DESC;
    which worked reasonably well with 2000 records. But the poster had several thousand(30,000) and indicated the full query took 30+ minutes to process, and over 45 min for 60000+ records.

    Another poster suggested to limit the records by Date and suggested this sql

    Code:
      with Date cut off
    SELECT [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].horse, [running lines].Date, [running lines].Race
    FROM [running lines]
    WHERE [running lines].date In (SELECT TOP 5 XX.[Date] 
        FROM [running lines] as XX 
        WHERE XX.horse = [running lines].horse AND XX.[Date]
        ORDER BY XX.[Date] DESC) AND [running lines].date)>=#6/1/2014#
    GROUP BY [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].horse, [running lines].Date, [running lines].Race
    ORDER BY [running lines].rctrack, [running lines].RCDate, [running lines].rcRace, [running lines].horse, [running lines].Date DESC;
    I did some searching, trial and error which resulted in this query which was about 10 to 25 times quicker than the traditional Top N on the sample data.

    Code:
     with Ranking query
    SELECT *
    FROM (SELECT a1.rctrack, a1.RCDate, a1.rcRace, a1.horse, a1.Date, a1.Race, COUNT(*) AS CategoryRank 
    FROM [Running Lines Id] AS a1 INNER JOIN [Running Lines Id] AS a2 
    ON (a1.horse = a2.horse) AND (a1.date<= a2.date) 
    GROUP BY a1.rctrack, a1.rcdate, a1.rcrace, a1.horse, a1.date, a1.race
    )  AS RankingQuery
    WHERE (((RankingQuery.[CategoryRank])<=5) )
    ORDER BY RankingQuery.rctrack, RankingQuery.RCrace, RankingQuery.horse, RankingQuery.CategoryRank;
    Here are some relative times:


    Code:
    LatestQueryByOP  <------------------traditional Top 5
    Start  31/12/2014 2:56:28 PM
    End    31/12/2014 2:56:32 PM
    4453  ~millisecs
    
      
    RawbRevised  '<----------------------------the revised Top 5 with Date cut off
    Start  31/12/2014 5:48:52 PM
    End    31/12/2014 5:48:54 PM
    1828  ~millisecs
    
    QuickerQueryJED  <------------------the quicker query
    Start  31/12/2014 2:56:32 PM
    End    31/12/2014 2:56:32 PM
    169  ~millisecs
    Bottom line: Consider a ranking query approach when trying to speed up a Top N query.
    Last edited by orange; 02-27-2017 at 08:50 AM. Reason: original link was lost --recreated it.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks Orange. Very useful information to share. Happy New Year.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Same to you Allan -- all the best in 2015.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Improving a querry for faster result
    By Aloupha in forum Queries
    Replies: 7
    Last Post: 10-31-2013, 01:07 PM
  2. How to speed up my Query
    By Cfish3r in forum Queries
    Replies: 2
    Last Post: 10-18-2012, 02:55 AM
  3. Slow query - Help to speed up?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 05-26-2011, 01:23 PM
  4. Improve query speed
    By FMJO in forum Queries
    Replies: 3
    Last Post: 02-10-2011, 08:37 AM
  5. Replies: 1
    Last Post: 11-17-2010, 08:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums