Why you should use stored procedures for application queries

Performance Benefits

A stored procedure can help you encapsulate logic and let you reuse queries from many parts of your application. The fact that stored procedures use parameters will help the optimizer find the optimal execution plan regardless of your parameters. This will in many cases give you some performance benefits.

How the optimizer works

Consider the following query to fetch order data from a table.

SELECT * FROM ORDERS WHERE ID = 2

When you run this query, the optimizer will find the optimal path to row ID = 2, and stores the execution plan for this exact query.

If you run this query again you will see that the query is a bit faster than the first time your ran it. That is because the optimizer has already found and created stored an execution plan for this query.

The next time your application runs, it fetches the result for ID = 3.

SELECT * FROM ORDERS WHERE ID = 3

Now the optimizer will again try to find an optimal path, but this time for another row with id = 3 and store the execution plan ready to be reused.

The thing now is that the query optimizer considers each query with as a completely new query even if just the id changes.

It gets even worse; if you introduce an extra space character to your first query, the optimizer counts this as a unique query and tries to find a new query plan.

SELECT * FROM ORDERS    WHERE ID = 2

* Notice the extra space character before WHERE

Stored procedures to the rescue

We could easily rewrite our simple query to a stored procedure with the orderId as an input parameter.

CREATE OR ALTER PROCEDURE spOrders_GetById
(@orderId INT)
AS
BEGIN
     SELECT * FROM ORDERS WHERE ID = @orderId
END

To call this procedure and fetch results for id = 2 you would use the following command:

EXEC spOrders_GetById @orderId = 2

The query optimizer will now find the optimal path for the command used and store an execution plan.

The next time call the stored procedure, the execution plan is already stored even if the input parameter changes. 

You will notice that your query will run a couple of ms faster than before.

Database logic in one place

Another benefit of using stored procedures is that the logic for retrieveing order info is stored and maintained in one place. If you need to change the quere, you will only need to change it in one place.

Possible to unit test

The fact that a stored procedure helps you move database logic from your application, closer to the database gives you testable code. You can easily run database unit tests to verify that your code behaves as expected.