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

Subreport question

edited April 2004 in Subreports
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

Comments

  • edited April 2004

    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



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited April 2004
    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
  • edited April 2004

    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


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited April 2004
    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

  • edited April 2004

    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


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited April 2004
    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
  • edited April 2004

    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



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.