Wednesday, April 05, 2006

To use stored procedures or not to use stored procedures

I thought this battle was settled down, and we had all agreed that stored procedures (from now on called SPs) are (for the most part) the way to go when accessing your SQL data

turns out Ayende is not quite convinced yet, in fact it seems he prefers to use no stored procedures at all, I told him SPs can give you scalability, flexibility, security, etctirity, and here's his response

"I completely disagree with this, if you didn't catch on so far. Here is my refutal:

Scalability:
There is no performance advantages to using SP over Parameterized SQL.
Scaling the database is much harder than scaling an application.
Note: I implemented a caching web service once that simply cache data for certain period of time, saving quite a bit from the poor database. Deploy a bunch of those, and you're seriously reduced the amount of work the database has to do.
Flexibility:
Stored Procedures are... Well, procedures. This means that you've all the advantages and disadvantages of procedural programming. On general, I wouldn't call it very flexible. Changing the procedure's internal can be done without breaking clients is possible within a certain set of changes, but major stuff is hard.
A search procedure should be flexible, you should be able to search by any combination of parameters, how easy it is to write this procedure?
Security:
Not applicable in situations where you don't give users direct access to the database, in my opinion. There are things to be said about defense in depth, but I don't think that they should be curried to the point where you are forced to give up significant advantages while keeping the security of the system (using views / triggers instead of procedures, for instance, can result in a situation just as safe, and far easier for the programmers to work with effectively)."


let's see

"Scalability:
There is no performance advantages to using SP over Parameterized SQL. "


...you probably posted that in the incorrect section, Scalability and performance are different things

"Scaling the database is much harder than scaling an application. "
that's just another reason to separate the data rules (SPs) from your application

"Note: I implemented a caching web service once that simply cache data for certain period of time, saving quite a bit from the poor database. Deploy a bunch of those, and you're seriously reduced the amount of work the database has to do. "
you can still cache the result of the stored procedures, I don't see how that's an advantage of not-using-stored-procedures

"Flexibility:
Stored Procedures are... Well, procedures. This means that you've all the advantages and disadvantages of procedural programming. On general, I wouldn't call it very flexible. Changing the procedure's internal can be done without breaking clients is possible within a certain set of changes, but major stuff is hard. "

even if they are just a few, I still didn't have to recompile (and potentially distribute the application) just to make those little changes

"A search procedure should be flexible, you should be able to search by any combination of parameters, how easy it is to write this procedure? "
just as easy as it is to write a query on the app, besides that, I can make important changes to the stored procedures, perhaps look in other tables (history, cache, etc), take other parameters to use the same SP in another application, etc; again, without having to recompile my app

"Security:
Not applicable in situations where you don't give users direct access to the database, in my opinion. There are things to be said about defense in depth, but I don't think that they should be curried to the point where you are forced to give up significant advantages while keeping the security of the system (using views / triggers instead of procedures, for instance, can result in a situation just as safe, and far easier for the programmers to work with effectively). "

secure programming is all about good practices, the stupid SQL injection is because people just do

"select * from users where userName = "+UserID

if they would've used a stored procedure, that would've been ok, the same thing would've been accomplished by using a parameterized query as you point out

but is much more than that, security is not only who can get access to your data, is who can mess up with your data, and changes to that are far easier accomplished using SPs; is the exact same concept you use when you break up your code in files and classes, you give each class a responsability (I hope), is the same thing with the database, you give the database it's responsability and let your application do it's responsability. it's about using the right tool to get the job done

2 comments:

Ayende Rahien said...

"that's just another reason to separate the data rules (SPs) from your application" - How is that a reason?

"you can still cache the result of the stored procedures, I don't see how that's an advantage of not-using-stored-procedures" - Because if you use WS, you don't care how you do this. And scaling out is far easier than using SP.

"even if they are just a few, I still didn't have to recompile (and potentially distribute the application) just to make those little changes" - Who needs to recompile for those changes. I'm not advocating embedded SQL here, you realize.
In fact, I'm advocating a real service tier and not bulid in the databases using procedural programming.

"secure programming is all about good practices, the stupid SQL injection is because people just do" - And I'm not talking about string concantation to do SQL either.

"ty is not only who can get access to your data, is who can mess up with your data, and changes to that are far easier accomplished using SPs" - This is not the correct layer to handle security. Not by far.

BlackTigerX said...

"How is that a reason? "
I already mentioned, I can archive tables to history-tables, modify views, etc, and any changes would be applied just to the stored procedure to manage the scalability of the data

"I'm not advocating embedded SQL here, you realize.
In fact, I'm advocating a real service tier and not bulid in the databases using procedural programming."
ok, that's where the misunderstanding was then, you're just using something else to replace stored procedures, which to me it just seems like reinventing the wheel, of course there are a some (I have not yet come to such case) cases where you would need something like that, but very specific cases

"- And I'm not talking about string concantation to do SQL either."

at least we agree on something

"This is not the correct layer to handle security. Not by far."
some times it is (at least a part of it), some times it isn't, it depends on the project