Subreports speed 1

[bonkdawn]

Hello, all!
I have an Access report that has two subreports embedded in it. They are linked to the main report by a single field ([JTID]). Subreport #1 gathers information about equipment used on each JTID. JTID gathers information about materials used on each JTID, and does some relatively simple currency calulations based on that info. The main report gathers information on employee job hours and tasks performs on each JTID. It then sorts and groups by Invoice then CUSTID, then Code, then JTID. The subreports are embedded in the JTID header section of the report. The main report then subtotals materials, equipment and labor costs by JTID, then by Invoice, and then by Grand Total of all the Invoices. There are some deeper calculations in the subtotals based upon what Code the JTID falls under. All three underlying queries run within a sceond or two, as do the reports when I run them separately. When I run the main report with the subreports in it, it is taking 15-18 mins to run 222 pages.

I have tried several things to speed up this report by looking up articles in MS KB, but nothing has worked. I do not understand why they work fine separately, but dog so much when together.

TIA for any help!!

Dawn

 

[lameid]

First thoughts are to ensure that JTID (and all fields being sorted on) is indexed in all tables in underlying queries and to remove any sorting that is inconsitent with the sorting and grouping of the report and if possible remove grouping in the queries.

Second thought is that Access seems to run the same queries multiple times when grouping and sorting in reports. Based on that I have a couple of crazy ideas to try.

1) Group by something that will not change for JTID like Invoice after JTID (so you will group by Invoice twice, before and after JTID). Next move your subreports to the new header. This may help prevent the subreport from being requeried.

2) Remove master and child fields. Add an invisible control to the header of the main form containing JTID (master field). Make the record source of the subreports reference this field (be sure to give the control a different name than the field name and use the control's name and not the field name in the query). Finally, in the on format event of the header (code), requery the subreports if necessary.


I'm not sure either will help but those are the kind of shot in the dark things that come to my mind.

One other thought... Any reason not to put the subreports in the detail section? If you can do that I'd try that first. Also, if you are doing any calculations in the main query that could be done in the main report, move them to the main report and remove them from the query. If the query is going to be run a million times because of report grouping, make it simpler.

 

[bonkdawn]

lameid -

Thank you for you ideas! The JTID is indexed in all related tables... I will try your other ideas and let you know how I make out.

Thanx, again!

 

[bonkdawn]

lameid...

Thank you for your ideas... because of the way the client wants to see the report set up, I could not get any of them to work... BUT... it did make me think of running the subreports off of tables created by the queries and that worked perfectly!! Thank you, again!!

Dawn

 

[fullkeel]

If you are doing your math on the report I would move these calculations to your queries to see if that would improve the reports output. Another item that can slow down a report is with code behind the report, if you can move that to your queries you will increase throughput.