Monday, December 07, 2015

Ways to Improve Stored Procedures

There are multiple ways to improve the SP few of them without doing any code modification are

Do not prefix your Stored Procedure with sp_.
In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is looked into masters database first before it is looked into the database it is called in.
Call your Stored Procedure prefixed with dbo.SPName – fully qualified name.
    When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.

No comments:

Post a Comment