The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Stored Proc to avoid frequent builds

My new team heavily believes in stored procs and does let me use parameterized queries.

The argument they have is that by keeping these operations in stored procs, you can avoid building/redeploying the whole asp.net application. I know that there has been countless arguments on the benefits and disadvantages of stored procs but I can't seem to come up with a counter argument for this.

do I buy into it or am I missing something?
Slowhand Send private email
Friday, April 04, 2008
 
 
correction on my earlier post: They don't let me use parameterized queries
Slowhand Send private email
Friday, April 04, 2008
 
 
Stored Procs rock, Its a great way to insulate the app from DB structure changes. Which improves object re usability and reduces the ol bug count
Richard Corsale Send private email
Friday, April 04, 2008
 
 
Disclaimer: IANADBA
One of the advantages our DBA touts is that the execution plan for stored procs is stored and doesn't need to be recalculated.  Since parameterized queries are temporary, they have to be compiled & the execution plan reevaluated on every execution. Can make a difference on busy systems.

If SPs are entrenched, you'll likely have a hard time convincing anyone that programmer-written queries without the blessing of a DBA are not a risk. Not so much for data breaches but for things like unintended Cartesian joins etc.

Our product does a LOT of dynamic query building & execution, so I am not rabid about the 'all access thru SPs' thing.
a former big-fiver Send private email
Friday, April 04, 2008
 
 
Stored procs are a great way to encapsulate DB design details and manage DB performance and security, as mentioned above. So I'm a big fan of stored procs IF used properly.  However, if the reason for using them is to avoid frequent builds, that sounds like a motivation to include business and even presentation logic in stored procs, which is a recipe for disaster.
Mike Stockdale Send private email
Friday, April 04, 2008
 
 
What they hell do you care about how often you do a build? You will still need to test the entire system whenever you make a change to the database (or to the stored procedures) and that will take orders of magnitude longer than just building the software. Further, if you find that the database change has broken your software in some way, there is a reasonable chance that the fix will be in your source code, not in the database or stored procedures, so you'll have to do a build anyway.

There are good reasons to use stored procedures, but to avoiding re-building your software when you make changes to the database is NOT one of them.
Jeffrey Dutky Send private email
Friday, April 04, 2008
 
 
We've done this topic to death already. But the reason I'll chime in is because you've qualified it with the "frequent builds" issue.

I'll start by restating that we DON'T use stored procedures for many reasons.  But one of those reasons is that we would PREFER to just do a build and release source code than to have to alter a database. In our world, altering a database is 10 times more likely to result in issues than simply updating code. We have thousands of databases around the country (we write enterprise point of sale software) so on any given update you can expect a certain degree of failure. If the failure rate from updating code is .1% then the failure rate for applying a database schema or stored procedure change can be 1%. And with 1000 databases (or more) you will have to manually debug 1 store location if we update code but 10 store locations if we update the database. Many of these stores are only connected through dial-up so it is also much more painful to get database access and figure out what is screwed up. With code changes you can usually just xcopy the latest version of the files down to fix the problem. There is no xcopy equivalent for updating stored procedures and versioning in stored procedures is sketchy at best anyway.

There are many more reasons why we don't use stored procedures but I'll spare you the details. The bottom line is that they don't offer us any more security, performance, or ease of maintenance. Everything is in code which makes it much easier to deploy, version control, and troubleshoot. Our database is a data store only. No triggers, views, or stored procedures. For us that makes sense. Your mileage may vary.

People who rant about stored procedures have never lived in our world. They expect databases to be located in data centers and be under the watchful eye of a DBA. When your database is remote, only accessible by dial up, and the most technical person at the location is the 16 y/o flavor of the week with a MySpace page, stored procedures lose their lustre.
dood mcdoogle
Friday, April 04, 2008
 
 
I'll follow up that rant with a quick point. It should be clear from my rant that the benefit of using stored procedures varies greatly based on the architecture of the application.

In our case we are a desktop app with distributed databases. The opposite would probably be a wep app with a single database and many web servers configured in a web farm. In that case it would make more sense that "frequent builds" would be frowned upon. If you have 20 web servers then you would need to update all of them for a simple code change. But if you only have one database then you could update the stored procedure one time for the same basic change.

The OP didn't give any indication as to what type of application is being discussed. I can only assume that it is a web app running in a datacenter with one (or few) databases but many more web servers. If not, the issue of having to do frequent builds becomes less important.
dood mcdoogle
Friday, April 04, 2008
 
 
+1 for this being an architecture decision.

In architectures where the database integrity is the responsibility of the DB and you want to be able to manipulate the DB without changing the interface to the application (segmenting tables, normalizing, denormalizing, partitioning, schema changes, type changes, etc.), SPs are a tool to get you there, and the primary reason I give for it as a benefit is that it gives you a useful layer of abstraction and allows your DB to present a consistent interface contract which cannot easily be violated.

Yes, it changes to an SP will come without a compile, but it is not going to come without some kind of test and deployment.  When that deployment comes, it is likely to be able to be closer to no downtime, and can often be done live without users even noticing that the SP has been altered to provide new features or a bug fix.

In most apps I have built, the benefits of having an SP layer to define DB access are clearly evident.  I've worked with many systems, and ones where the boundary of the DB is clearly delineated and controlled are the easiest to maintain and have a high degree of confidence about data quality.
Cade Roux Send private email
Friday, April 04, 2008
 
 
Let me add:

The pre-compiled benefit is a red herring, and I think people who bring that up are bringing up performance because performance always trumps maintenance value, but the performance value is minimal - and you shouldn't be optimizing until you have a performance problem.

The security benefit is a partial red-herring, because unless you have a comprehensive security model and lock down direct access to your tables, your SPs aren't going to be any more secure.

The best benefit of SPs is control and improved lifecycle maintenance through the abstraction layer and well-defined interface.
Cade Roux Send private email
Friday, April 04, 2008
 
 
RE: former big-fiver
"One of the advantages our DBA touts is that the execution plan for stored procs is stored and doesn't need to be recalculated.  Since parameterized queries are temporary, they have to be compiled & the execution plan reevaluated on every execution. Can make a difference on busy systems."

For SQL Server, execution plan for custom queries are stored the same as stored procs.  For both types, the plan is generated on first use.  So as long as you're using parameterized queries there is no performance benefit to stored procedures.  This is not the nineties anymore.
Ted
Friday, April 04, 2008
 
 
stored procs marry you to your database engine. i'd prefer to use straight sql so i'm not married to m$sql or whatever. this said, the primary app i work on relies on stored procs to the degree that i'm using mssql server without a way to opt out.

-don
Don Dickinson Send private email
Friday, April 04, 2008
 
 
As I said, I'm not a DBA...
a former big-fiver Send private email
Friday, April 04, 2008
 
 
Stored Procs are easier to do development, testing and performance tuning on.  I can work in sql analyzer until the development of the stored proc is done and then I start working on the web page while staring the result set.  This is harder to do when you are building sql strings.

I also always have a sample call to the stored proc in the comments of the stored proc so that later I don't have to recreate the parameters, etc. just to see what the results looks like.

So it allows me to work on a smaller part of the system at a time making me more productive.
Jim Brooks Send private email
Friday, April 04, 2008
 
 
Hopefully they have them in version control somewhere... right?

I worked with a group years ago where a handful of the core people were *diligent* about making sure their code was covered and completely forgot the SP's.  Doh.
KC Send private email
Saturday, April 05, 2008
 
 
Two rules on Stored Proc,

1. No business logic - plain CRUD
2. SP are properly named and does one thing and thing only.

If the above rules are inforced, I love SP else, just kill me.

Saturday, April 05, 2008
 
 
I used SPs quite a bit in Oracle and some in SQL Server. In Oracle the deployment issue would almost argue against use of SPs:

10:00 Session A signs in, runs an SP
10:30 Session B, a developer, alters the SP
10:35 Session A tries to run the SP, get the Oracle error meaning "the state of the package has changed."
George Jansen Send private email
Saturday, April 05, 2008
 
 
Well George, you shouldn't be doing that in production.  In development we give each developer (and the build system) their own schema.  That way the developer can make any modifications he wants and not affect anyone else.  We have a build script for the db that will do a complete tear down and rebuild of their schema.  So they can always reset to a known state.

One nice thing about stored procs is that we can define the call the Java guys make to the db.  I can stub out the call and just have it return something hard coded.  Then they can hook up to this while I flesh out the details of the guts.  Once I am finished I check in the filled out stored procs (packages in Oracle) and the Java guys do a get from subversion. I have run my unit tests by then and then they can run and develop theirs.

It allows us to work in parallel.
Jim Send private email
Saturday, April 05, 2008
 
 
People write that stored procedures tie you to the particular database.

But the way I would do it is that the database dependent features can be hidden by the stored procedures like a language independent API. The downside being that you require a set of stored procedures for each supported database.

Doesn't that work ?
not the DBA
Monday, April 07, 2008
 
 
3 quick points

To Dood - if you get ten times the failure rate with one environment than another, one explanation is that the environment sucks - the alternative is that maybe you understand one environment better than another.

To those that want to insulate the app from the data - how many 7 year old apps do you have? Now how much 7 year old data? In general data outlives apps, and is accessed by multiple apps, of course code reuse is wonderful.

To george - you have developers changing code on the fly in production and that's the fault of where the code sits? huh?
Niall Litchfield
Monday, April 07, 2008
 
 
"if you get ten times the failure rate with one environment than another, one explanation is that the environment sucks - the alternative is that maybe you understand one environment better than another. "

Or maybe one environment is just more complex. Applying SQL/DDL changes requires running database scripts which includes connecting, disconnecting, committing, and versioning through another application. Applying code changes requires copying files which is a standard OS function that is readily handled by most remote control software. The main issue is that our updates need to be automated because all systems are remote and there are so many of them. So it is much easier to create an automated way to copy files than it is to automatically execute database scripts without errors. As I indicated, our failure rate is pretty low. But with thousands of stores you will always get some failure. The question is: do you want to manually diagnose file versioning issues with one store or database versioning issues with 10 stores over a dial-up connection? I'd prefer to just log in to one store and copy some files.
dood mcdoogle
Tuesday, April 08, 2008
 
 
"For SQL Server, execution plan for custom queries are stored the same as stored procs.  For both types, the plan is generated on first use.  So as long as you're using parameterized queries there is no performance benefit to stored procedures.  This is not the nineties anymore."

The lifetime of the execution plan for stored procedures are "forever". I think the lifetime of the plan for custom queries is until the next query in the session that is different.

"The pre-compiled benefit is a red herring, and I think people who bring that up are bringing up performance because performance always trumps maintenance value, but the performance value is minimal - and you shouldn't be optimizing until you have a performance problem."

The real advantage is the predictability. With custom queries, the DBAs have no idea what the performance of the query will be. In an environment where the transactions have to be fast and predictable, stored procedures might make maintaining a high level of performance easier.

"The security benefit is a partial red-herring, because unless you have a comprehensive security model and lock down direct access to your tables, your SPs aren't going to be any more secure."

Yes, if you are using stored procedures for security, the permissions on tables has to be very restricted. It would be easier to fix the permissions after the fact if stored procedures are used. It would be difficult to fix this if stored procedures are not used.

=============

Another advantage of stored procedures is beinga ble to creat queries that avoid lots of data to be sent across the network.
somebody else
Tuesday, April 08, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz