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.

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

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