Why Store Procedure improve performence

Dear Ravindra,

Reasons for using stored procedures:

Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.


Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.


Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.

Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc


Separation of logic -- remove the SQL-generating code and segregate it in the database.


Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.


Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.


Optimization -- It's easier for a DBA to optimize the SQL and tune the database when sprocs are used. It's easier to find missing indexes and such.


SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection


For more details please follow this link: http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mo[^]