Results 1 to 15 of 15
  1. #1
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15

    Very Slow Query


    I have a query that does what I want - ie it selects all the rows from a table and displays just the ones with the MIN date. ie there will be several rows with the same ID number but different dates and we only want the rows with the first/min date returned. As its been setup with a sub query and the original table has 35,000 records this is likely why it is slow. Is there any way to speed this process up by doing it a different way? It will probably slim down the results to about 7,000 if we just have the ones with the earliest (and not all) date. The masters table it takes its data from is also a query in actual fact but it just takes a few seconds to run normally.

    Here is the query code:

    Code:
    SELECT PIN, P__Business_Unit, EPISODEUNIQUEKEY, D_S__Episode_Start_Date, D_S__Finish_Date, S__Episode_Type, CLINICALENCOUNTERUNIQUEKEY, D_S__Attendance_Date, S__Event_Type, S__Seen_By, S__Outcome
    FROM [2-4 Create list of all Referrals with a Management Report Event] AS ESM
    WHERE ESM.D_S__Attendance_Date=DMin("D_S__Attendance_Date","[2-4 Create list of all Referrals with a Management Report Event]","EPISODEUNIQUEKEY=" & "'" & ESM.EPISODEUNIQUEKEY & "'");

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If this is what you're doing, you might try it and see if it's faster:

    http://www.baldyweb.com/LastValue.htm

    The domain aggregate function is likely slowing yours down.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15
    After a fair bit of trying the following works best hopefully there are no obvious issues?
    Code:
    SELECT MIN([2-4 Create list of all Referrals with a Management Report Event].D_S__Attendance_Date) AS MinDate, [2-4 Create list of all Referrals with a Management Report Event].EPISODEUNIQUEKEY, FIRST([2-4 Create list of all Referrals with a Management Report Event].CLINICALENCOUNTERUNIQUEKEY) AS EventKey
    FROM [2-4 Create list of all Referrals with a Management Report Event]
    GROUP BY [2-4 Create list of all Referrals with a Management Report Event].EPISODEUNIQUEKEY;

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also ensure all fields used as criteria are indexed

  5. #5
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15
    Quote Originally Posted by Ajax View Post
    Also ensure all fields used as criteria are indexed
    Thanks can you provide a little further detail on what this means? I created an alias for all the fields that are not grouped by (ie EPISODEUNIQUEKEY is the only one with no alias as it is used in the group by clause).

    I am going to pick some random rows and see if they pull through as expected from the original table as a test but wandered if folk thought this looked like a decent solution.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    criteria appears after the WHERE or HAVING statement in SQL - you don't use this in the sql posted but you refer to a query called '2-4 Create list of all Referrals with a Management Report Event' which may use criteria

  7. #7
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Open your table in design view. For each of the fields in your criteria of your query, select Index. Look at this video for an explanation of why this helps.

    http://www.datapigtechnologies.com/f...earchform.html

  8. #8
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15
    The solution above only takes a second or two to work as opposed to an hour or so before. There are no criteria fields in this query, one in the previous and 3 I think in the initial make table query. None of them are indexed. Is there any need to create them if the query speed is ok just now? The accuracy of the data being returned is what I am mainly concerned with now that the speed issue is resolved!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The other values returned may not be from the record with the min date.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is there any need to create them if the query speed is ok just now?
    No, just good practice for fields regularly used for criteria or sorting

  11. #11
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    The other values returned may not be from the record with the min date.
    Please expand on this please!

    As I said iv tested quite a few randomly picked rows returned and they do return all the fields from the MIN date row listed for that Episode.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Each Min/First/etc field in your query operates independently of each other. To illustrate, I could have

    SELECT KeyField, Max(DateField) As MaxDate, Min(DateField) As MinDate
    FROM TableName
    GROUP BY KeyField

    Hopefully it's obvious that the 2 dates returned would not likely be from the same record, they would be the min and max dates for each key value, across all records with that key value. In your case, the First() may return the value from the record with the Min() date, but it may not. The 2-query solution I posted will (presuming the date field isn't repeated for that date).

    Also, First may or may not be pulling what you want:

    https://support.microsoft.com/en-us/kb/208190
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    If this is what you're doing, you might try it and see if it's faster:

    http://www.baldyweb.com/LastValue.htm

    The domain aggregate function is likely slowing yours down.
    Thanks for your help so far. You were right the FIRST was occasionally returning odd results so have ditched the last effort!

    I tried your code above but it wasnt much (if any) faster query still took over an hour and I gave up.

    I cant believe there isnt a simple way of grouping rows based on one field (EPISODEUNIQUEKEY) and then only returning the one with the earliest date for each Episode? I admit the sample size to go through is quite large being about 35,000 records and it will narrow down to maybe 13,000 records. Surely the computer can process this quicker than several hours though it is the 21st century~!

  14. #14
    83dons is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    15
    Hello finally got the following query to work! Weirdly it only works in Access 2007 as opposed to Access 2003 which tries to 'optimise' the code and breaks it!

    Code:
    SELECT tQ.[PIN], tQ.[P__Business_Unit], tQ.[EPISODEUNIQUEKEY], tQ.[D_S__Episode_Start_Date], tQ.[D_S__Finish_Date], tQ.[S__Episode_Type], tQ.[CLINICALENCOUNTERUNIQUEKEY], tQ.[D_S__Attendance_Date], tQ.[S__Event_Type], tQ.[S__Seen_By], tQ.[S__Outcome]
    FROM (SELECT 
            [2-5 All Man Report Events within Man Referral Episodes].[PIN], 
            [2-5 All Man Report Events within Man Referral Episodes].[P__Business_Unit], 
            [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY], 
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Episode_Start_Date], 
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Finish_Date], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Episode_Type], 
            [2-5 All Man Report Events within Man Referral Episodes].[CLINICALENCOUNTERUNIQUEKEY],  
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Event_Type], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Seen_By], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Outcome]
           FROM
                [2-5 All Man Report Events within Man Referral Episodes]
            INNER JOIN 
            (
                SELECT 
                    [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],  
                    Min([2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date]) AS nEventDate
                FROM 
                    [2-5 All Man Report Events within Man Referral Episodes]
                GROUP BY 
                    [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY]
            ) As tmp 
            ON 
            [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY] = tmp.[EPISODEUNIQUEKEY]
            AND 
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date] = tmp.[nEventDate]
        )  AS tQ;

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it to work. I don't see the point of the outer SELECT but perhaps I'm missing something. Does this work?



    Code:
    SELECT 
            [2-5 All Man Report Events within Man Referral Episodes].[PIN], 
            [2-5 All Man Report Events within Man Referral Episodes].[P__Business_Unit], 
            [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY], 
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Episode_Start_Date], 
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Finish_Date], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Episode_Type], 
            [2-5 All Man Report Events within Man Referral Episodes].[CLINICALENCOUNTERUNIQUEKEY],  
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Event_Type], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Seen_By], 
            [2-5 All Man Report Events within Man Referral Episodes].[S__Outcome]
           FROM
                [2-5 All Man Report Events within Man Referral Episodes]
            INNER JOIN 
            (
                SELECT 
                    [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],  
                    Min([2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date]) AS nEventDate
                FROM 
                    [2-5 All Man Report Events within Man Referral Episodes]
                GROUP BY 
                    [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY]
            ) As tmp 
            ON 
            [2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY] = tmp.[EPISODEUNIQUEKEY]
            AND 
            [2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date] = tmp.[nEventDate]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Query with Function very slow
    By dtours in forum Queries
    Replies: 8
    Last Post: 04-09-2015, 03:51 AM
  2. Query running Slow
    By mike02 in forum Queries
    Replies: 2
    Last Post: 07-26-2013, 12:47 PM
  3. slow query when asking for sum of minutes
    By sf827 in forum Queries
    Replies: 13
    Last Post: 01-04-2012, 09:00 PM
  4. Query running VERY slow
    By purple_kittykat in forum Queries
    Replies: 4
    Last Post: 07-31-2011, 12:40 AM
  5. Slow query - Help to speed up?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 05-26-2011, 01:23 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