locked
MS Access SQL Query taking abnormally long due to WHERE clause - WHY? RRS feed

  • Question

  • Hi Folks -

    I have the following query:

    Select * from [MDM_Project_Portfolio_Reference]
    WHERE ([Name] like 'PFP-*'
    And [Parent Node] like 'PFI-*'
    And Format( [Last Changed On],"yyyymmdd") >= Format(Date()-7,"yyyymmdd"))
    OR [Parent Node] IN (SELECT * FROM [qry_PFP_PFI_Check])

    It's taking quite a long time to run.  However, I run each piece of the WHERE clause, each piece finishes in just a few seconds.  Why is it that when I combine with the AND/OR, it takes much longer?

    Tuesday, November 10, 2020 10:42 PM

All replies

  • it would surprise me if that was not slow!!  

    2 text wild cards, along with a sub query....

    you might split them into 2:

    Select * from [MDM_Project_Portfolio_Reference] WHERE ([Name] like 'PFP-*' And [Parent Node] like 'PFI-*' And Format( [Last Changed On],"yyyymmdd") >= Format(Date()-7,"yyyymmdd"))

    Select * from [MDM_Project_Portfolio_Reference]
    WHERE [Parent Node] IN (SELECT * FROM [qry_PFP_PFI_Check])

    then append the rows together into a temp table... it might be faster overall even though there are more steps...

    Wednesday, November 11, 2020 2:48 AM
  • First, make sure you applied an index to each of the fields you filter on.

    Also, do not format the date field as you loose the index on that field:

    And [Last Changed On] >= DateAdd("d", -7, Date())

    Gustav Brock


    Friday, November 13, 2020 10:11 AM
  • Using "IN" clauses in Access is generally very slow.  Use a left join  to qryPFP_PFI_Check instead and check for non-null values in qryPFP_PFI_Check.[Parent Node] , and agreed with Gustav's assessment of the dates in your WHERE clause...that line is not doing what you think it is doing, and whatever it is in fact doing, it is doing very inefficiently.

    Something like

    Select * from [MDM_Project_Portfolio_Reference]
    left join qry_PFP_PFI_Check
    on [Parent Node] = qry_PFP_PFI_Check.[Parent Node] 
    WHERE ([Name] like 'PFP-*'
    And [Parent Node] like 'PFI-*'
    And[Last Changed On] >= DateAdd("d", -7, Date())
    OR [Parent Node] is not null


    -Bruce



    • Edited by Bruce Hulsey Wednesday, November 18, 2020 8:23 PM
    Wednesday, November 18, 2020 8:19 PM