Slow to render a report in Report Services 1 min query time.

Posted on 2007-10-19
Last Modified: 2010-03-19
I have a detail report that has drill down enabled as well as sub totals.  It's a daily grouping for all production our plant does.  Figure 2500 rows a day.  

This query across 8 tables consumes 1 min for a two week time span and 1.5 for a months work.  It is CASE intense but who cares about that.  It generates 13,k rows that are 15 col wide, and 12 of them are a 0 / 1 (Bit)
Actually its more like:
, case
When StorageSystem.dbo.PrintFileDetails.ripStatus & Power(2, 4) = 16 --SuccessPDFPress = 16,
or StorageSystem.dbo.PrintFileDetails.ripStatus & Power(2, 0) = 1 --SuccessLowRes  
or StorageSystem.dbo.PrintFileDetails.ripStatus & Power(2, 2) = 4 --SuccessHighRes = 4,
then 1 else 0 end NormalizeSucceed

Why would this run in the prod RS server that is beefy for 10 min and still not render in a rolled up display?

Is there any way to catch a click event and run a detail query depending on the row that they clicked on?  Kind of like a grid.

Any ideas on this?


Question by:srussell705
    LVL 18

    Accepted Solution

    Have you looked at what is causing the delay? (Query, processing or rendering?)
    Run this against the ReportServer database to see where the time is going.

       C.Path, C.Name, EL.UserName, EL.TimeStart, EL.Status, EL.[RowCount],
       EL.ByteCount, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering
     FROM ExecutionLog EL
          INNER JOIN Catalog C ON EL.ReportID = C.ItemID
     ORDER BY TimeStart DESC

    I'm guessing it's going to be in the rendering, assuming you're displaying 10 columns, it will be rendering about 200k cells, html is not a very efficient mechanism for displaying large tables. Whether they are visible or not doesn't matter, it still has to render them.

    I'd suggest only showing the sub-totals in the main report, then creating drill through reports to see the details. Should speed up the entire process considerably.
    Alternatively, try rendering straight into Excel or PDF - that should decrease the rendering time compared to mhtml. (PDF doesn't do drilldowns though)
    LVL 18

    Assisted Solution

    Tried caching the report on a scheduled base so that the report isn't actually generated live but only drawn from memory ?

    Hope this helps ...
    LVL 18

    Expert Comment

    Oh, and to actually answer your question (!)
    >> Is there any way to catch a click event and run a detail query depending on the row that they clicked on?
    No, only by using a drill through report not drilldown toggle
    LVL 1

    Author Comment

    I took out the supporting details for the report, or 8 cols or 50% of the width of the row.  

    Same return count as before.

    It now renders  in no time at all.  

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now