RB 7 Server Performance

rbuser

RB 7.02 server via trsClientReport, Oracle 8 - one pass report, no cached
pages, outline turned off on a simple one to many master / detail report.
The RB report has 2 queries linked via keyfields with a master report and
one subreport for the detail. No calculated fields, no shapes, no pictures
etc - just DBText fields. There are 3 autoseach fields filled
programmatically. Total rows Master ~ 500, Detail ~ 6000


Query returns all rows in ~ 20 seconds via PL/SQL, Report takes ~ 130
seconds to produce the first page to the screen.

Are there any additional ways to speed up the report? Does this sound
right? I expect overhead to produce the pages but not this much. Seems to
get worse as the number of pages increases. If I limit the query to a
single master result row it runs fast. 1 second PL/SQL, 10 seconds RB
Server so there isn't much overhead filling the autosearch fields and
initial report setup.

In my setup I would expect ~ 10 seconds report setup plus the time to
execute the query (20 seconds) then some processing ofr the first page
(generous 10 seconds) -> shouldn't I see output in about 45 seconds? For
sure less than a minute instead of over two minutes. In fact, if the
subreport query is execute each time for a master row I should see output
even faster?

I'd be delighted to upgrade to RB9 if it would help performance...

TIA - Jon Gray

nardmoseley

The architecture of ReportBuilder Server is highly optimized. Our testing
here and feedback from customers has provded this out. Our live demo server
is a good demonstration of this.

I supect there is something else going on.

1. I recommend performing some incremenatl tests.

a. Run the report in a standard application (i.e. not the report server).
That will provide a base line of performance.

b. Run a local server (ClientReport and Server are running on the same
machine).

c. Run a remove server


2. Check the Windows\Temp directory and clean it up.

We had a customer that had a fast server, suddenly start to slow way down.
Turns out it was an issue with the temp directoy being too full.

3. Data linking

Linking TDataSets is much slower than linking DataPipelines. (see article
below).

4. Two Pass reports. A two pass reports running in a server environment is
going to be slower, because traverse all of the data prior to sending page 1
to the client.


------------------------------------------------------
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 for your suggestions, it helped me track down the problem. My
performance times are now what I would expect in my setup.

I was using "In List" AutoSearch fields on the linked detail query in the
dataview. While this worked, it generated inefficient SQL utilizing the
"LIKE" operator each time it hit the detail table. Removing the unnecessary
autosearch fields cut the report times in half.

Thanks again for all your help!