Results 1 to 8 of 8
  1. #1
    Aloupha is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    26

    Improving a querry for faster result

    Hello,

    I have the following query and I am wondering if there is a better way to write it to make it faster.



    Code:
    SELECT * FROM MEMBERS
        WHERE ACCOUNT_BALANCE <= (0.10 * INVOICE_BALANCE)
          AND NOT EXISTS (SELECT * FROM ELIGIBILITY WHERE MEMBER.SSN = ELIGIBILITY.SSN)
          AND NOT EXISTS (SELECT * FROM UPDATES WHERE MEMBERS.SSN = UPDATES.SSN)
          AND CONTRACT_STATUS IN ('AC','RS','PD')
          AND INVOICE_CREATE_DATE >= cdate('2013-10-01');
    Thanks

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    Yes, but you will need to experiment as we don't know the structure of the tables involved.

    eliminate each 'And' (including 'And Not') one by one - to determine which causes the greatest impact on time.... its possible they all contribute equally but generally one is very much more impactful than others....

    determine if there is a way to write the values of the slow 'AND' to a temp table so that this sub record set is pre-set just before running your core query. this can get messy and generally something one wants to avoid - but in some cases it is a key approach to speeding up the query performance...

    another parameter is if the tables are actually excel sheets - if so it may be faster to first write them to actual Access tables and do your query on true Access tables rather than attached excel sheets.

    finally - one must be realistic in terms of the PC's horse power - - if this involves record counts above 500k then an XP machine with 500k RAM is problematic !!......

    I hope this is a little bit helpful....

  3. #3
    Aloupha is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    26
    I should have provided information on the tables.

    They are actual access tables with no primary keys. I intend to address that later on.

    My first AND NOT clause fetched 133 rows out of 9393 rows from the members table and 17802 rows from the eligibility table in 106.516 seconds. It took 111.939 seconds with the second AND NOT to fetch 25 rows.

    I was thinking that even without the primary keys it shouldn't be this slow and that something must be wrong with the way I write the query. I also wanted to avoid joining tables.

    I have XP with a decent capacity to handle this.

  4. #4
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Need more information to rewrite your query. But, you can consider to create indexes on the Access table.

    For starter, index on column "SSN" on table MEMBERS, ELIGIBILITY, and UPDATES.

    Hope this will help speed thing up.

  5. #5
    Aloupha is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    26
    Quote Originally Posted by lfpm062010 View Post
    Need more information to rewrite your query. But, you can consider to create indexes on the Access table.

    For starter, index on column "SSN" on table MEMBERS, ELIGIBILITY, and UPDATES.

    Hope this will help speed thing up.
    What information do you need?

    I had indexes but deleted to test something. I'll add them back and see the result.

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

  7. #7
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Don't know if using "OUTTER JOIN" and "IS NULL" will speed thing up.

    The query will look something like this:

    SELECT *
    FROM (MEMBERS LEFT JOIN ELIGIBILITY ON MEMBERS.SSN = ELIGIBILITY.SSN) LEFT JOIN UPDATES ON MEMBERS.SSN = UPDATES.SSN
    WHERE (((MEMBERS.ACCOUNT_BALANCE)<=0.1*[MEMBERS].[INVOICE_BALANCE]) AND ((MEMBERS.CONTRACT_STATUS) In ('AC','RS','PD')) AND ((MEMBERS.INVOICE_CREATE_DATE)>=CDate('2013-10-01')) AND ((ELIGIBILITY.SSN) Is Null) AND ((UPDATES.SSN) Is Null));

  8. #8
    Aloupha is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    26
    Quote Originally Posted by lfpm062010 View Post
    Don't know if using "OUTTER JOIN" and "IS NULL" will speed thing up.

    The query will look something like this:

    SELECT *
    FROM (MEMBERS LEFT JOIN ELIGIBILITY ON MEMBERS.SSN = ELIGIBILITY.SSN) LEFT JOIN UPDATES ON MEMBERS.SSN = UPDATES.SSN
    WHERE (((MEMBERS.ACCOUNT_BALANCE)<=0.1*[MEMBERS].[INVOICE_BALANCE]) AND ((MEMBERS.CONTRACT_STATUS) In ('AC','RS','PD')) AND ((MEMBERS.INVOICE_CREATE_DATE)>=CDate('2013-10-01')) AND ((ELIGIBILITY.SSN) Is Null) AND ((UPDATES.SSN) Is Null));
    This works. Your query gave me a result within 0.203 seconds.
    Mine produced the result in 106.518 seconds.

    I did not want to use joins, but... I'll take 0.203 seconds over 106.518 anytime.

    Thanks!

    I got slightly different results. Your query returned 5 and mine 4. I think yours is right. I'll see.

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

Similar Threads

  1. Report Opens Faster on 2nd try
    By gg80 in forum Access
    Replies: 2
    Last Post: 12-29-2012, 09:18 PM
  2. Replies: 0
    Last Post: 02-19-2012, 08:22 AM
  3. Faster code? Which one?
    By starson79 in forum Programming
    Replies: 4
    Last Post: 05-13-2011, 06:11 AM
  4. A Faster Search??
    By bladelock9 in forum Forms
    Replies: 0
    Last Post: 03-17-2011, 09:25 AM
  5. Replies: 1
    Last Post: 11-17-2010, 08:18 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