You can optimize your SP as:
O - First make sure that the statistics on all tables in your query plan are up to date if not then first update the statistics of all the tables(Using update statistics command) which are used in SP. And look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the plan and start optimizng from here.
O - If you see the table scan in that part that means the table used in that part have not only indexes but also it dont have clustered index, so first create the clustered index on that table and run again the SP and check the SP is working fine or not according to time.
O - If you see the clustered index scan in that part that means the table used in that part have the indexes but the indexes are not using by the execution plan to search the data. In that case you need to replace the indexes position that means indexes are not created on the right columns. So when you create the indexes on right column then the index seek is performed to search the data in table.
So finally you need to create the indexes in such a way that the query execution plan always perform the Index seek to search the data in table.
Things to be remembered while writting a SQL code :
1 - Always use set nocount on.
2 - Avoid the use of Distinct keyword and use Group By.
3 - Avoide the use of Order By clause.
4 - Avoid the use of IN keyword use Exists or inner join.
5 - Avoid the use of NOT IN keyword use NOT Exists.
6 - Dont use the cursor use while loop with the help of identity column.
7 - Select only required field in query not use select *.
8 - In case of dynamic query always use sp_executesql instead of execute.
9 - Avoid use of temp table use table variable or CTE.
10 - Do not use the SP_ prefix with stored procedure name.
11 - Avoid any operation on the fields where possible. Some operations will prevent the use of index on this field even if it exist.
EX: where cast(dtfield as varchar(20)) = @DTval
Recently I encountered a case when I knew much more about the data than the optimizer. Originally the performance was horrible, this is why...
Introduction Web Service is known as the software program. These services use the XML to exchange the information with the other software wi...
Introduction Advanced Integration is for sellers who want more flexibility when creating payment buttons. Advanced Integration facilitates t...
Most experienced developers will tell you that end users tend to "judge an application by its cover". In other words, they don'...
Dependency Injection (DI) is a software design pattern. Dependency Injection reduces the hard-coded dependencies among your classes by inje...