Interviews - C# Corner

Chandan Prasad
How do you optimize a long running stored procedure in SQL Server?
By Chandan Prasad in SQL Server onAug 13 2018
  • mukul agrawal
    Sep, 2018 29

    To optimize the long running SP, First we need to analyze the joins(ex : inner,outer apply) in query one by one removing condition from SP and notice the time and records it takes to fetch the records. you can apply the index on the table columns which takes more time an above first condition. can be removed unwanted where condition in query, can be call some condition in if-else condition. Hope this help you.

    • 1
  • Chandan Prasad
    Aug, 2018 13

    i tried to answer but answered few points only as listed. please add to it i have missed any point.1. Use the transaction as short as possible and clear. 2. Avoid creating SP name as sp_procedurename as, it first searches this name in master database and the user database. this i can lead to a slight performance degradation. 3. Use of fully qualified sp name 4. Use set NOCOUNT ON

    • 1
  • Nilesh Patel
    Oct, 2018 22

    First bifurcation on store procedure in small unit and check performance which is taking more time1) Remove unwanted Cursor 2) Put All data in Temptable and update require date in Temp table using update Query instead of Looping 3) Use Exists Not Exists instead of In and Sub Query 4) Also you Cross Join if required

    • 0

Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS