Which is faster for multiple SQL calls - parameterized queries or stored procedures?

Question

I'm using SQL Server 2005. I'm looking at opening a SQL connection, looping though a collection and running an update query (stored procedure or parameterized query) with the data from the collection item, then closing the connection.

Which is going to give me better performance and why?

Answer 1

Usually stored procedures, because the server can pre-optimize the execution plan. Though well-written parameterized query is better than an over-general stored procedure.

Answer 2

Stored Procedures are usually the way to go. That said it also depends on how well your proc is written. Try running an Execution Plan on your Stored Procedure to make sure you're getting the best bang for your buck. Additionally using a Stored Procedure is usually the more secure way to go as well as the best performance on your server, provided the SQL instance is not on the same box as your code. When you use the stored procedure you put the load of the work on the SQL box, which will have optimized the query for you in the stored procedure.

Answer 3

In recent versions of SQL server, execution plans are cached for stored procedures and parametrized queries. The query will have a one time cost to create the execution plan, but this will be very minor and lost in the overhead of even a few calls.

From a performance perspective they will be almost identical.

The exception to this, is if you are doing recursive or nested queries (for each row in query 1, execute query 2), where the round trips between the client and server will add up. In this case, a proc will be much better.

Many companies still have "Everything as a proc" rules, but this is usually due to control of the database and not performance related. As LINQ grows in popularity, this may lessen.

If your application has a single (or few) points of deployment, then use whichever you prefer. On the other hand, if you are deploying to many many installations, then stored procs can in some (but not all) cases allow you to update database functionality without redeploying the client code.

Answer 4

It is difficult to say with certainty as there are a number of factors that can effect performance. In theory the Stored Procedure method should be faster.

There is another solution where you can pass XML to the stored procedure so you don’t have to call it multiple times. Assuming you can and know how to serialize your object into XML. Then you can use Xquery, Open XML or sp_XML_preparedocument to retrieve your data in a set based manner from the XML and perform the update statement.

Answer 5

Using a direct query or a stored procedure doesn't differ much in performance (if any), but if you are running the same query over and over with different data you should definitely use parameters.

Use the Prepare method to ensure that the same execution plan is reused:

• Create the SqlCommand object with the query/procedure.
• Create SqlParameter objects with specified data types but without values, and add them to the Parameters collection of the command.
• Call the Prepare method on the command object.
• Loop through the data just setting the parameter values and execute the command.

Answer 6

This can often turn into a religious debate between programmers and DBAs. Many programmers tend to like the prepared statement approach as it allows them complete control over the query that is being executed while DBAs like the stored procedure approach for the same reason. If you don't have that defined line between developer and DBA in your company and you dabble in both development and DBA roles then I would probably lean more toward the stored procedure route because if you need to make slight changes to the query in the future to fine tune performance or fix bugs then you don't have to recompile and redeploy your application.