Query speed over a large number of records

[GrantRawlinson]

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):

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:

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:

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.

[help_me_with_access]

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.

[GrantRawlinson]

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:

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!