This article describes that how we can optimize the Stored Procedures so that we can get better performance while our application is talking to database. I am enlisting some of the points from my personal experience.
1) Always Use Fully Qualified Name For All Database Objects:
While working with stored procedure we need to pass name of database objects (table, view, function, other stored procedure(s) etc.) several times. I recommend every developer to use fully qualified object names instead of using only object name. There is a very strong reason behind this recommendation.
SQL server always has to find supplied database object name from sys object that took some millisecond of processing time and consumption of CPU resources. And if, we pass fully qualified name then that processing time and CPU resource consumption to search the object from sys objects get reduced to significant amount.
Another reason behind the scene is it helps SQL Server to directly finding the Complied Cache Plan if available instead of searching the objects in other possible schema. This process of searching and deciding a schema for a database object leads to COMPILE lock on stored procedure which decreases the performance of a stored procedure.
