none
SSRS report running very slow but query is very fast in SSMS RRS feed

  • Question

  • I am running a very basic report. I am just retrieving some data from a table and I am using a parameter in the Where clause of the query. The query runs fast (in less than 5 secs) if I hardcode the parameter in the SSRS query but if it's left as a dynamically chosen parameter the query takes over 5 minutes to render. I have read a little about "Parameter Sniffing" but I am not sure if that applies to my case since I am only using a TSQL query and not a SP.

    Any feedback would be appreciated.

    PS: My query looks like below:

    Select Col1, Count(*)
    From Tbl1
    Where Col2 = @Para1
    Group By Col1

    KK

    Wednesday, April 3, 2013 3:00 AM

Answers

  • Hi Kk,

    Is your data retrieval takes time or report rendering takes time? Run this query in your report server database to get the above two. Select * From Executionlog2
    Check the timings data retrieval time, processing time, and report rendering time.

    If data retrieval takes time,
    Give some default values to filters (parameters).
    If parameter rendering takes time
    Choose different options for parameter selection. Instead of multiselction of parameter, use like etc.

    Let me know which causing this problem after running the SQL profiler or executionlog query so that I can help you more. Or you can use SQL profiler to check what query takes more time.

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, April 4, 2013 2:54 AM
    Moderator

All replies

  • "Parameter sniffing" applies to any parameterized query, not just stored procedures.  But before you go down that path, ensure that the data type of @Para1 exactly matches that of the underlying Col2 column.  Otherwise an implicit conversion may be needed, which can mitigate usefulness of indexes on the column.

    To address a parameter sniffing issue, you can add the OPTION RECOMPILE hint to the query:

    SELECT Col1, COUNT(*)
    FROM dbo.Tbl1
    WHERE Col2 = @Para1
    GROUP BY Col1
    OPTION (RECOMPILE);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com




    Wednesday, April 3, 2013 3:06 AM
  • Thank you for your reply Dan. 

    I tried adding OPTION (RECOMPILE) to my query and that doesn't seem to help. Its still taking 5 minutes to execute in SSRS. Do I need to rebuild the query somewhere first for this to take effect? Sorry I am very new to SSRS.

    I also checked the data types. Col2 is CHAR(2) and @Para1 is TEXT. So I suppose that shouldn't be a problem. Any other suggestions?

    Wednesday, April 3, 2013 1:00 PM
  • I also checked the data types. Col2 is CHAR(2) and @Para1 is TEXT. So I suppose that shouldn't be a problem. Any other suggestions?

    This could be the issue since I believe RS pass Text as data type nvarchar.  Since nvarchar has a higher data type precedence than char, the char value is implicitly converted to nvarchar, which results in a non-sargable expression that prevents indexes on Col1 from being used efficiently.

    I usually used stored procedures so the report parameter is converted to the stored proc parameter type before the procedure us executed.  With an ad-hoc query, you might try an explicit cast in your query like the example below.

    SELECT Col1, COUNT(*)
    FROM dbo.Tbl1
    WHERE Col2 = CAST(@Para1 AS char(2))
    GROUP BY Col1;
    

    You might not need the OPTION RECOMPILE.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Melior_IT Monday, March 12, 2018 2:33 PM
    Thursday, April 4, 2013 2:44 AM
  • Hi Kk,

    Is your data retrieval takes time or report rendering takes time? Run this query in your report server database to get the above two. Select * From Executionlog2
    Check the timings data retrieval time, processing time, and report rendering time.

    If data retrieval takes time,
    Give some default values to filters (parameters).
    If parameter rendering takes time
    Choose different options for parameter selection. Instead of multiselction of parameter, use like etc.

    Let me know which causing this problem after running the SQL profiler or executionlog query so that I can help you more. Or you can use SQL profiler to check what query takes more time.

    Hope this helps.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, April 4, 2013 2:54 AM
    Moderator
  • <object height="1" id="plugin0" style=";z-index:1000;" type="application/x-dgnria" width="1"><param name="tabId" value="{F6D4E078-F2A5-4732-BB33-50F38ED2385C}" /></object>i'm having the same issue here. I did a select from the execution log and see that the rendering time takes a while. I am guessing that is what is causing my issue....Can you explain how to pick different options for parameter selection?
    Thursday, September 19, 2013 12:44 AM
  • I've had a similar problem, but only when connecting to an Oracle database.  When I hardcode the params in the query....works great.....pass a parameter to the query and it would take forever.  The weird thing is it would only take forever for certain parameters.  In my case it was the rendering time that was taking forever when I checked the logs.  (should also mention this was a multi-select param...had no way of getting around that) 

    Strange solution:

    Instead of passing the parameter into the query, I passed it into the dataset as a filter.  One would think this would only increase the load on the ssrs server since you're sending more data back across the wire for it to deal with...thus increasing the rendering time, but the difference is night and day. The report runs great now.

    Honestly can't explain it. 

    I've written more reports than I could count against SQL Server data sources, and I've only seen this (and a few other strange things) when connecting to Oracle.  But might be worth a try if you're having the same problem, and the parameter you're applying doesn't actually limit the data all that much. (most people in my case were running 'all' anyway.

    Friday, March 7, 2014 3:17 AM
  • if you're not familiar with using filters on data sets.  Go to this link and look for the heading "Using Filters with Multiple Value Parameters"  (applies to non-multi-value params as well) http://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/
    Friday, March 7, 2014 3:22 AM
  • In your Query use:

    SET ARITHABORT ON;

    SET ANSI_NULLS ON;

    The execution plan might be different

    Saludos

    Thursday, November 5, 2015 5:20 AM
  • Please, forgive me necroing this post but it's a number one hit on Google.

    Also had this issue. I was able to improve the performance and make the SSRS report usable by using a temp table in a stored procedure.

    While running my rather complicated query from a stored procedure through SSMS I noted that it started returning rows after a few seconds and took about 30 seconds to complete returning rows. It occurred to me that perhaps this period of row returns over time might be contributing to the SSRS report slowness so I added an INTO #temptable into the main query and then did a SELECT * FROM #temptable to return the results to the report. This significantly reduced the reporting time. The report still shows an empty report after about a minute but several seconds later, pop goes the report and it displays the data. I suppose that last delay before the data shows is on the client plugin side.

    Hope this helps someone and might contribute to improving SSRS.

    CC


    --Chris, Tampa, FL, USA


    • Edited by Chrisbot Tuesday, November 17, 2015 6:30 PM
    Tuesday, November 17, 2015 5:50 PM
  • I had the same problem where the stored procedure by itself would run in 2 seconds but through SSRS it was taking 2 minutes or more! I solved it after a few hours of looking at everything involved and picking apart the RDL. In my case, I knew it wasn't "parameter sniffing" as I added code to thwart that. The ExecutionLogStorage table in the ReportServer database indicated that the total time to retrieve data, process and render took only 2 seconds (which was incorrect on their part btw and I'll explain why shortly). The record wouldn't even get written until the 2 minutes had passed.

    The problem we had was two-fold. First, the stored procedure had been altered over the years and somehow a duplicate field name had been added to the result set. This causes the "key has already been added" error in SSRS designer when you try to refresh the field list of the dataset which was preventing me from easily seeing & fixing the problem at first. The second part to the problem and what was actually causing the performance degradation was that the DataSet field list in the report had two fields listed which were no longer returned by the underlying stored procedure. Eliminating those 2 fields from the dataset list of fields was all that was needed and the report runs in 1-2 seconds now. I hope this helps some people as I know SSRS performance issues can be perplexing at times. 

    Wednesday, July 6, 2016 9:11 PM
  • rockit,

    Your solution, using a parameter as a dataset filter worked like a charm.  Thank you.  One report went from timing out to running in only 7 seconds.  Now I need to update some other slow reports.  Thanks again.

    Thursday, April 5, 2018 5:24 PM
  • You sir, are a genius! Throwing away and re-creating the DataSet solved it for me as well. In my case I was calling a stored procedure. The columns were exactly the same on both sides, except that they were in a different order. What an awful, ugly bug. If your reports are running a lot slower than the query - delete the DataSet and re-create it.
    Wednesday, May 1, 2019 10:56 PM
  • I had the same issue. I had taken care of param sniffing in my test environment, deleted/recreated, set config file on the server for memory min/max, and about everything else I could find through searches. 

    Took out the above changes so my result (below) was not skewed.

    I had one field being returned that I wasn't using, and an additional dataset based on an earlier stored proc that was not "hooked" to a tablix or anything but was still in project. I deleted the unused dataset and deleted the unused field being returned by proc.

    These reports were taking several minutes with an older procedure, and I had a programmer make a stored proc that took +-1 second in the console. But I still did not see amazing results until removing everything that was unnecessary. Now the largest report containing 34 pages (1456 rows) takes less than 5 seconds. Thanks! 

    Monday, September 9, 2019 9:57 PM
  • I know this post is a few years old, but still a relevant problem. I spent a few hours trying each of the solutions proposed here and on other threads, and although I ended up improving the performance of my query, I was still having issues with the report taking forever to render. I used the Query Designer within the dataset properties to determine the data was getting passed through to the report, but SSRS was having trouble actually rendering it. 

    After a lot of trial and error I finally figured out my problem was within the display settings I had set up for a Rectangle I was using. The dataset that was being returned was fairly large, but I had the "Keep contents together on a single page" box checked. Given the way users are interacting with the report that setting wasn't necessary, so I removed the check mark and went from watching it spin for 10+ minutes before giving up, to returning a report within 10 seconds.

    Just wanted to share in case anyone else has a similar problem.

     

    Monday, September 16, 2019 6:50 PM
  • I have been fighting with this issue.  I ended up deleting groups/tables from the report and rerunning until it was fast.  Then I'd undo checkout on the RDL to revert to my last version from source control.  I repeated this with deleting fewer items until I identified which table/tablix was causing the slowness.

    I reverted back to previous version again. 

    On that table I modified the groups to remove sorting on the group details.  It was then fast.   I added the group sorting back.  It was still fast.  (I don't know what this horrible forum has done to my text size)

    Here's what gets me. I then did a compare against source control, because I'm like "what actually changed in removing/adding the group sorting" and the RDL locally was identical.  Deleting and readding the group sort fixed the issue, but the RDL was still the same.

    I also made sure to frequently delete the *.data file that caches the data to make sure it wasn't causing misleading results.


    Thursday, January 30, 2020 3:10 PM