extremely SLOW report with data in summary

rbuser

I have set up some reports for the end-user, but the one
with data in the summary band - based on calculations during
traversal of the detail band - takes about 4 minutes to
load. This is a bit understandable, since there are about
120 data fields in the summary band, and several hundred
records need to be read - then categorized to determine
which variable in the summary band to increment.

However, when the user closes the dialog box containing the
report explorer, report designer, dictionary, and the report
itself, it AGAIN takes about 4 minutes. I hope that unusual
behavior is a clue to the problem. The problem on closing
the report is more serious, since the user has no way of
knowing that they have to wait 4 minutes - and if they abort
the application during this time, the designed report
template becomes corrupt and they can not load again.

I hope someone can help. This report was to be the basis
for many user designed reports. My basic method is to use a
DBCalc for doing a count, and in its OnCalc, I check the
data date and increment the appropriate "global" (report
wide) variable (e.g. JanCount:=JanCount+1), which then is
used to set the value in the OnCalc of the the appropriate
summary variable (e.g. Variable6.Value:= JanCount).

Some speed tips I have tried:
-setting the default printer to a local printer
- checking the calc order (some summary variables do depend
on others - have a column for attempts and successes for
different years, and a calculated column for percent success).
- being sure set to One Pass
- looked at the timing dialog - anything I can change
there?? - it is "On Traversal"

Hope this makes sense

Scott
stalheim@tds.net

nardmoseley

If you think it is the calculations that are slow, then try creating a
version of the same report that contains no calculations - but still
traverse the same amount of data.

I do not know why the report takes so long to close - no traversal occurs at
that point.

If you have complex RAP event-handler code, you might try implementing it as
Delphi code contained in RAP pass-through functions. Then call the functions
from the RAP code. The Delphi code will execute faster.

You might consider trying to use a SQL query or pre-process that can create
a summary table that can be used to generate the summary portion of the
report.

You might also try running the report under a profiling tool that can
identify bottle necks.

Unless you are using dataset filtering (which is inherently slow) traversing
a dataset should be fairly quick. Perhaps try writing a routine that does
nothing more thatn traverse the dataset - then time it.


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks for the help. I found out some more by doing some of
the testing, etc. and am still puzzled.

First of all, the primary slow up appears to be calculating
for all of the summary variables, and not the data traversal
as such. With over 100 variables calculated in the summary
(9 columns of 14 variables), I can make get almost twice the
speed by eliminating half of the columns (same data traversal).

Since the data is in a very simple table already, and the
user needs to design/modify these reports - I can not
pre-calculate the results for the user ( I know that would
be fast). All I can do is warn the user to print fewer
columns or be very patient. But I would also like to
streamline the data processing as much as possible, since
this RAP code will be a template for end-users to follow.
So here are my new questions:

1) What is with the strange phenomenon of very slow closing
of the report form? Any way to change that? The report
closes quickly enough, returning to the Report Explorer.
Then the Report Explorer closes fine. The long delay is on
closing the original user form I designed - its Close event
only has in it - removing that does not help.
The closing delay is directly proportional to the
slowness loading the report!

2) Seeing that the calculations are the problem, is there
anything else I can do with them? I tried to streamline
the calculations some, so have what seems very simple and
direct now:
1)I eliminated most of the OnPrint events for variables,
like: if Variable121.Value=0 then Variable121.Visible:= False;
I would like to blank the zeroes, but anything for speed.

3) Basically I have a column for Breeding attempts, and
another for breeding successes, and a third for fertility,
which is the 2nd column divided by the first. Each column
is Jan-Dec - then Totals and then Average - so some are
calculated from the calculations.
Originally I had data in the detail band, and left the
detail band invisible, but the report appeared to labor at
printing blank detail, so I now have a DBCalc in the summary
band, and in its OnCalc, I get the BredDate and the
BredResult direct from the data pipeline, and with this
information I increment the many "global" report variables.
Thus the OnCalc event code itself is a s simple as can be:
Value:= BredFeb;
Or if based on other calculations, it is:
if Variable13.Value<>0 then
Value:= (Variable26.Value/Variable13.Value)*100;

Can't get much simpler can I?
(I did the calculation order dialog too).

4) Can the end user add a progress bar to some reports, or a
message? I suppose I can add a progress bar to the close
process of the user entry form for them - something too fast
to see on the "normal" reports?

Thanks for reading my verbose message!!

Scott S.

rbuser

Thanks, Nard-

As yoiu suggested, i pursued the problem as data
calcualtion versus data transversal, which was helpful.
Seeing it was in the calculations, I re-built everything -
very carefully for the most efficient and quickest code
possible to do the job. Now I have a report that takes 32
seconds to load, and 4 to 5 seconds to close the user dialog
box. At least tolerable.

Probably the biggest thing I found to streamline was that
the 127 variables in the summary band were dropped on the
form as their default data type, string. As variants, it
all worked, but I believe it is faster when the variables
are set to be the correct data type (string or double in my
case).
Still puzzled by those other extremely slow times closing
the form, but now that is not so bad.

Scott S.

nardmoseley

Excellent, thanks for the update. That is good information to know. Whenever
I use TppVariable I always set its DataType to the correct type so I guess I
never encountered this.

Still puzzled as to why the report closes down so slowly - I do not recall
having reports of this behavior by other developers.

--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com