Post reply

SSRS Performance on large table

  • February 20, 2015 at 6:02 am

    #295436

    Hi,

    we have created ssrs report to show data from the very large table. Every hour the bulk insert job imports lakshs of records.

    The table contains 15 columns that has one date column and rest of them are varchar columns.

    And the query is simple, it does not join any other table but it has 6 to 8 where "in" conditions. we created required index as advised by the performance tuning advisory.

    while checking the query in ssms execute it takes 4-5 mins for first execution and second time it takes only 10 seconds, but the reports always takes more than 10 mins for every time.

    Please help how to solve the issue.

    Thanks

    Mani

  • February 20, 2015 at 7:04 am

    #1778939

    How much data does the query return? (be aware, most people do not know what lakh is)

    The fact that the query takes 4-5 minutes the first time and 10 seconds for the second time is probably due to caching and the re-use of the query plan.

    Your report might be slower because it has to render the report as well.

    You can check this in the execution logs.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 20, 2015 at 7:14 am

    #1778941

    Thanks for the replay,

    I have checked the report execution log, Seams TimeDataRetrievalis greater 🙁

    TimeDataRetrieval | TimeProcessing | TimeRendering | ByteCount | RowCount

    263105 | 57 | 79 | 56481 | 19

  • February 20, 2015 at 7:24 am

    #1778943

    smanikandan23 (2/20/2015)

    Thanks for the replay,

    I have checked the report execution log, Seams TimeDataRetrievalis greater 🙁

    TimeDataRetrieval | TimeProcessing | TimeRendering | ByteCount | RowCount

    263105 | 57 | 79 | 56481 | 19

    Over 4 minutes for data retrieval, seems consistent with your query being run for the first time.

    But 4 minutes for 19 rows, that seems like a real long time.

    Maybe you should post your table DDL (including indexes), the query and the actual execution plan.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 9, 2015 at 4:09 pm

    #1782725

    Is the arithabort setting on the db disabled? ssms might enable it and give youu data fast.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • March 9, 2015 at 4:46 pm

    #1782731

    One option is to setup a subscription for the report and see how long that takes to run. This will reduce some latency/rendering issues.

    Also can you check the execution plan used by the Report vs what you get in SSMS. Sometimes the query plan may differ and get different performance results.

    Regards,

    Nachi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply