Below are some of the tips to improve the performance of stored procedures of queries
-Use TRUNCATE instead of DELETE where ever possible.
-REMOVE 'SELECT TOP 100 PERCENT FROM TABLE' and use 'SELECT * FROM TABLE' or 'SELECT column_names FROM TABLE'
-Design out the OUTER JOINs. Filter the data as much as possible so that the procedure runs on minimum set of data. Use temp tables or table variables to do this.
-If you are using 'NOT IN' then avoid using that. It causes severe overhead to the performance of the query.
-Have a primary key to the table and create non clustered Indexes where ever necessary, this increases the performace surprisingly.
-Avoid using functions in the query, since each row of the data has to be processed through the function, this causes overhead to the performance.
You can read the below articles for detailed information: