Significant reduction in MS Access query performance

[Ash]

Hello,

I am running a 2 gig database has about 10 Million records that I am
trying to sum up into about 60000 records. I have about five group by
clauses ( size/region/years/type/business). Of this I have two sizes,
five regions, 18 years, 50 types and 64 businesses. should my group by
clause say group by size, region, years, type business or should it be
the other way around -- group by business, type, years ... for better
results.

Also I tweaked the registry of my laptop to optimize swap file
performance and the above query that used to take 2 hrs now takes 18+
hrs and still does not execute. (i did not take a backup of the
registry and am struggling to remember what key i modified to have this
huge reduction in performance).

Also, is 2-3 hrs normal for this kind of summing up ? how many indexes
must i create in this query -- all/none/ ?

thanks in advance.

 

[Brendan Reynolds]

2GB is the maximum capacity of a Jet database, so you should stop doing
anything at all with that database and switch to a database engine with a
larger capacity immediately. I have heard reports that Jet fails rather
abruptly when it hits the limit. Do not attempt to create any new indexes,
as this will increase the size of the database and may put it over the 2GB
limit. Ensure that you have recent reliable back ups.

 

[Ash]

Thanks I use the 2gb as a data store and link to it from another db for
queries/calcs. i am hoping someone will show me a link where i can
connect ms access performance with swap file setup and registry
entries.

 

[Brendan Reynolds]

That doesn't change my advice.

Sorry I can't help with the registry setting. I've always managed my swap
file via the control panel. You might want to try asking that question in a
forum dedicated to your version of Windows.