Stored procedures or inline queries?

Question

First of all there is partial question regarding this, but it is not exactly what I'm asking, so, bear with me and go for it.

My question is, after looking at what SubSonic does and the excellent videos from Rob Connery I need to ask: Shall we use a tool like this and do Inline queries or shall we do the queries using a call to the stored procedure?

I don't want to minimize any work from Rob (witch I think it's amazing) but I just want your opinion on this cause I need to start a new project and I'm in the middle of the line, shall I use SubSonic (or other like tool, like NHibernate) or I just continue my method that is always call a stored procedure even if it's a simple as

Select this, that from myTable where myStuff = StackOverflow;

Answer 1

It doesn't need to be one or the other. If it's a simple query, use the SubSonic query tool. If it's more complex, use a stored procedure and load up a collection or create a dataset from the results.

See here: http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code and here http://stackoverflow.com/questions/228175/subsonic-and-stored-procedures

Answer 2

See answers here and here. I use sprocs whenever I can, except when red tape means it takes a week to make it into the database.

Answer 3

I wouldn't personally follow rigid rules. Certainly during the development stages, you want to be able to quickly change your queries so I would inline them.

Later on, I would move to stored procedures because they offer the following two advantages. I'm sure there are more but these two win me over.

1/ Stored procedures group the data and the code for manipulating/extracting that data at one point. This makes the life of your DBA a lot easier (assuming your app is sizable enough to warrant a DBA) since they can optimize based on known factors.

One of the big bugbears of a DBA is ad-hoc queries (especially by clowns who don't know what a full table scan is). DBAs prefer to have nice consistent queries that they can tune the database to.

2/ Stored procedures can contain logic which is best left in the database. I've seen stored procs in DB2/z with many dozens of lines but all the client has to code is a single line like "give me that list".

Because the logic for "that list" is stored in the database, the DBAs can modify how it's stored and extracted at will without compromising or changing the client code. This is similar to encapsulation that made object-orientd languages 'cleaner' than what came before.

Answer 4

Stored procedures are gold when you have several applications that depend on the same database. It let's you define and maintain query logic once, rather than several places.

On the other hand, it's pretty easy for stored procedures themselves to become a big jumbled mess in the database, since most systems don't have a good method for organizing them logically. And they can be more difficult to version and track changes.

Answer 5

I've done a mix of inline queries and stored procedures. I prefer more of the stored procedure/view approach as it gains a nice spot for you to make a change if needed. When you have inline queries you always have to go and change the code to change an inline query and then re-roll the application. You also might have the inline query in multiple places so you would have to change a lot more code than with one stored procedure.

Then again if you have to add a parameter to a stored procedure, your still changing a lot of code anyways.

Another note is how often the data changes behind the stored procedure, where I work we have third party tables that may break up into normalized tables, or a table becomes obsolete. In that case a stored procedure/view may minimize the exposure you have to that change.

I've also written a entire application without stored procedures. It had three classes and 10 pages, was not worth it at all. I think there comes a point when its overkill, or can be justified, but it also comes down to your personal opinion and preference.

Answer 6

Are you going to only ever access your database from that one application?

If not, then you are probably better off using stored procedures so that you can have a consistent interface to your database.

Is there any significant cost to distributing your application if you need to make a change?

If so, then you are probably better off using stored procedures which can be changed at the server and those changes won't need to be distributed.

Are you at all concerned about the security of your database?

If so, then you probably want to use stored procedures so that you don't have to grant direct access to tables to a user.

If you're writing a small application, without a wide audience, for a system that won't be used or accessed outside of your application, then inline SQL might be ok.

Answer 7

I prefer inline sql unless the stored procedure has actual logic (variables, cursors, etc) involved. I have been using LINQ to SQL lately, and taking the generated classes and adding partial classes that have some predefined, common linq queries. I feel this makes for faster development.

Edit: I know I'm going to get downmodded for this. If you ever talk down on foreign keys or stored procedures, you will get downmodded. DBAs need job security I guess...

Answer 8

With Subsonic you will use inline, views and stored procedures. Subsonic makes data access easier, but you can't do everthing in a subsonic query. Though the latest version, 2.1 is getting better.

For basic CRUD operations, inline SQL will be straight forward. For more complex data needs, a view will need to be made and then you will do a Subsonic query on the view.

Stored procs are good for harder data computations and data retrieval. Set based retrieval is usually always faster then procedural processing.

Current Subsonic application uses all three options with great results.