Subreport question

rbuser

I have a very complex report with several multi level sub reports. I am
using Delphi 7 and Report builder 7.02.My report runs very slowly so I tried
to tune up my queries as best as I could. When I was doing my performance
tuning through sql server profiler I noticed that some of the queries were
firing several different times. I am not sure why is report builder firing
the same query several differnt times. Can anyone please help me find out as
to what is causing this? Is there arny setting in Report builder that I
could use to make sure that report builder does not unnecessarily fire the
same queries several different times.

Any help in this matter will be greatly appreciated.

Thanks

nardmoseley

Please see the following tech tip which explains two types of query linking.
The second type provides much faster performance - queries are only fired a
single time.

------------------------------------------------------
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

Thanks Nard for your prompt reply,I have been playing with the second option
you suggested and that definitely fires the query only once which is what I
wanted for performance reason. But in my case the performance is degraded
because the query pulls back the entire data set. So my question to you is
if I have a master detail report and lets say for one master ID there are
three different detail rows, it would make sense if report builder fired
the query three different times with three different detail id. Now is there
a way to input the detail id on the fly so that my data set is filtered and
the query runs just 3 times in this case. Please suggest how I could achieve
that?

Thanks

nardmoseley

You are now describing option 1, Delphi Query linking. Each time the master
record position changes, the detail quire fires.

However, ReportBuilder does not simply traverse your dataset
unidirectionally (i.e. once from first to last). ReportBuilder has to
sometimes back up to complete a group break, do some keep together work,
etc. Some reports require two pass (i.e. Report.PassSetting = psTwoPass).
And when the user is previewing the report, they can of course jump to any
page.

You can minimize data traversal by setting Report.CachePages to True and by
keeping the report layout very simple.


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

rbuser

Thanks again Nard for your response. I tried setting my Report's CachePages
property to true and in my future reports I will be setting that to true but
in this particular report I need something more than that to improve the
performance of the report.It is a complex report it has a total of over 20
sub reports and some of the sub reports are sub reports within sub reports.
But unfotunatlely that is the requirement. So my question to you do you have
any suggestion to acomplish the same thing using a different approach that
would be faster? Right now the main slow down is caused by the number of
times my query is getting executed but I understand why Report builder does
that in case of subreports.

Thanks
Sujas

nardmoseley

Tough problem.

1. One option is to use the ReportBuilder DataPipeline linking rather than
the Delphi linking and open all of the DataPipelines prior to calling
Report.Print - that will prepare the data first. Probably will still require
a delay, since your queries are running slowly when they pull all records
back from the database. You could perhaps display a status message to the
user that the report is being prepared.

2. Another option would be generate this report to an archive file (.raf)
during an off peak type or in a background thread. Then allow the user to
preview/print the archive file. You could also generate to a pdf file.
ReportBuilder 7.03 contains support for generating reports to printer or
file in a background thread. For details see the online help for the
TppReport.BackgroundPrintSettings property.




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

rbuser

Thanks again. I have tried to implement option 1 but that degraded the
preformance because that requires to return the whole dataset unless you can
tell me if there is a way to filter the result set.

Option 2, that I was not aware of either but I cannot implement that
procedure either because the business requiremnt for this particular report
is to print it right away, the customers would want to see the report right
away.

Now does Digital Metaphor have some sort of limits for the number of sub
reports in a report? Because in this example I have over 20 nested
subreports and I was wondering if Report Builder can be eefctive with that
many sub reports. If not, would I be better off if I created a report in
code ? I was looking at the example on Report Builders Delphi Guide (Third
Edition) but unfortunately there are no examples for Sub reports. Can those
be done also? And if the answer is yes, will I be still able to print my
reports in different formats? Would you suggest doing that? Our goal is to
make the report run faster, I am just not sure what would be the fastest way
to do it. I have already tuned my queries using the profiler and they seem
to be running fairly well, but the main issue is the same queries are being
run several different times when used in a subreport.
Thanks
Sujas

nardmoseley

1. ReportBuilder is a power development tool and an extensible class
library. There is no limit to the number of subreports you can have.
Obviously you can encounter performance bottlenecks in any system that you
design. There are so many variables. hardware, software, network
performance, database design, report layout design, etc.

The performance of SQL queries can vary widely depending upon the database
software used, the database design, the number of records being built into a
result set, etc.

2. There is no difference between a report layout created via code or one
created via the report designer. Or create via a combination of the two. A
ReportBuilder report layout is a set of objects that define a report
template used to generate a report.

3. To run a report you will have to retrieve all the data at some point. I
think your issue with DataPipeline linking is that more of a performance hit
occurs up front. The query has to be opened on the server, however it does
not have to be retrieved all at once by client. In using a standard TDataSet
this will occur incrementally. Perhaps your database server is slow.

I suggest performing some tests without ReportBuilder. Just write some code
to link datasets and perform record traversals.

1. Link the 20 queries together using Delphi TDataSet linking. Then write
code to traverse every single record in the 20 queries. The traversal must
be done in a nested manner. That is for each record (1..n) in the highest
level master, you have to traverse the next lowest query n times. And for
each record (1..x) in that query you have to traverse the next lowest query
x times, etc. What you going to see is a whole lot of queries being fired -
which will likely be slow.

2. Link the queries together using ReportBuilder DataPipeline linking. Write
the same traversal logic. This should require a bit more overhead upfront to
open the queries but overall result will be faster performance.



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