I'm not a fan of stored procedures (SPs) for anything except complex data processing generally.
Having just read Writing and Calling Stored Procedures, I felt like explaining my point of view on this stuff. It's sunday after all.
Firstly, I'm developer who considers sql to be mandatory for the developer, rather than having dba's doing all the sql stuff.
That's just an approach which creates an unnecessary bottleneck in the development cycle. I have often seen bad results when developers don't understand the database and it's concepts, or maybe the DBA's didn't understand (or have time to) the application either.
DBA's are there to help with the complex stuff. Use them wisely (and cost effectively!)
This example of using SP's to ends up adding yet another layer to your app and is more complex & longer that the code it intends to replace. Suddenly it's also more work to say add a column to the insert as you have yet another chunk of code to update.
Using stored procedures means you can't easily work with multiple versions of code against the same database (as your logic in SP's is only once per db, not back in the app layer). You lose the ability to easily version your data access layer and hence things tend to stagnate.
If you database is 10 GB, do you really want to have to be running a different instance for each build with changes to the db layer. Doesn't that sounds like a rather expensive approach? It's a recession after all.
Tuning SQL for Coldfusion app with stored procedures, is also more complex in many ways! Personally I love how easy it is to tweak the sql in normal bound CFQUERIES. Copy from debug, tweak in something like TOAD and then update the CF code, easy!
What I love the most about Coldfusion is how clean looking (aside from the verbose cfqueryparam) the database support is when compared to other programming langauges.
Alas the ugly embedded sql in strings syntax of some other languages, almost necessitates SP's
Plus you can't port the app to another (perhaps free database) without rewriting the entire sql layer! Expensive huh?
SQL Server people seem to love (and needs) SP's more than with say Oracle. With Oracle you get the same result and performance just by using simple bound sql. I think the horrible muck that was classic ASP database access really started this trend.
A lot of the common uses of SP's I have seen is what I consider Premature Optimisation
Most of the performance myths about SP's date back to the old days of 100Mhz servers. Servers are cheaper than developers right? Not that I'm advocating something which is going to make your app run slower either (generally speaking).
My Advice, avoid SP's (until there's a clear issue which they can resolve) and enjoy the nice flexibility of bound sql and faster development cycles.
Sunday, May 17, 2009
KISS: Avoid Stored Procedures and save money!
Labels:
coldfusion,
database,
project managment
4 comments:
What about the following reasons for stored procs:
1. Seperation of presentation & data layers. We seem to have lost the business layer in most modern web apps. The procs are your interface to the DB it doesn't matter if you are calling them from websites, desktop apps, web services or wherever. You just need to interface with your proc.
For example I have 180+ websites that use the same database. Having the SQL all in one place is easy for maintanance, let a DBA (if you have one) do their work easily.
2. Security. You mention SQL injection which is obviously a serious problem with all the automated hackbots about. If you are doing your CRUD operations (create,read,update,delete) from the app layer it means your SQL logon that is used to connect to the DB has to have write permission assigned to it. Therefore even if 99.9% of your code is properly parameterised it only takes one mistake on a Monday morning when you are slightly hung over and a bit sloppy and you carry out a
SELECT * FROM BLAH WHERE Key = PARAM
statement in one of your pages
and forget to sanitise the PARAM parameter value to make sure its an integer and within minutes a hackbot has found page, malformed your post/get data and changed the usual value of 10 to 10;DELETE
FROM(SELECT NAME FROM sys.tables) as a
(thats just a quick hack off the top of my head many more will inject <script src='virus.js'> tags into all your text based columns.) and there goes your data unless your DBA (who may not even exist if its a small company) has locked access to all system views.
3. I am personally of the opinion that only CUD needs to be done in procs and SELECTs can still be handled from the web. As long as your web logon only has membership to the SQL role of
db_datareader
then even if you do have a slip up on a Monday morning when your slightly hung over the hacker won't be able to update/delete/insert anything even if they do find a hole in your system.
4. Network traffic is reduced. If you are passing hundreds of characters containing your SQL from web to DB on each page load that is a lot of traffic compared to just the name of the proc with a param value or two e.g
SELECT Name,Address,Town,Country FROM CUSTOMERS as c JOIN CUSTOMER_ACCOUNTS as a OM c.CustomerPK = c.CustomerFK WHERE CustomerPK = 35456
compared to
EXEC dbo.usp_get_customer 35456
I know its a lame example but it shows what I mean.
5. Some people complain that adding new parameters to a proc means having to update reams of app code to handle it. This is not true. You can add the parameter to a proc with a default value so that it doesn't matter if its passed to the proc or not.
So there are 5 reasons to use stored procs. You may not agree with all of them and I would love to hear your response to each in turn if possible. My own blog page about cleaning up a DB after a successful SQL injection hack
http://blog.strictly-software.com/2008/09/recovering-from-sql-injection-hack.html
has had so many hits becuase of these automated hack bots that point 3 I feel is very important in this day and age.
Thanks for a good blog article.
1. having your sql only in CFC's is also just one place, all depends on your approach.
2. I tend to disagree with the argument about SQL injection, both stored procs and bound sql suffer from the same problem.
Using bound parameters applies to both approaches, you can make the same mistake with both.
3. The length of an exec vs sql is argument over a few hundred bytes, usually over localhost or a 1 gig connection? bytes!!!!!I'll follow up more later, I got some work to do :)
I'm just posting as a reminder to follow-up on this later. FWIW, I'm in R Reid's camp. Bottom line: On a high transaction site, stored procs will do you better than inline / CFC based queries for more reasons that Reid lists.
If you have a need to write SQL for different DBs, then make sure your object model's data layer has branches for each DB.
Ok, I've read the article and it's a bit of an overreaction and doesn't justify SP's, IMHO.
Having all queries bound, type checking and a bit of input filtering as suggested in the blog entry ( perhaps the built in cfadmin measures as well) and your safe.
Just because you get stung bad from a mistake, doesn't mean you need to change they way you work, if it was a mistake.
It's important to make these calls as a developer, avoid premature optimisation.
@iKnowKungFoo i agree about high transaction sites, if it makes sense to do it. all depends on what your doing.
Too often I have seen stored procs used where it was only a cftransaction around the statements which was required to make it performant.
For a lot of common projects, the time spent coding in SP's could potentially easily pay for a much faster database server, or two etc.
Development is expensive, hardware is cheap.
Post a Comment