06-30-09, 09:41 #1Registered User
- Join Date
- Jun 2009
Unanswered: Report run time is excessively long
I'm relatively new to using Access, so apologies in advance if this is something a typical user should know. I searched online for a while and was unable to find anything that looked like my problem, so I was hoping someone here might be able to help.
Iím using an Access 2000 front-end with some MySQL tables. One of my queries, qrySystemChecklist, pulls from three linked MySQL tables. This query runs in a little over a second. Thereís also a report that uses this query as its record source, and its run time is over 40 seconds, which baffles me.
I played around with it a little bit and found out that if I remove all the controls that use data from one specific MySQL table, the reportís run time speeds up to just a second or two. Does anyone know why one particular MySQL table might cause a report to lag so badly when the report features controls that link to its data? And why the query that pulls the exact same data runs almost forty times faster? My best guess is that somehow using fields from one particular table in the report causes it to rerun the query for every record, but I donít understand why that would happen or how to circumvent it.
Also, if itís useful, I tried to get around the problem by using a make-table query rather than a select query and to use the temporary table as the reportís record source. When I did this, the reportís run time, as expected, decreased to just a couple of seconds, but the query took about forty seconds to run, so the total run time from start to finish was almost identical.
That all said, does anyone know what the problem is and how to fix it?
06-30-09, 09:48 #2Registered User
- Join Date
- Apr 2005
- Zagreb - Croatia
Maybe yo need to do a "Compact and Repair Database".
06-30-09, 10:08 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
the report generator is a black box piece of code. its reamrkably effective at what it does and how it does it. however like any generalised piece of software it can get its knickers in a twist
if you have lots of group footers all using dmain functions or sum functions or stat functions that can delay the report. I have a sneakign suspiscion that instead of doing the totalling on the fly it issues a multitude of SQL statements to do the summation/count/whatever.
historically if there are several (say 5 or more) sub totals in a footer Ive done the calculation myself by placing code in the report events
as the report generator is an Access product it may well be issuing queries as JET queries rather than MySQL queries.. so making certain that you use MySQL SQL as pass through queries will always give a performance hit.
ultimately this may not be an issue, after all whats 40 seconds on a working day.I'd rather be riding on the Tiger 800 or the Norton