locked
SSRS Cache RRS feed

  • Question

  • HI All,

    I have created report based on SQL Stored Procedure...

    Now My store procedure took 10 seconds to excute but when i use the same in SSRS Report, it tool 30 seconds to complete..

    Is it possible to cache the result set of SP in SSRS Cache so that when ever user ran the report, it will refer to the cache data and hence took less time?

    If not please suggest me other way around to crack the same.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Tuesday, June 5, 2012 8:54 AM

Answers

  • Hi Syed,

    What you want to say is it it not possible to cache whole data at once in case of paramter, it should be cached on the basis of parameter value provided in report, and it must took required time to execute at fist go for a specific parameter value.

    What i required is not possible in SSRS Caching, is that you want to say..?


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Thursday, June 7, 2012 10:47 AM

All replies

  • Amit

    I would start with optimizing the stored procedure (properly defined indexes and etc)... How much data does it return?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, June 5, 2012 9:10 AM
  • Hi URI,

    It would return data near about 2 lacs rows and took just 10 sec...but report took sometime a min to do so , hence what i concluded , there is something wrong at SSRS part.

    My main concern is to use the Property of SSRS Cache to reduce the processing time of SSRS Report


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Tuesday, June 5, 2012 11:01 AM
  •  

    Hi Amit

    Please have a look on this article that might help you

    http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

    http://www.mssqltips.com/sqlservertip/1919/how-to-enable-caching-in-sql-server-reporting-services-ssrs/

    one thing more I can suggest you please go to your report server database and run the query against your log table

    SELECT [LogEntryId]
          ,[InstanceName]
          ,[ReportID]
          ,[UserName]
          ,[ExecutionId]
          ,[RequestType]
          ,[Format]
          ,[Parameters]
          ,[ReportAction]
          ,[TimeStart]
          ,[TimeEnd]
          ,[TimeDataRetrieval]
          ,[TimeProcessing]
          ,[TimeRendering]
          ,[Source]
          ,[Status]
          ,[ByteCount]
          ,[RowCount]
          ,[AdditionalInfo]
      FROM [dbo].[ExecutionLogStorage]

    you can see there are  three different time [TimeDataRetrieval]     ,[TimeProcessing]      ,[TimeRendering] which might help you for more investigation point of view

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.



    Tuesday, June 5, 2012 11:05 AM
  • Amit,

    You should always target the SQL trace. Verify the possible bottlenecks. Come up with your reviews.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, June 5, 2012 11:07 AM
  • HI Syed,

    Thanks for reply..

    I have gone through  [TimeDataRetrieval]     ,[TimeProcessing]      ,[TimeRendering] columns of table ExecutionLogStorage and find values as below

     [TimeDataRetrieval] :13342 

    ,[TimeProcessing] :46050 

    ,[TimeRendering]: 305

    First let me know the unit of this value, i.e whether it is in SECONDS or in MiliSeconds.

    All these columns show very high value while my sp took just 10 sec to completes, please let me know how to reduce above [TimeDataRetrieval],[TimeProcessing],[TimeRendering] for report.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Wednesday, June 6, 2012 7:53 AM
  • Hi Amit

    Please have a look on the following that might help you

    TimeDataRetrieval

    Number of milliseconds spent retrieving the data

    TimeProcessing

    Number of milliseconds spent processing the report.

    TimeRendering

    Number of milliseconds spent rendering the report.

    ByteCount

    Size of rendered reports in bytes.

    RowCount

    Number of rows returned from queries.

    From your example you can see the byte count and row count for your report and try to calculate the average KB of retrieval, Processing, of per second

    Please take a look on the following thread as well

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/5b9c5a65-7797-4de8-8ef1-c50ecf4bcb11/

    I think the retrieval speed might be limited by the hardware. The processing speed is due to the complexity of the report. If the report is very complex, it need more time.

    Many Thanks

    Syed Qazafi Anjum                                             

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.


    Wednesday, June 6, 2012 8:59 PM
  • Hi Syed,

    I am able to reduce the processing time of report execution by implementing Cache property to the report those having no parameters.

    I have implemented the same logic to the reports having parameter(User suppose to provide inputs) , things are good but from second attempt onwards for one set of parameter

    For E.g.  If user select say date, 07/06/2012, it will take 20 sec to execute..and if again user select 07/06/2012, it will take a less than sec to complete...things are good till here..

    But when User selects another date say 08/06/2012, again it took 20-22 sec to complete, again if 08/06/2012 is selected , will took less than sec.

    I can understand, for each unique run, ssrs cache the data,

    Is there is any way that will take time at first run for selected paramter but then after it will not take time of any selected parameter.?

    For E,g, If user Selects 07/06/2012 first time, will took 20 sec to complete, but if after that user selects 07/06/2012 or 08/06/2012 or any date, will took less than a sec to complete.

    Please suggests

    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/


    Thursday, June 7, 2012 8:07 AM
  •  

    Hi There

    By enabling caching SSRS improves the performance of report processing for the report so that if the same report request comes again or for all subsequent requests the stored copy can be rendered in the desired format and served instead of processing it from scratch as long as cache is available as you are changing the parameter values first request will take some time to rendered

    If you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, June 7, 2012 10:06 AM
  • Hi Syed,

    What you want to say is it it not possible to cache whole data at once in case of paramter, it should be cached on the basis of parameter value provided in report, and it must took required time to execute at fist go for a specific parameter value.

    What i required is not possible in SSRS Caching, is that you want to say..?


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Thursday, June 7, 2012 10:47 AM
  • Hi Amit

    Thanks for your posting please have a look on this thread this might answer your question

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/7a63deb0-8969-48ff-afb5-5dc6fb1e0602

    Many thanks

    Syed Qazafi Anjum

    Thursday, June 7, 2012 11:25 AM
  • Hi Syed,

    Thanks for your help and patience:)

    Hope microsoft will add this feature as well in future.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Thursday, June 7, 2012 11:58 AM