locked
how to speed up execution time of a stored procedure ? RRS feed

  • Question

  • Hi Guys,

    Recently I came across an interview question like this. A stored procedure is taking long time to execute.

    How to reduce execution time for stored procedure? What are all the steps to be followed to reduce execution time of stored procedure ?.  

    • Moved by SSISJoostMVP Thursday, March 31, 2016 7:16 AM not SSIS specific
    Thursday, March 31, 2016 7:04 AM

Answers

  • Should I remove temp tables in stored procedure ?

    Maybe you should. Maybe you should break up a big query and introduce a temp table instead.

    The correct answer to the interview question is to first identify which statement(s) that takes most time and capture the query plan for this statement and then work from there.

    Monday, April 4, 2016 7:14 AM

All replies

  • First check the execution plan to see if suitable indexes are used. If you see a table/index scan then a suitable index is missing.

    Next check the code to see if it's a set based solution and no cursors are used.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, March 31, 2016 7:25 AM
  • Does the stored procedure accept parameters?

    Does it use cursors?

    How much data does it return?

    Show me an execution plan of the query 

    also use SET STATISTICS IO,TIME ON


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by i6first Thursday, March 31, 2016 8:22 AM
    Thursday, March 31, 2016 7:27 AM
    Answerer
  • Just to add to what Olaf and Uri has mention, check if index are there properly created to suite to the SQL statement in the SP.
    Thursday, March 31, 2016 7:45 AM
  • Should I remove temp tables in stored procedure ?
    Monday, April 4, 2016 4:24 AM
  • Should I remove temp tables in stored procedure ?

    Maybe you should. Maybe you should break up a big query and introduce a temp table instead.

    The correct answer to the interview question is to first identify which statement(s) that takes most time and capture the query plan for this statement and then work from there.

    Monday, April 4, 2016 7:14 AM
  • There are several ways by which you can speed up the execution timing of a stored procedure. 

    1. use SET NOCOUNT ON :- when you include this statement inside of your stored procedure it will reduce the Network traffic between server and the client
    2. Avoid using sp_ at the start of your stored procedure name.
    Monday, April 4, 2016 9:48 AM