Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • 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?

TIA

0
srussell705
Asked:
srussell705
  • 2
2 Solutions
 
PFrogCommented:
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.

SELECT
   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)
0
 
YveauCommented:
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 ...
0
 
PFrogCommented:
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
0
 
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.  
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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