Stored Procedures vs Parameterized Queries

Question

Do you prefer to use stored procedures or parameterized queries? What is your argument supporting your choice? Is one method really any better than the other in terms of performance, security, maintainability... etc?

Answer 1

Related to this is (with good answers):

http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-moder

Answer 2

From the .NET tag (and only from the tag) I'm going assume SQL Server here. Parametrized queries are excellent for projects that have a single application and modest security requirements. The require less effort to set up, and many ORM systems are going to use them if you like it or not.

On the other hand, if your database is handled by multiple applications and you need to enforce consistency of access, or if you need complex security (partial access to tables, either by column or by row or both) you should be using stored procedures. They allow you to enforce your security requirements and provide a consistent interface so applications don't violate your data rules.

As far as performance goes, I haven't noticed enough difference between the two to care these days: parametrized queries plan caching seems to work well enough.

Answer 3

Stored Procedures are going to have better performance for complicated queries, this is because when you store the procedure the DB has the opportunity to compile the procedure in advance, with parametrized queries it need to be compiled every time it is run.

I think StoredProcedures are better for maintainability because they can create a clear interface to the database, you can use them to add a layer of abstraction to the database.

As for security I believe StoredProcedures (because they are precompiled) are less susceptible to SQL injection. That is of course unless you decide to use dynamic queries within the stored procedure (ugly).

Answer 4

Also related:

Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s?

Answer 5

Two questions you might ask yourself when consider this are:

If I have to make big changes to the way the database is structured, which would I rather rewrite?

and

When I'm debugging an issue, which would make my life easier?

Answer 6

Hi,

Here are some advantages for Stored procedures:

In terms of maintainability:

One could use parametrized queries for simple/trivial operations, like retrieving data, or inserting data in table. Stored procedures are easier to maintain than inline queries if you have complicated (processing) code. Who would like to maintain program source code mixed with complicated SQL code in the same file. Also, almost all of the parametrized queries that are complex, can be found splitted in multiple string concats. Believe it or not, I've seen a SQL query concatenated in over 60 lines length.

In terms of speed:

First, you could think about the time it takes to compile the query and, also, the time it takes to create an execution plan (Both of them are sometimes longer than the execution time of the query). The stored procedures are compiled only once, unless specified, and the execution plan is stored, so a restart of the server wouldn't loose it. Also, for queries, the execution plans aren't created right away, but after a few calls of the same query (depending on the databse engine).

In terms of connection:

If you have a slow connection to the sql server, and large queries, you would probably be better off using stored procedures, as they generate less network traffic, and usually less roundtrips between the server and the client.

Also, there are also lot's of other advantages/disadvantages of using one over another, but the bid would be for stored procedures in most of the cases.

At this moment there is no advantage coming throug my mind for parametrized queries, but i would leave others to come with them.

Answer 7

I like to use stored procedures because they help me write transaction oriented code rather than very object oriented code. They isolate the database logic. I don't have to compile anything to deploy them. So fixing them after deployment of the main app is easier. For simple queries that only read data, I just use parameterized queries. For anything that modifies the database or uses a complex select I use a stored procedure.

Stored procedures' big advantage in performance is keeping the data on the sql server and not having to transfer it to the client to process it. The whole idea of batch processing data and minimizing cursor use.

Stored procedures' big advantage in security is they can be secured via permissions like tables. Also you can enforce business level data security in them.

Answer 8

Bogdan has said it already. Think of it in this way - do you want to write clean separation of application code and data access layer, or do you want to be like those vilified PHP programmers who embed the HTML throughout their scripts?

Placing SQL inside your application and calling the DB is easy - from an application developer's point of view, but SPs are the better approach in every way. Once you've created them, they're easy to debug (especially with the tools that come with SQL Server)

The ultimate approach is to design your application, and then give the required data access components to a DBA to code for you - he gives you an API to call, and the internal niceties of figuring out how to return the requested data is up to him, who is hopefully much more of an expert in how to make the data relationships work better than you could do.

Parameterized queries work, and there's no reason prohibiting them, but just like you take the time to properly design your app, you should take the time to put SPs in.