Fast query runs slow in SSRS

Question

I have an SSRS report that calls out to a stored procedure   If I run the stored procedure directly from a query window  it will return in under 2 seconds   However  the same query run from an 2005 SSRS report takes up to 5 minutes to complete   This is not just happening on the first run  it happens every time   Additionally  I don t see this same problem in other environments   Any ideas on why the SSRS report would run so slow in this particular environment

User · Answer

I was able to solve this by removing the [&TotalPages] builtin field from the bottom. The time when down from minutes to less than a second.

Something odd that I could not determined was having impact on the calculation of total pages.

I was using SSRS 2012.

User · Answer

Aside from the parameter-sniffing issue  I ve found that SSRS is generally slower at client side processing than  in my case  Crystal reports   The SSRS engine just doesn t seem as capable when it has a lot of rows to locally filter or aggregate   Granted  these are result set design problems which can frequently be addressed  though not always if the details are required for drilldown  but the more um   mature   reporting engine is more forgiving

User · Answer

Had the same problem  and fixed it by giving the shared dataset a default parameter and updating that dataset in the reporting server

User · Answer

I had the same scenario occuring  Very basic report  the SP  which only takes in 1 param  was taking 5 seconds to bring back 10K records  yet the report would take 6 minutes to run   According to profiler and the RS ExecutionLogStorage table  the report was spending all it s time on the query   Brian S  s comment led me to the solution  I simply added WITH RECOMPILE before the AS statement in the SP  and now the report time pretty much matches the SP execution time

User · Answer

I will add that I had the same problem with a non-stored procedure query - just a plain select statement   To fix it  I declared a variable within the dataset SQL statement and set it equal to the SSRS parameter   What an annoying workaround   Still  thank you all for getting me close to the answer

User · Answer

DO you use  group by  in the SSRS table   I had a report with 3 grouped by fields and I noticed that the report runed very slowly despite having a light query  to the point where I can t even dial values in the search field   Than I removed the groupings and now the report goes up in seconds and everything works in an instant

User · Answer

Add this to the end of your proc  option recompile   This will make the report run almost as fast as the stored procedure

User · Answer

I had the report html output trouble on report retrieving 32000 lines  The query ran fast but the output into web browser was very slow  In my case I had to activate    Interactive Paging    to allow user to see first page and be able to generate Excel file  The pros of this solution is that first page appears fast and user can generate export to Excel or PDF  the cons is that user can scroll only current page  If user wants to see more content he she must use navigation buttons above the grid  In my case user accepted this behavior because the export to Excel was more important   To activate    Interactive Paging       you must click on the free area in the report pane and change property    InteractiveSize        Height    on the report level in Properties pane   Set this property to different from 0  I set to 8 5 inches in my case  Also ensure that you unchecked    Keep together on one page if possible    property on the Tablix level  right click on the Tablix  then    Tablix Properties     then    General        Page Break Options

User · Answer

I simply deselected  Repeat header columns on each page  within the Tablix Properties

User · Answer

Couple of things you can do  without executing the actual report just run the sproc from within the data tab of reporting services   Does it still take time  Another option is to use SQL Profiler and determine what is coming in and out of the database system     Another thing you can do to test it  so to recreate a simple report without any parameters   Run the report and see if it makes a difference   It could be that your RS report is corrupted or badly formed that may cause the rendering to be really slow

User · Answer

If your stored procedure uses linked servers or openquery  they may run quickly by themselves but take a long time to render in SSRS  Some general suggestions    Retrieve the data directly from the server where the data is stored by using a different data source instead of using the linked server to retrieve the data  Load the data from the remote server to a local table prior to executing the report  keeping the report query simple  Use a table variable to first retrieve the data from the remote server and then join with your local tables instead of directly returning a join with a linked server    I see that the question has been answered  I m just adding this in case someone has this same issue

User · Answer

In our case  no code was required     Note from our Help Desk   Clearing out your Internet Setting will fix this problem    Maybe that means  clear cache

User · Answer

I Faced the same issue  For me it was just to unckeck the option       Tablix Properties   Page Break Option    Keep together on one page if possible   Of SSRS Report   It was trying to put all records on the same page instead of creating many pages

User · Answer

In my case  I just had to disconnect and connect the SSMS  I profiled the query and the duration of execution was showing 1 minute even though the query itself runs under 2 seconds  Restarted the connection and ran again  this time the duration showed the correct execution time

User · Answer

Thanks for the suggestions provided here   We have found a solution and it did turn out to be related to the parameters   SQL Server was producing a convoluted execution plan when executed from the SSRS report due to  parameter sniffing    The workaround was to declare variables inside of the stored procedure and assign the incoming parameters to the variables   Then the query used the variables rather than the parameters   This caused the query to perform consistently whether called from SQL Server Manager or through the SSRS report

User · Answer

I came across a similar issue of my stored procedure executing quickly from Management Studio but executing very slow from SSRS  After a long struggle I solved this issue by deleting the stored procedure physically and recreating it  I am not sure of the logic behind it  but I assume it is because of the change in table structure used in the stored procedure

User · Answer

I had the same problem  here is my description of the problem   I created a store procedure which would generate 2200 Rows and would get executed in almost 2 seconds however after calling the store procedure from SSRS 2008 and run the report it actually never ran and ultimately I have to kill the BIDS  Business Intelligence development Studio  from task manager    What I Tried  I tried running the SP from reportuser Login but SP was running normal for that user as well  I checked Profiler but nothing worked out   Solution   Actually the problem is that even though SP is generating the result but SSRS engine is taking time to read these many rows and render it back   So I added WITH RECOMPILE option in SP and ran the report    this is when miracle happened and my problem got resolve

[performance] Fast query runs slow in SSRS

Examples related to performance

Examples related to reporting-services

Examples related to report

Examples related to reportingservices-2005