Search Result

Are stored procedures faster?

This debate has been going on for years.

"Back then" when I was learning the basics of database management, one of the advocating points for the usage of stored procedures (SP) was performance. Fast forward some years and all I have read is that they perform exactly the same as a parametrized query. Is this correct?

Probably depends on your RDBMS, the SQL statement itself and your very specific scenario, but most likely the answer is no. In Microsoft SQL Server and Oracle, Stored Procedures are compiled and then stored in a global stored procedure cache, in MySQL the process is somewhat different. This is usually the reasoning behind why they perform better.

In SQL Server, when you execute a stored procedure what happens is the following:

The syntax is checked.

A plan is determined in order to execute the SQL statement (use a clustered or non-clustered index, etc.).

The SQL is executed.

The SP is cached.

The thing is, from SQL Sever 2005 onwards, all SQL statements are compiled and cached, regardless if they are a SP or inline code.

Network traffic is another argument of why stored procedures perform better, since they are executed in batches rather than sending multiple requests. This point however, is very debatable.

If there are many applications requesting the SQL server it can lead to increased network traffic, depending on the number of transactions. If for example, a heavy SQL query is sent to the server in-line, it would need several network packets to transmit the code to the server, rather than executing something that is already stored in the server.

With modern broadband connections, or cases in which the SQL and application code are in the same data center, this point may be negligible. Again, it depends on the situation. Whatever your case may be, always try to reduce the number of round trips.