• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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

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?


  • 2
2 Solutions
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

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)
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 ...
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
srussell705Author Commented:
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.  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now