how to optimise the stored procedure

That is Big Question. Lets see your Stored Procedure

Thank you for posting at Dotnetfunda
[Administrator]

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Generally stored procedure is optimized by removing unnecessary use of Temporary tables, cursors.

Also try to avoid long running queries. Use Clustered index on queries which requires complex join statements. By this way most of the optimization could be performed.

Also if you are using SQL Server Management Studio, Check the "Actual Execution Plan" of your stored procedure. If there are long running queries, you can right click on the line to see what optimization you require as well.

Always check for Table Scanning. If there are lots of percentage load on Table Scanning, try to build indexes on them.

Happy Coding.

www.abhisheksur.com

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Some more points are there :

1. If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
2. Try to avoid using temporary tables inside your stored procedure.
3. Use NOCOUNT,If you turn on the NOCOUNT option, stored procedures won't return row-count information-and therefore, you'll save the network overhead involved with communicating that information to the client. To set NOCOUNT, simply insert SET NOCOUNT ON as the first statement in the stored procedure.

Regards,
Chikul

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

if we use composite primary key
then how many indexes will created

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

A great question and a must know question !!
Here is all about it !!
Please everyone, do study it

http://www.sqlservercentral.com/articles/Performance+Tuning/67427/

Best Regards,
Puneet Sharma - Infosys
Pune, India

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator