Databases
StackOverflow2013
Postsdbo
POSSRS - copying report to a new folder increases speed 10x
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSRS - copying report to a new folder increases speed 10x
    text
    copied!<p>I've got a SQL 2008R2 report that runs 12,000 times a month. It averages 60-90 seconds per execution. </p> <p>I've been using SQL for 12 years, but I just started this job 2-3 weeks ago, and am still trying to get my head around some of these SSRS performance problems. It goes without saying I've been re-indexing everything in order to help this report.</p> <p>Here is a picture / dump of my execution log:</p> <pre><code>SELECT ReportPath, TimeDataRetrieval, TimeProcessing, TimeRendering, Source, [RowCount] FROM ReportServer.dbo.ExecutionLog2 WHERE UserName = '_________' AND ReportAction = 'Render' ORDER BY timeStart desc </code></pre> <p><a href="http://accessadp.com/?attachment_id=562" rel="nofollow">http://accessadp.com/?attachment_id=562</a></p> <pre><code>ReportPath TimeDataRetrieval TimeProcessing TimeRendering Source RowCount /CubeReports/Freight Allocation 2954 4402 2039 Live 2348 /RS Reports/Freight Allocation 39954 4087 2380 Live 2348 /RS Reports/Freight Allocation 37718 3948 1888 Live 2348 /RS Reports/Freight Allocation 39534 4317 1937 Live 2348 /CubeReports/Freight Allocation 3257 4206 2422 Live 2348 /RS Reports/Freight Allocation 37517 4164 2402 Live 2348 /RS Reports/Freight Allocation 36127 4151 1986 Live 2348 /RS Reports/Freight Allocation 36415 39888 2569 Live 19048 /RS Reports/Freight Allocation 37544 41644 2071 Live 19048 /RS Reports/Freight Allocation 37970 41003 2187 Live 19048 /RS Reports/Freight Allocation 38057 48085 1885 Live 19048 /CubeReports/Freight Allocation 3030 4558 2056 Live 2348 /CubeReports/Freight Allocation 3534 5232 2422 Live 2348 </code></pre> <p>Please note, I do believe I know what the difference in 'RowCount' is. I had a subreport that was running a dataset (that wasn't important) and I removed it.</p> <p>I thought that this was the reason for the increase in performance.. but I've double-checked and triple-checked that the subReports no longer have the other dataset (and this is refereced in the decrease in rowcount). Unfortunately, that didn't translate into a decrease in processing time.</p> <p>I downloaded the report from 'RS Reports', and deployed it to 'CubeReports'.. and I didn't change anything else on this version of the report.</p> <p>I run it with the same parameters.. and now the copy of the report 'CubeReports' literally runs 10x faster.</p> <p>I just can't figure out WHY this is happening? I REALLY need to find the solution and move it into production.</p> <p>I've checked snapshots, history, execution caching.. none of that is turned on, it all looks like the default setting for both reports.. I've checked all the other options, and I just can't find anything that would explain this.</p> <p>The only three options I see:</p> <ol> <li>Report Builder 3.0 isn't 'compiling the report' as well as BIDs does.</li> <li>Having 3-4 people running the primary report at the same time I'm doing the test is causing this problem. (We have 300 employees, I really can't test anywhere else, because people run this all day every day). </li> <li>Dropping the report and re-deploying the report, and crossing my fingers that this is going to make it run 10x faster</li> </ol> <p>Unfortunately, I've been able to duplicate the 10x speed increase consistently, I've ran it about 10 times each with the same parameters with the same result. Keep in mind, there is only 1 SSRS server, going against 1 database server. Same sprocs, same parameters.</p> <p>10x worse performance in the production copy of this report. 10x better performance when I copy it to a new folder.</p> <p>Primary ERP database is ~100gb, only 4 cores, only 16gb RAM. SSRS Server is on a VM, it is only 2 cores, only 8gb RAM.</p> <p>There is one additional database that lives on the SSRS Server; it's actually a fairly large database- but not a TON of activity. The other database (Bartender) is only 9gb data / 3gb log.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload