Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    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?

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Maybe yo need to do a "Compact and Repair Database".

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •