[AccessD] VERY SLOW report

Barb,

  That's a pretty heavy duty report.  Without seeing it directly, it's
difficult to say if it could be optimized or not.  So a couple of general
suggestions:

1. Optimize current report - i.e. are fields that need to be indexed
indexed, move calculations up to the highest query level, etc.
2. Prebuild some of the data in temp tables.
3. Break up into separate reports -  I do understand that they need to
submit all this info, but does it really need to be on "one" report.  Is the
end customer going to know that it was 5 reports that printed a page apiece
or 1 report that printed 5 pages?

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Barbara Ryan
  Sent: Thursday, May 29, 2003 3:17 PM
  To: Access List
  Subject: [AccessD] VERY SLOW report


  I have an Access 97 report that runs VERY slowly (I mean, like an hour or
more at times!).  Any suggestions you can offer would be greatly
appreciated.

  The report header contains 5 subreports.  The detail section contains 7
subreports.  Each subreport contains,on average, appr. 3 detail lines and 1
total line. There are appr. 36 calculated fields per subreport.  There are
54 calculated fields in the detail section of the main report --- SO, 36 x 7
subreports + 54 = 306 calculated fields in each detail section of the
report.  Each detail section is printed on a new page.

  The report is often more than 100 pages.  There are appr. 20 tables used
in the various queries of the main report and subreports.

  The client needs all of this info in one report.  It is their Proposal
that they submit when bidding on a contract, containing all of the labor
rates, fringe benefits, overhead, profit, etc. for each union trade and
level (i.e., carpenter foreman, carpenter journeyman, etc.) for straight
time/overtime/double time for first shift, second shift, third shift.

  Help!
  Barb Ryan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030529/90c8807d/attachment-0001.html>