Slow report

[Zinzaba]

I have a fairly complex report that uses functions, written in VBA, as well
as sub-reports. The report takes anything from 30m to a few hours to run. The
number of records returned in the main report is not more than 12,000.

I removed the sub-reports and most of the function related fields on the
main report and then it ran within a few seconds.

Is seems as though the report runs the queries over and over again. When I
step through my code everything happens instantaneously but when the query is
run there is a delay. The database tables are linked in from SQL but if I run
the queries individually they run quickly. It is as though the main report
query is run for every group and the sub-report query is run for every detail
record. Is there any way to improve this setup?

 

[Allen Browne]

What are the functions doing?

As an example if the function opens a recordset, and you call it in a query
with 12k records (passing in some arguments), then yes: that will certainly
slow the report down. There may be a way to declare the recordset in the
General Declarations section of the report's module, OpenRecordset in
Report_Open, pass it into the recordset into the function, and close it in
Report_Close.

There could be other causes for the slow report as well. For example, if you
have sections that CanGrow or CanShrink and must KeepTogether, with Group
Headers that must stay with first detail (or all details), mix in some
ForceNewPage properties, and it is not difficult to have the poor old report
formatting and retreating many, many times before it finally figures out
where a page must break to meet all those rules. Perhaps you could make a
copy of the report, turn all this stuff off, and see if it makes a
difference.

In general, VBA functions and event procedures are slower than built-in
functionality such as conditional formatting.

 

[Zinzaba]

Thanks for the reply.

I do have functions that opens recordsets but when I step through them there
is no delay here. Rahter there is a delay when the query is "rerun" for every
group or subreport. Is this not the cause of the problem?

Do you provide a service where you can look at reports and give some advice?

 

[Allen Browne]

To open a connection to the file containing the data, assign the memory and
management required for the recordset, open it, read the data, then flush
the recordset, close it and close the connection to the file takes a
significant fraction of a second. You certainly won't notice it when you
step through the code, but you will notice it when you do it 12000 times
(once for each record.)

Say it took 1/10th of a second. To repeat that for 12000 seconds would take
1200 seconds, i.e. around 20 minutes. You won't notice the 1/10th of a
second, but you will notice the 20 minutes times the number of function
calls your report is actually making.

I'm not in a postion to take on any further paid work at present.