Which is better: Ad hoc queries, or stored procedures?

Question

Assuming you can't use Linq for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hoc queries against the database (say, Sql Server for argument's sake)?

Answer 1

Stored Procedures are great because they can be changed without a recompile. I would try to use them as often as possible.

I only use ad-hoc for queries that are dynamically generated based on user input.

Answer 2

My answer from a different post: Stored Procedures are MORE maintainable because:

• You don't have to recompile your C# app whenever you want to change some SQL
• You end up reusing SQL code.

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

What happens when you find a logic error that needs to be corrected in multiple places? You're more apt to forget to change that last spot where you copy & pasted your code.

In my opinion, the performance & security gains are an added plus. You can still write insecure/inefficient SQL stored procedures.

Easier to port to another DB - no procs to port

It's not very hard to script out all your stored procedures for creation in another DB. In fact - it's easier than exporting your tables because there are no primary/foreign keys to worry about.

Answer 3

Depends what your goal is. If you want to retrieve a list of items and it happens once during your application's entire run for example, it's probably not worth the effort of using a stored procedure. On the other hand, a query that runs repeatedly and takes a (relatively) long time to execute is an excellent candidate for database storage, since the performance will be better.

If your application lives almost entirely within the database, stored procedures are a no-brainer. If you're writing a desktop application to which the database is only tangentially important, ad-hoc queries may be a better option, as it keeps all of your code in one place.

@Terrapin: I think your assertion that the fact that you don't have to recompile your app to make modifications makes stored procedures a better option is a non-starter. There may be reasons to choose stored procedures over ad-hoc queries, but in the absence of anything else compelling, the compile issue seems like laziness rather than a real reason.

Answer 4

There are persuasive arguments for both - stored procedures are all located in a central repository, but are (potentially) hard to migrate and ad hoc queries are easier to debug as they are with your code, but they can also be harder to find in the code.

The argument that stored procedures are more efficient doesn't hold water anymore. link text

Doing a google for Stored Procedure vs Dynamic Query will show decent arguments either way and probably best for you to make your own decision...

Answer 5

Procs for the reasons mentioned by others and also it is easier to tune a proc with profiler or parts of a proc. This way you don't have to tell someone to run his app to find out what is being sent to SQL server

If you do use ad-hoc queries make sure that they are parameterized

Answer 6

Parametized SQL or SPROC...doesn't matter from a performance stand point...you can query optimize either one.

For me the last remaining benefit of a SPROC is that I can eliminate a lot SQL rights management by only granting my login rights to execute sprocs...if you use Parametized SQL the login withing your connection string has a lot more rights (writing ANY kind of select statement on one of the tables they have access too for example).

I still prefer Parametized SQL though...

Answer 7

In my experience writing mostly WinForms Client/Server apps these are the simple conclusions I've come to:

Use Stored Procedures:

1. For any complex data work. If you're going to be doing something truly requiring a cursor or temp tables it's usually fastest to do it within SQL Server.
2. When you need to lock down access to the data. If you don't give table access to users (or role or whatever) you can be sure that the only way to interact with the data is through the SP's you create.

Use ad-hoc queries:

1. For CRUD when you don't need to restrict data access (or are doing so in another manner).
2. For simple searches. Creating SP's for a bunch of search criteria is a pain and difficult to maintain. If you can generate a reasonably fast search query use that.

In most of my applications I've used both SP's and ad-hoc sql, though I find I'm using SP's less and less as they end up being code just like C#, only harder to version control, test, and maintain. I would recommend using ad-hoc sql unless you can find a specific reason not to.

Answer 8

Some things to think about here: Who Needs Stored Procedures, Anyways?

Clearly it's a matter of your own needs and preferences, but one very important thing to think about when using ad hoc queries in a public-facing environment is security. Always parameterize them and watch out for the typical vulnerabilities like SQL-injection attacks.

Answer 9

Stored procedures represent a software contract that encapsulates the actions taken against the database. The code in the procedures, and even the schema of the database itself can be changed without affecting compiled, deployed code, just so the inputs and outputs of the procedure remain the same.

By embedding queries in your application, you are tightly coupling yourself to your data model.

For the same reason, it is also not good practice to simply create stored procedures that are just CRUD queries against every table in your database, since this is still tight coupling. The procedures should instead be bulky, coarse grained operations.

From a security perspective, it is good practice to disallow dbdatareader and dbdatawriter from your application and only allow access to stored procedures.

Answer 10

Stored procedures are definitely the way to go...they are compiled, have execution plan before hand and you could do rights management on them.

I do not understand this whole source control issue on stored procedure. You definitely can source control them, if only you are a little disciplined.

Always start with a .sql file that is the source of your stored procedure. Put it in version control once you have written your code. The next time you want to edit your stored procedure get it from your source control than your database. If you follow this, you will have as good source control as your code.

I would like to quote Tom Kyte from Oracle here...Here's his rule on where to write code...though a bit unrelated but good to know I guess.

1. Start with stored procedures in PL/SQL...
2. If you think something can't be done using stored procedure in PL/SQL, use Java stored procedure.
3. If you think something can't be done using Java Stored procedure, consider Pro*c.
4. If you think you can't achieve something using Pro*C, you might want to rethink what you need to get done.

Answer 11

Store procedures should be used as much as possible, if your writing SQL into code your already setting yourself up for headaches in the futures. It takes about the same time to write a SPROC as it does to write it in code.

Consider a query that runs great under a medium load but once it goes into fulltime production your badly optimized query hammers the system and brings it to a crawl. In most SQL servers you are not the only application/service that is using it. Your application has now brought a bunch of angry people at your door.

If you have your queries in SPROCs you also allow your friendly DBA to manage and optimize with out recompiling or breaking your app. Remember DBA's are experts in this field, they know what to do and not do. It makes sense to utilise their greater knowledge!

EDIT: someone said that recompile is a lazy excuse! yeah lets see how lazy you feel when you have to recompile and deploy your app to 1000's of desktops, all because the DBA has told you that your ad-hoc Query is eating up too much Server time!

Answer 12

My experience is that 90% of queries and/or stored procedures should not be written at all (at least by hand).

Data access should be generated somehow automaticly. You can decide if you'd like to staticly generate procedures in compile time or dynamically at run time but when you want add column to the table (property to the object) you should modify only one file.

Answer 13

I haven't found any compelling argument for using ad-hoc queries. Especially those mixed up with your C#/Java/PHP code.

Answer 14

someone said that recompile is a lazy excuse! yeah lets see how lazy you feel when you have to recompile and deploy your app to 1000's of desktops, all because the DBA has told you that your ad-hoc Query is eating up too much Server time!

is it good system architecture if you let connect 1000 desktops directly to database?

Answer 15

I can't speak to anything other than SQL Server, but the performance argument is not significantly valid there unless you're on 6.5 or earlier. SQL Server has been caching ad-hoc execution plans for roughly a decade now.

Answer 16

is it good system architecture if you let connect 1000 desktops directly to database?

No it's obviously not, it's maybe a poor example but I think the point I was trying to make is clear, your DBA looks after your database infrastructure this is were their expertise is, stuffing SQL in code locks the door to them and their expertise.

Answer 17

In our application, there is a layer of code that provides the content of the query (and is sometimes a call to a stored procedure). This allows us to:

• easily have all the queries under version control
• to make what ever changes are required to each query for different database servers
• eliminates repetition of the same query code through out our code

Access control is implemented in the middle layer, rather than in the database, so we don't need stored procedures there. This is in some ways a middle road between ad hoc queries and stored procs.

Answer 18

I prefer keeping all data access logic in the program code, in which the data access layer executes straight SQL queries. On the other hand, data management logic I put in the database in the form of triggers, stored procedures, custom functions and whatnot. An example of something I deem worthy of database-ifying is data generation - assume our customer has a FirstName and a LastName. Now, the user interface needs a DisplayName, which is derived from some nontrivial logic. For this generation, I create a stored procedure which is then executed by a trigger whenever the row (or other source data) is updated.

There appears to be this somewhat common misunderstanding that the data access layer IS the database and everything about data and data access goes in there "just because". This is simply wrong but I see a lot of designs which derive from this idea. Perhaps this is a local phenomonon, though.

I may just be turned off the idea of SPs after seeing so many badly designed ones. For example, one project I participated in used a set of CRUD stored procedures for every table and every possible query they encountered. In doing so they simply added another completely pointless layer. It is painful to even think about such things.

Answer 19

These days I hardly ever use stored procedures. I only use them for complicated sql queries that can't easily be done in code.

One of the main reasons is because stored procedures do not work as well with OR mappers.

These days I think you need a very good reason to write a business application / information system that does not use some sort of OR mapper.

Answer 20

I think this is a basic conflict between people who must maintain the database and people who develop the user interfaces.

As a data person, I would not consider working with a database that is accessed through adhoc queries because they are difficult to effectively tune or manage. How can I know what affect a change to the schema will have? Additionally, I do not think users should ever be granted direct access to the database tables for security reasons (and I do not just mean SQL injection attacks, but also because it is a basic internal control to not allow direct rights and require all users to use only the procs designed for the app. This is to prevent possible fraud. Any financial system which allows direct insert, update or delete rights to tables is has a huge risk for fraud. This is a bad thing.).

Databases are not object-oriented and code which seems good from an object-oriented perspective is can be extremely bad from a database perspective.

Our developers tell us they are glad that all our databse access is through procs becasue it makes it much faster to fix a data-centered bug and then simply run the proc on the production environment rather than create a new branch of the code and recompile and reload to production. We require all our procs to be in subversion, so source control is not an issue at all. If it isn't in Subversion, it will periodically get dropped by the dbas, so there is no resistance to using Source Control.

Answer 21

Store procedure work as blok of code so in place of adhoc Querey it work fast. another thing is store procedure give re complile option which the best part of sql you just use this for store procedurs nothing like this in adhoc Query's.

Some result in Query and store procedure are diffrent that's my personal exp. use cast and covert function for check this.

Must use store procdure for big projects to improve the performance.

i had 420 procedures in my project and it's work fine for me i work for last 3 year on this project.

so use only Proceduer for any Transaction.

Answer 22

The sproc performance argument is moot - the 3 top RDBMs use query plan caching and have been for awhile. Its been documented... Or is 1995 still?

However, embedding SQL in your app is a terrible design too - code maintenance seems to be a missing concept for many.

If an application can start from scratch with an ORM (greenfield applications are far and few between!) its a great choice as your class model drives your DB model - and saves LOTS of time.

If an ORM framework is not available we have taken a hybrid of approach of creating an SQL resource XML file to look up SQL strings as we need them (they are then cached by the resource framework). If the SQL needs any minor manipulation its done in code - if major SQL string manipulation is needed we rethink the approach.

This hybrid approach lends to easy management by the developers (maybe we are the minority as my team is bright enough to read a query plan) and deployment is a simple checkout from SVN. Also, it makes switching RDBMs easier - just swap out the SQL resource file (not as easy as an ORM tool of course, but connecting to legacy systems or non-supported database this works)