Slow Opening Invoice

[AggieLJ]

I have built an Invoicing system for the company I work for. I have attached a example copy of the invoice I designed.

The only major issue I am having with my system is that the report we use as an invoice is slow opening (around 20-40 secs) for one page.

This report has multiple sub reports:

Payments
Refunds
Notes
Footer (srpt source is variable)


I suspect this is the main reason it is slow, but don't see a way around it.

There can be multiple payments, multiple refunds, notes, and the footer changes depending on whether the invoice is a bill, paid in full, or overpaid.

All of the sub-reports are allowed to auto-height except for the footer which is done through VBA (because I have to first change the source, then match the height to that particular sub-report).

Each part of the report has its own query, and is linked by the InvoiceID.

Any suggestions on making the report faster while still maintaining an Invoice look?

 

[PNGBill]

I would have thought you could achieve this look without any sub reports.

Have you timed the underlining sql's to process when removed (copied) from the report?

This may show which of the sub reports / sql's / vba's is the cause.

Our system can produce a 3,000 page report in 2 to 3 seconds.

Remove the logo's, one at a time and see if this makes a difference.

A basic Invoice that gets data from say 4 tables. OrderHeader, OrderDetail, CustomerTable & ??? should be able to use just one SQL for it's data source and produce a smart looking Invoice document complete with logo etc.

 

[AggieLJ]

Our other reports with 100-200 records each open almost instantaneously, even with the same header as the invoice.

Our invoice is the product of about 12-14 tables... The people I work for are concerned about hitting the 2 GB limit, as a consequence, the laboratory testing data each has its own storage BE. All of which total about 40 MB as of today.

Even besides that point, I have tried having no subforms, and used the grouping / sorting feature, but if there were multiple payments, the all of the main test data would be reprinted for each payment. Also, if there were multiple payments AND multiple refunds on the same invoice, WOW... it would get ugly...

 

[AggieLJ]

I finally have an update... I started with the complete Invoice, then subtracted one thing at a time until there was nothing left.

Complete Invoice: 21 secs
- Footer srpt: 21 secs
- Notes srpt: 21 secs
- Totals: 16 secs
- Dividing Line: 12 secs
- Refunds srpt: 12 secs
- Payments srpt: 11 secs
- Cust. Address: 11 secs
- Logo and everything else: 11 secs

I guess this means that I have something really wrong with the query that drives the main report... and if I fix that, I should be good!

PS - Why would a simple dividing line add 4 seconds to the load time?

 

[PNGBill]

think about your Invoice Structure and consider if some data is not needed and can be calculated in the report and see if this assists.

I know if we have One document with many records it seems easier to let the Report handle the Header details ie DLookup for the Name and Address etc and use the query to handle the Data for the detail part of the report.

This simplifys the query and doesn't appear to slow the report.

If you run 200 Invoices in a batch then it may not work so well.