Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291

    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
    16,716
    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
    291
    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:	37 
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 Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    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
    16,716

  6. #6
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Orange,

    I had a little bit of a tough time following your improved speed post and the original post. I think that guy had several groupings and rankings combined. I put this together from what I found in your post:

    Code:
    SELECT RankingQuery.pID, RankingQuery.CategoryRank
    FROM (SELECT Dupe1.pID, COUNT(*) as CategoryRank
    FROM q_AllSub AS Dupe1 INNER JOIN q_AllSub AS Dupe2
    ON (Dupe1.pID = Dupe2.pID)
    GROUP BY Dupe1.pID
    )  AS RankingQuery;
    And this gave me the correct number of records, the same as my sq_OneSubEach gave me. Although I dont understand why the CategoryRank has weird numbers, several 1s but other numbers like 196, so I am not sure what COUNT(*) is actually counting, could someone explain that to me?

    That query gives me the right number of records but does not contain any of the important information so I next tried to add in the sID which is the primary key for the submissions table, this was my attempt:

    Code:
    SELECT RankingQuery.pID, RankingQuery.sID, RankingQuery.CategoryRank
    FROM (SELECT Dupe1.pID, Dupe1.sID, COUNT(*) as CategoryRank
    FROM q_AllSub AS Dupe1 INNER JOIN q_AllSub AS Dupe2
    ON (Dupe1.pID = Dupe2.pID)
    GROUP BY Dupe1.pID, Dupe1.sID
    )  AS RankingQuery
    ORDER BY RankingQuery.sID;
    Which I believe the group by is screwing with what is displayed and it returns way more records. I then tried adding in the code:
    Code:
    WHERE (((RankingQuery.CategoryRank)=1))
    and it gave me less records than I have in my original query.

    I tried thinking about that max query idea and I went back to the basics and tried to use pID as my group by and then get the Max of Due and that returns the right number of records, but I need the sID associated with each of those records and if I add sID to the query it becomes a group by and ruins what records are returned.

    Code:
    SELECT Project.pID, Max(Submission.Due) AS MaxOfDueFROM Project INNER JOIN Submission ON Project.Tracking_num = Submission.sTracking_num
    GROUP BY Project.pID;
    Any direction on either of these queries would be appreciated.

    Thank you

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In real simple terms --what exactly is the requirement? The sample I mentioned was looking at the Top 5 per group. Your queries don't identify a Top X, so I'm not sure what you are seeking.

  8. #8
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    I simplified the database as much as I could so hopefully that will make it easy to see.
    2 tables, Project and Submission :
    Click image for larger version. 

Name:	Tables.JPG 
Views:	23 
Size:	39.0 KB 
ID:	42216


    The following query just pulls all submissions and their associated project information:
    Click image for larger version. 

Name:	All submissions query.JPG 
Views:	24 
Size:	30.5 KB 
ID:	42211

    Then this query pulls the most recent submission for each project and the associated project info based on the most recent due date and if there are 2 of the same due dates then it pulls the most recent sID. The follow SQL does produce the results that I want as shown in the query results below. Runs great with this few records but I have other queries after this and thousands of rows of data, so it is just too slow and as you pointed out the TOP query might be a large cause to the slow query.
    Click image for larger version. 

Name:	one submission each query sql.JPG 
Views:	24 
Size:	22.3 KB 
ID:	42213Click image for larger version. 

Name:	Desired results.JPG 
Views:	22 
Size:	37.6 KB 
ID:	42212

    So that is it, start from those tables to finish is the datasheet view of sq_OneSubEach as shown above. I have attached the simplified database here:
    simplifiedforquery.zip

    If there is anything else that would help please let me know.
    Thank you.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A few questions for clarity.

    Why are you using a right join? Can you have a Submission that doesn't relate to a Project? If so, please explain.
    Your tables are joined on Tracking_num. I would have expected , if a submission must be related to a project, that there would be a FK in Submission (ProjectID_FK or similar). I realize the tracking_num are indexed no dups, but wonder(curious) why you did that.

    With only 7 records there is no ability to get any timing differences to various query set ups. Is it possible to get a database with more data?

    In simple terms you have Projects and each Project may have a number of Submissions. Your query is to identify the latest submission information (agency, tracking num, review etc) for each project. Normally you would use Due date, but you indicated if there are multiple records for same Due date then used the most recent sID, But in the example data your latest sID (for pID 3) relates to Review2, even though you have a Review3 for that Project/Submission.

    You could try this query (using sID rather than Due because of 2 records with same Due)

    Code:
    Select project.pid
    ,project.agency
    ,project.title
    , Submission.* from Project INNER JOIN Submission ON Project.Tracking_num = Submission.sTracking_num
    where submission.sid in
    
    (SELECT  Max(Submission.sid) AS MaxOfsID FROM Project INNER JOIN Submission ON
    Project.Tracking_num = Submission.sTracking_num
    GROUP BY Project.pID);
    Last edited by orange; 06-18-2020 at 06:48 AM. Reason: spelling

  10. #10
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Orange,

    A project can exist that does not have a submission yet, but every submission MUST have a project for which it is related.

    Every project has a Tracking_num and it must be unique, I could have used that as the primary key of the Project table but I guess I am just used to using AutoNumbers. If there is a better way to setup the table let me know and I will try that. In the Submission table the sTracking_num (essentially the foreign key) is indexed with duplicates allowed as it has a one to many relationship with the Project table.

    There is a zipped database file above in the middle of the post from 5/18/2020 that has 473 projects and 1367 submissions that should be slower. I thought with that many records it might be hard to see if the query was producing the correct results. My sq_OneSubEach query does produce the correct results, just very slow and after I add in other queries that pull from this query it slows down even more.

    In simple terms, each project can have many submissions. I want to identify the latest submission information and its corresponding project information based on the due date, if there are 2 submissions with the same due date for the same project then it would pull the greater AutoNumber (sID).

    Thank you.
    -Dan

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

  12. #12
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Orange,

    My apologizes, I did not see the updated post.

    But in the example data your latest sID (for pID 3) relates to Review2, even though you have a Review3 for that Project/Submission.
    You are correct, but I am fine with max of sID as it is the best I can do. The review field they dont always put in numbers and are not always consistent.

    The query you posted is much faster, but still needs the logic for going for the due date first. If I sort the first query by sID and then just pull the max due date in a similar query as you have posted will that work?

    Thanks.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Possibly. What is the number of records you consider the correct number?

    I used this query and the result is 430 records.

    Code:
    Select project.pid
    ,project.agency
    ,project.title
    , Submission.* from Project INNER JOIN Submission ON Project.Tracking_num = Submission.sTracking_num
    where 
     submission.due in
    (SELECT  Max(Submission.due) AS MaxOfDue FROM Project INNER JOIN Submission ON
    Project.Tracking_num = Submission.sTracking_num 
    GROUP BY Project.pID)
    AND
     submission.sid in 
    (SELECT  Max(Submission.sid) AS MaxOfsID FROM Project INNER JOIN Submission ON
    Project.Tracking_num = Submission.sTracking_num 
    GROUP BY Project.pID)
    I notice that your q_OneEach returns 473.

    Does that signify something for you??

    On thinking about my query--perhaps the 430 would be those records where the Due is Max and the sID is Max????
    Last edited by orange; 06-18-2020 at 02:36 PM. Reason: spelling

  14. #14
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    I notice that your q_OneEach returns 473.
    The q_OneEach query takes all the records from the query we are working on (sq_OneSubEach) and adds in all the projects which do not have submissions.

    432 is what my TOP query sq_OneSubEach produces. I used your query and compared the sIDs from both and the sIDs that your query didnt pick up were 622 and 987. I looked up the pID associated with sID 622 which the pID = 175 and looked at all the submissions for that pID . I thought I would find some submission with the same due date or some other clue as to why it misses 2 records but I didnt not see anything special. See the image below:

    Click image for larger version. 

Name:	skipped sID.JPG 
Views:	21 
Size:	25.5 KB 
ID:	42229

    I did notice that your query still pulled the max sID on the ones where there is a more recent due date on another submission. I tried switching the
    Code:
     submission.due in(SELECT  Max(Submission.due) AS MaxOfDue FROM Project INNER JOIN Submission ON
    Project.Tracking_num = Submission.sTracking_num  GROUP BY Project.pID)
    with the sID part and it still gave me 430 records with the greater sID even when a more recent due date existed on another submission.

    If it looks like this is too difficult of a problem let me know and I will try and figure out what I can give up

    Thank you for your help on this.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I seem to be having some issues with Access or the PC. I looked at your table designs and saw no PK and no indexes on Project and Submission.A few minutes laters I did same thing and they were present???

    Try these 2 queries,

    QueryJED1 --- goes first to collect some info

    Code:
    SELECT Project.pID
    , Submission.sTracking_num
    , Max(Submission.Due) AS MaxOfDue
    FROM Project INNER JOIN Submission ON 
    Project.Tracking_num = Submission.sTracking_num
    GROUP BY Project.pID, Submission.sTracking_num;
    QueryJED2 uses the first query

    Code:
    SELECT Project.pID
    , Project.Tracking_num
    , QueryJED1.sTracking_num
    , QueryJED1.MaxOfDue
    , Submission.sID
    , Submission.ReviewNum
    , Submission.Reviewer
    , Submission.Completed
    , Submission.Due
    FROM (QueryJED1 INNER JOIN Project ON 
    (Project.pID = QueryJED1.pID) AND (Project.pID = QueryJED1.pID) AND 
    (QueryJED1.pID = Project.pID)) INNER JOIN Submission ON (QueryJED1.MaxOfDue = Submission.Due) AND (QueryJED1.sTracking_num = Submission.sTracking_num)
    ORDER BY Project.pID;
    I just picked some fields from Submission, you can refine/extend to match your needs.
    Perhaps someone else will take a look and offer some SQL.

    Good luck.

Page 1 of 2 12 LastLast
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