• Replies 1 reply
  • Subscribers 338 subscribers
  • Views 21 views
  • Users 0 members are here
Related

Stored Procedure Running Time

Matthew Hodge
$organization

Here's a quick question which someone out there might be able to help me with. I created a stored procedure which returns some sales data, but it takes ages to run (about 0:15 seconds to return 900 rows …). I created a copy of the procedure – same name, just with _test added onto the title. Oddly enough, the new version of the procedure, which is identically scripted, does the whole query with the same parameters in 1 second – which is what I’d expect.

 I’ve done a bit of reading as to why this might occur and have tried:

1)      Recreating the original stored procedure with a WITH RECOMPILE option.

2)      Declaring an extra parameter that is set equal to the stored procedure paramater (which is apparently a good way of dealing with parameter sniffing).

3)      Running sp_updatestats.

4)      Executing sp_recompile on the stored procedure.

None of these have made any difference whatsoever. I'm sure the issue is something to do with an old execution plan from back when I was developing the procedure, but I'm just not sure how to proceed here. Is there anything else that I can try?

  • $organization

    Those are certainly things to try, but it also comes down to a number of factors.  The actual execution plan that it takes can have a big impact, depending on whether or not it uses indexes or is forced to do table scans.  Optimizing SQL is a huge topic.  By looking at the execution plan, you can narrow down which portions are taking the highest percentage of time during the execution and focus your concentration on those areas.

     

    Depending on your data, it is possible that you might just have a longer running query.  We optimize the transactional tables for speed of transaction processing and not for reporting/data analysis.  A table in particular that is notorious for slow queries (expected, this is not necessarily a defect of any sort) is T_ORDER_SEAT_HIST.  This is an audit table, and while we use it in reporting (Ticket Sales by Period, e.g.), it is not optimized for reporting. 

     

    Best of luck.  A google search or some looking through SimpleTalk or SQLServerCentral will give you some useful resources for optimizing queries.

    This would be a fun topic for a TLCC session?

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Hodge
    Sent: Thursday, September 27, 2012 1:02 AM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] Stored Procedure Running Time

     

    Here's a quick question which someone out there might be able to help me with. I created a stored procedure which returns some sales data, but it takes ages to run (about 0:15 seconds to return 900 rows …). I created a copy of the procedure – same name, just with _test added onto the title. Oddly enough, the new version of the procedure, which is identically scripted, does the whole query with the same parameters in 1 second – which is what I’d expect.

     I’ve done a bit of reading as to why this might occur and have tried:

    1)      Recreating the original stored procedure with a WITH RECOMPILE option.

    2)      Declaring an extra parameter that is set equal to the stored procedure paramater (which is apparently a good way of dealing with parameter sniffing).

    3)      Running sp_updatestats.

    4)      Executing sp_recompile on the stored procedure.

    None of these have made any difference whatsoever. I'm sure the issue is something to do with an old execution plan from back when I was developing the procedure, but I'm just not sure how to proceed here. Is there anything else that I can try?




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!