locked
Report runs slow,but query runs faster in the Mgmt studio ? RRS feed

  • Question

  • I have one complex report in the SSRS 2012,where i am using one USP ,its running in the Sql cosnole very fine executing in 10/15sec,

    but when i call this USp at the report data set level ,its not exciting the report,below is the error message,Please find that below

    In my USP they are some Nulls are they in the select list,might be it causes the issues,please let us know 

    An error occurred during local report processing,

    The server did not provide the meaningful reply,this might caused by contrast match,a premature session is shutdown or an internal server error

    Thanks you!

    Thursday, September 22, 2016 5:49 AM

Answers

All replies

  • Hi Ychinari,

    Is your data retrieval takes time or report rendering takes time? Run this query in your report server database to get the above two.

    Use ReportServer  
    select * from ExecutionLog3 order by TimeStart DESC  

    go

    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 execution log query so that I can help you more. Or you can use SQL profiler to check what query takes more time.

    Refer in following link

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b64454c-96ee-4d3a-95ca-6ef3bd41008e/ssrs-report-running-very-slow-but-query-is-very-fast-in-ssms?forum=sqlreportingservices


    Please click Mark As Answer if my post helped.

    Thursday, September 22, 2016 6:39 AM
  • Hi Ychinnari,

    According to your description, you were using a stored procedure as your report's DataSet. And this SP runs fast in SQL Server but runs slow in Reporting Service DataSet side. Right?

    Besides the method that Dinesh Vishe suggested to troubleshoot the performance of report. The issue can be also related to the parameter sniffing in stored procedure. Is your stored procedure containing multiple parameter? If so you can try 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.

    Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/be9c1c98-6e0c-4d42-ba1a-e4a90d36fe6b/performance-in-ssrs 

    http://www.sommarskog.se/query-plan-mysteries.html 

    http://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs 

    If you still have any questions, please feel free to ask.

    Thanks,

    Xi Jin.

    • Proposed as answer by Xi Jin Thursday, October 6, 2016 9:20 AM
    • Marked as answer by Xi Jin Monday, October 10, 2016 1:25 AM
    Thursday, September 22, 2016 7:38 AM