Benefits of Stored Procedures - Explained (Part 1)

Network Latency

One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications.

case 1.

when we call a stored procedure from your application we just make a small RPC call while image if the stored procedure encapsulates 200~500 lines of SQL code, and if the same was to be moved to application code all the SQL in form of string will first have to sent across the network creating number of network packets and now imagine if 100 users are calling the same code at once you have one very busy network.

case 2.

when we are executing a number of statements based on some business logic, it is best all the business processing is done at stored procedure level. eg. lets say we have master detail relashionship where master data and detail data is to updated or inserted in one go. now if we were to use client side or application to handle all this we will first have to createa a master insert statement (T-SQL) , that would be sent across the network like explained above and then we will need to retrieve the master id just created with the insert statement. Then we proceed to creating a update or insert statement for the details table and now following the same process of sending the T-SQL to database and then having the needful done. while it would be just one call to a stored procedure with all the required parameter to handle all the above defined.

It is recommend to move as much of business logic to the stored procedure in that you would be moving all the processing to the sql server reducing the round trip network traffic.

In case you have any questions or comments please post them i will try and respond.