Results 1 to 5 of 5
  1. #1
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    283

    TOP Query slowing down dramatically

    I have a TOP query that is slowing me down by 10 to 20 times. I did some googling and it seems there are lots of posts about TOP queries and a few suggestions such as declaring a variable or using HASH joins, but these all seems to be for other SQL formats and I dont see how to make any of the suggestions work in MS Access. Anyone know how to make a TOP query not slow down? Thank you.



    Current Query:
    Code:
    SELECT q_AllSub.*FROM q_AllSub
    WHERE q_AllSub.sID IN
    
    
    (SELECT TOP 1 sID
    FROM q_AllSub AS Dupe
    WHERE Dupe.pID = q_AllSub.pID
    ORDER BY Dupe.Due DESC, Dupe.sID DESC)
    ORDER BY q_AllSub.Due;
    Thank you for any help.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852
    I suggest you post a zipped copy of your database so we can see your issue in context.

  3. #3
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    283
    Sorry about the delayed reply, took a little bit to scrub the data, attached is the database. The query in question at the moment is sq_OneSubEach, but that is just the first on my list to figure out why the database is so slow. You see it can take 20 seconds for the main page to load. If I switch the data source for the form f_AllTracking to q_AllSub it opens in less than a second. It is all the filtering of the queries to get the exact items that is slowing everything down. A while back I put together the following chart of how long it was taking each query to run, but since the database has been populated with more data it has gotten so much worse. The Database is here: Review Tracking-forum.zip

    Click image for larger version. 

Name:	Database Query Hierarchy.jpg 
Views:	13 
Size:	82.6 KB 
ID:	41953

    The Database is here:

    Review Tracking-forum.zip

    Thanks for the review Orange and anyone else who happens to take a look.
    Attached Thumbnails Attached Thumbnails Database Query Hierarchy.jpg  

  4. #4
    Cottonshirt is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    62
    Anyone know how to make a TOP query not slow down?
    have a very small dataset.

    TOP queries work by repeatedly sorting the entire dataset and are notoriously processor intensive. the strategies I have found that work the best consist of filtering your dataset (either direct filtering or running some preliminary queries) to eliminate records that will not be included in the end result.

    however, I note that your TOP query is asking for the TOP 1, and in almost all cases an AGGREGATE query looking for MAX(something) will be a lot quicker.


    good luck with your project,


    Cottonshirt

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852

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

Similar Threads

  1. TempVars Slowing Down Query?
    By jnoonan22 in forum Queries
    Replies: 4
    Last Post: 12-31-2015, 12:13 PM
  2. Function call in query slowing it down
    By sneuberg in forum Queries
    Replies: 2
    Last Post: 10-17-2014, 10:32 AM
  3. Replies: 3
    Last Post: 02-27-2014, 10:50 AM

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 - Senior Forums