Home Server
New Blog Posts: Merging Reports - Part 1 and Part 2

RB 7 Server Performance

edited June 2005 in Server
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

Comments

  • edited June 2005

    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



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited June 2005
    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!


This discussion has been closed.