Results 1 to 3 of 3
  1. #1
    GrantRawlinson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    2

    Query speed over a large number of records

    Just wondering if someone can help me with an access query that's causing me problems.

    I have a few sub queries to identify a customers last order date and their second to last order date:

    To identify their last order date (not necessary to have this separately I suppose):

    Code:
    SELECT T1.email, max(T1.order_date) AS order_date
    FROM invoicedsalesoldsystem AS T1
    GROUP BY T1.email;
    To identify their second to last order date:

    Code:
    SELECT T1.email, max(T1.order_date) AS order_date
    FROM invoicedsalesoldsystem AS T1
    WHERE T1.order_date < (SELECT max(T2.order_date) FROM invoicedsalesoldsystem T2 WHERE T2.email=T1.email)
    GROUP BY T1.email;
    Finally, I have a third query which draws upon these two and does some other calculations:

    Code:
    SELECT DISTINCT
    invoicedsalesoldsystem.email,
    max(invoicedsalesoldsystem.firstname) as firstname,
    max(invoicedsalesoldsystem.surname) as surname,
    lookupMaxDate.order_date,
    lookup2ndMaxDate.order_date,
    datediff("d", lookup2ndMaxDate.order_date, lookupMaxDate.order_date) as daysvar,
    min(invoicedsalesoldsystem.order_date) as firstorderdate,
    datediff("d", min(invoicedsalesoldsystem.order_date), lookupMaxDate.order_date) as totaldaysvar,
    round((datediff("d", min(invoicedsalesoldsystem.order_date), lookupMaxDate.order_date)/count(invoicedsalesoldsystem.order_number)),0) as frequency,
    count(invoicedsalesoldsystem.order_number) as totalorders,
    sum(invoicedsalesoldsystem.gbp_total) as totalrevenue,
    max(invoicedsalesoldsystem.currency) as crncy
    
    FROM (invoicedsalesoldsystem INNER JOIN lookupMaxDate ON invoicedsalesoldsystem.email = lookupMaxDate.email) LEFT JOIN lookup2ndMaxDate ON invoicedsalesoldsystem.email = lookup2ndMaxDate.email
    
    GROUP BY
    invoicedsalesoldsystem.email,
    lookupMaxDate.order_date,
    lookup2ndMaxDate.order_date;
    This all works fine when I tested it over 100 records. However, when I test it over the full dataset (500,000+ records) it just runs indefinitely. I left it for an hour once and it still hadn't completed so I had to cancel the execution.

    Can someone advise me on a way to get this to run over a large dataset within a few minutes rather than potentially several hours?



    Thanks,

    Grant.

  2. #2
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    try splitting up into multiple tables? stacked queries? combine the effort with coding? all of those are options.

    i would say that using more than one aggregate functions that are not domain functions (e.g. - MAX instead of DMAX) in a single query is probably not a good idea. more or less because it causes ace to work too hard. or is it that domain functions are tougher on the ops? heck, I can't remember. but it doesn't matter...neither are good if too many of them are used.

    in visual basic, I believe you can run complex code routines such that you can overstack memory and acc will just crash as a result.

    what I've found is that if you break up operations, and/or give the engine a break from time to time, it works better.

    try that. or move to a more powerful app, like those on servers.

  3. #3
    GrantRawlinson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    2
    Thanks for that.

    I've started to try and break things up but even testing this part on its own isn't able to successfully execute:

    Code:
    SELECT T1.email, max(T1.order_date) AS order_date FROM invoicedsalesoldsystem AS T1 WHERE T1.order_date < (SELECT max(T2.order_date) FROM invoicedsalesoldsystem T2 WHERE T2.email=T1.email) GROUP BY T1.email;
    I can't really think of a way to make this any simpler? Moving to a more powerful app isn't really an option at the moment so I'm kind of stuck!

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

Similar Threads

  1. Replies: 6
    Last Post: 07-25-2011, 01:54 PM
  2. count the number of records from a query
    By Nixx1401 in forum Queries
    Replies: 4
    Last Post: 05-24-2011, 06:45 PM
  3. The number of records in a query
    By Persist in forum Programming
    Replies: 3
    Last Post: 07-13-2010, 06:23 AM
  4. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 PM
  5. Linking large number of forms and tables
    By jlcaviglia-harris in forum Forms
    Replies: 2
    Last Post: 04-17-2009, 09:19 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