none
Slow SQL Query Issue, Access 2013 RRS feed

  • Question

  • I'm trying to (SQL) query a single table of 200K records, no primary key.  The table is coming from another source, so I can't enforce data integrity on their end, but I want to check it on my end.  I want the query to check that a particular field IS NOT NULL and is not a duplicate.  If I query for either criteria, the result is instant, but if I combine them on the same query, it takes forever.

    Checking for duplicates:

    SELECT SO_KEY, ADDRESS, ADDRESS2
    FROM GISsitus AS gis
    WHERE SO_KEY In (SELECT [SO_KEY] FROM [GISsitus] As Tmp GROUP BY [SO_KEY] HAVING Count(*)>1 );

    Checking for NULL:

    SELECT SO_KEY, ADDRESS, ADDRESS2
    FROM GISsitus
    WHERE SO_KEY IS NULL;

    Trying to do both, method 1:

    SELECT SO_KEY, ADDRESS, ADDRESS2
    FROM GISsitus AS gis
    WHERE SO_KEY In (SELECT [SO_KEY] FROM [GISsitus] As Tmp GROUP BY [SO_KEY] HAVING Count(*)>1 ) OR
    SO_KEY IS NULL;

    Method 2: 

    SELECT SO_KEY, ADDRESS, ADDRESS2
    FROM GISsitus AS gis
    WHERE SO_KEY In (SELECT [SO_KEY] FROM [GISsitus] As Tmp GROUP BY [SO_KEY] HAVING Count(*)>1 ) 
    UNION
    SELECT SO_KEY, ADDRESS, ADDRESS2
    FROM GISsitus as gis
    WHERE SO_KEY IS NULL;

    Both of them take forever and I CTRL+Break to stop them.  Any clue as to how I can check for both duplicates and NULL values for SO_KEY?



    Wednesday, December 21, 2016 7:39 PM

All replies

  • Hi BizzyM,

    Thanks for visiting our forum. Then this forum mainly focus on questions and feedback for Office client, since your issue is more related to developing issues involving Access, I'll move your question to the following MSDN forum for Access:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, December 22, 2016 2:58 AM
  • What happens if you use Method 2, but use "UNION ALL" instead of just "UNION"? Using just "UNION" will remove duplicates from the result set which can indeed take a long time.

    Matthias Kläy, Kläy Computing AG

    Thursday, December 22, 2016 8:17 AM
  • If none of the above helps, maybe it's quicker to load the 200K remote rows into a temporary local staging table and then do the quality check locally. Or maybe you can invoke the test procedure on the remote system. Access may be retrieving the same rows multiple times depending on the query plan it uses. When done locally it's probably still very fast, but the remote access time could be the problem.

    What is the remote source, and how are you connected to it?


    Paul

    Thursday, December 22, 2016 9:45 PM