performance degration when printing large report

rbuser

i posted this a while ago but now we are at a critical point. we have a
set of data (50-100k records) that each print 1 page of data - dollar
amounts, and some text, 2 memo fields that format name/address
information. there is a master/detail relationship linked on a single
key using the provided datapipeline against tdatasource to tiboqueries.
the main report has a single group item and a subreport for the detail band.
i have set cachepages to false; the OutlineSettings.enabled to false and
visible to false as well as making sure the outline event is set to nil
in code at runtime.
every 1000 pages seems to take an additional 30,000 milliseconds to
print. the first 1000 pages take about 1.3 minutes but by the time it
prints the 6000-7000 pages its taking 4.4 minutes. after 30 minutes
i've only spooled 9000 pages.

i'm really not sure what else to try. i tried replacing the report
with one that has a single database field per page, and there still is a
significant degration over number of pages printed.

processing the same data to a text file (not using reportBuilder but
file i/o) only takes a couple hours to process the same 100k records.

do you have any ideas/suggestions?

thanks!
-martha

nardmoseley

Try setting Report.OutlineSettings.Enabled to False

I researched this once before using a profiling tool and discovered that for
large reports, the overhead of producing the outline degrades performance.


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

rbuser

i have set cachepages to false; the OutlineSettings.enabled to false and
visible to false as well as making sure the outline event is set to nil
in code at runtime.

i just substituted a report with a single label per page and no groups
and am experiencing the same degradation.

any other suggestions?

thanks!
-martha

rbuser

In article <43023b01$1@dm500.>, ~martha@balanceconsult.com says...


What OS is this running on? Is this a network printer? Is your
workstation "thrashing"? I'm wondering if maybe you are running into a
RAM or spooling limitation...

Cheers,
EdB

rbuser

running on xp sp2. at the moment i'm in a test environment running from
a desktop. i'm printing to a file (having defined a device via printer
setup - print to file port) since i really do not want to print these
pages real time until production - and we can prove the software will
not take days to generate these statements. which hasn't been the case
todate. i've also tried printing to a pdf output with the same
degration. using delphi 7 with report builder 9.02.

i've replaced the current report template with one that simply contains
one label per page - no data controls; results are the same.

is there any thing else we can do to identify the problem and resolve it?

thanks!
-martha

nardmoseley

There are many things that can affect performanc: Hardware, Software,
Database, DataAccess, Network, Drivers, Memory, on and on.

1. Do you have a master/detail relationsip? If so are you using the RB Data
workspace to build queries or are you using external datasets? Are you
linking the datasets or linking the datapipelines? See the data access
thread of the Tech Tips newsgroups for an article on linking queries.

2. As a benchmark, write some code that opens the dataset(s) and a loop that
traverses the dataset(s) from beginning to end. Time each of those two
steps.

3. In another post you indicate that you created a simple report containing
only a Label - did that provide a speed improvement? If not then the problem
is less likely anything related to RB.

4. How many records are in your result set(s) and how many pages are you
generating?

5. Try installing a local printer driver and use that to generate the report
to archive?

What is database product are you using? What data access component set are
you using? Provide more info about Delphi version, RB version, OS, etc. Is
that database running a separate machine across a network?


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

rbuser

more investigation - if i move the subreport structure to the main
report detail (eliminating the subreport) - and remove all detail
pipeline references and detail data fields - leaving just the master
data fields - and run this - everything works fine - no degradation and
the report is pretty darn fast. so the degradation is a result of the
master/detail relationship traversal.

it's really not feasible for us to generate this relationship into a
single query as we can potentially have master to multiple details (or
master query and several detail queries from different database tables).

is there anything that can be done to optimize the master/detail
relationship?

thanks!
-martha

rbuser

see embedded responses

nardmoseley

If you suspect the datapipeline linkage, then as a benchmark, write some
code that opens the datapipelines and a nested loop that traverses the
datapipelines(s) from beginning to end. Time each of those two steps.

If you are linking the datapipeilnes, make sure that the detail query is
sorted on the linking fields. And make sure that you are not linking the
datasets.

An alternative approach you can try is to link the queries rather than the
datapipelines. For most cases this is slower, but there are always
exceptions, so its worth a try.

Both types of linking are described here. You could try each approach and
testing using the same benchmark code I described above.


------------------------------------------------------
Tech Tip: Linking SQL Queries for Master/Detail Data
------------------------------------------------------

The following example shows two options for linking SQL queries to create a
master/detail relationship.

In this example, we are using Delphi's DBDemos data to create a
Customer/Order relationship. Thus we wish to link the Orders detail to the
Customer master.

I. Delphi Query Linking
------------------------

a. Set the detail TQuery.DataSource property to point to the master
query's TDataSource component.

b. In the SQL "Where" clause for the detail query use a ':' followed by
the linking field name from the master:

example
select *
from orders
where orders.CustNo = :CustNo

Now each time the master record position changes, the detail query will
automatically be refreshed with the correct result set.


II. RB DataPipeline Linking
-----------------------------

a. Set the detail DataPipeline.MasterDataPipeline to point to the master
DataPipeline.

b. Use the detail DataPipeline.MasterFieldLinks property to define the
linking relationship

c. In the SQL for the detail, retrieve all records and sort them by the
linking master field:

select *
from Orders
order by CustNo


Notes:

1. Using RB DataPipeline, each query is executed only a single time - thus
performance is much faster.

2. RB Professional and Enterprise Editions include a visual Data environment
for creating SQL queries, defining linking relationships, and creating
Ask-At-Runtime parameters. Using the RB tools you could create the above
linked queries in about 10 seconds.


--
Tech Support mailto:support@digital-metaphors.com
Digital Metaphors http://www.digital-metaphors.com




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

rbuser

In article <430342fb$1@dm500.>, ~martha@balanceconsult.com says...

Can you "de-normalize" the data? That is, create an in-memory table that
includes all required fields and populate this at run-time prior to
running report? This way you could avoid the subreports and m-d
relationships.

That is, if you have something like:
master table
mFld1
mFld2
mFld3
DetailTable A
dAFld1
dAFld2
dAFld3
Detail Table B
dBFld1
dBFld2
dBFld3

You'd create a cds with fields:
mFld1
mFld2
mFld3
dAFld1
dAFld2
dAFld3
dBFld1
dBFld2
dBFld3
srcTbl // "dA" or "dB"
Then do something like:

cds.EmptyTable;
cds.LogChanges:=false; // better performance
masterTable.first;
while not masterTable.eof do begin
// see if any recs for detail table A
detailTableA.setRange([mastertable.],
[mastertable.]);
if not detailTableA.IsEmpty then begin
cds.insert;
// set master info
for k:=0 to masterTable.fieldCount-1 do
cds.fieldByName(masterTable.fields[k]).value:=
masterTable.fields[k].value;
// set DetailA info
for k:=0 to detailTableA.fieldCount-1 do
cds.fieldByName(detailTableA.fields[k]).value:=
detailTableA.fields[k].value;
cdssrcTbl.AsString:='dA';
cds.post;
end;
// see if any recs for detail table B
// repeat logic from above using detailTableB
masterTable.next;
end;

Assuming you need different layout (or field names) for detailTableA and
detailTableB in your report, create regions in detail band - one for each
detailTable and control visible property of regions by examining value of
srcTbl: ppRegionA.visible:=(ppDBPipeline1['srcTbl']='dA')

The TClientDataSet wasn't really designed to handle 100K records, but
I've used the above logic to consolidate stupid amounts of data (50k+
records) from Sybase ASE and MSSQL tables (at the same time) into a
single big CDS for reporting purposes...

Cheers,
EdB

rbuser

thanks i'll see what i can do about denormalizing the data. i know in
most cases i'll probably be able to do that, but there is always the one
case that doesn't fit. but it's a start. thanks!
-martha