Post reply

Stored procedure return but report takes long time

  • September 30, 2011 at 11:18 am

    #245824

    Hi,

    I have a fairly simple report that runs from stored procedure. When I execute stored proc with certain parameters it takes 19 seconds to return results. When I execute the report with same parameters it takes over 1 minute and sometimes times out.

    Any ideas? I set do not time out for the report but it still takes forever.

    Thanks!

  • September 30, 2011 at 1:52 pm

    #1389014

    Juls-195136 (9/30/2011)

    Hi,

    I have a fairly simple report that runs from stored procedure. When I execute stored proc with certain parameters it takes 19 seconds to return results. When I execute the report with same parameters it takes over 1 minute and sometimes times out.

    Any ideas? I set do not time out for the report but it still takes forever.

    Thanks!

    It could potentially be many things, depending on exactly what you are doing in the report...i.e are you doing a lot of filtering, grouping or formatting etc.

    I would also suggest you look at parameter sniffing. It could very well be that a bad execution plan is used when executing the stored procedure from the report.

    Hope this helps.

  • September 30, 2011 at 2:00 pm

    #1389017

    I would agree that I would start with the sproc. Unless you are talking about millions of rows in the base tables 19 seconds sounds way too long. If you want some help making it scream post up some ddl, desired results, some sample data and of course the code and we can make it blazing fast. We can go back and look at the report if it is still slow.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 3 posts - 1 through 2 (of 2 total)

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