Comment Re:What's your opinion (Score 1) 206
As an app developer and our company's backup DBA, I say this isn't a black-and-white issue.
Here are some very good reasons why you'd use stored procs.....
*Speed is highly critical. Maybe you expect thousands of hits per minute; where poorly-tuned SQL could be lethal.
*Security is unusually critical. Maybe you're storing credit card numbers.
*Uniformity. Maybe an account balance can be changed from multiple apps, or multiple places in one app.
But there is overhead too.....
*Added layer of bureaucracy. Sarbanes-Oxley in the U.S. places heavier red tape on changes to the database (which stored procs count as). App changes are less regulated---for now. What business decision doesn't factor in regulatory burdens?
*Loss of flexibility. If you widen a column in the DB, most engines require all stored proc parameters referring to that column be adjusted too. When stored procs call each other, you may need to change each one in each possible call path. And you may need to change the apps to reflect the altered field size as well.
*Code-intensive. When writing SPs, count on writing code 3 times: first in the SP, second declaring the SP parameters in the SP header, and finally in the app, hooking up the SP parameters to values from your app. An unsung issue here is quality: the more you write by hand, the higher the risk of bugs...even if you are a DBA. Middleware such as JDBC or Microsoft's ADO/ADO.NET allow you to get the job done in fewer lines of code.
*Debuggers. My experience is in the Microsoft world, where app-side tools are vastly superior to SQL tools---which are barely above Notepad in functionality. Like it or not, this is a quality issue: testing is superior in the app world.
So, in sum, I'd say use stored procs where performance or security is of unquestioned importance. Also use them where multiple apps may do the same repetitive tasks---especially if those tasks carry importance.
But if your app fits one/more of these: is small, workgroup-specific, "lower-volume" (realistically this is decided by your hardware), or can be protected only by network security...who cares?
For those interested, here are a few "myths" about stored procs I've observed with Microsoft SQL Server......
*SPs run faster than equivalent embedded SQL. FALSE...with SQL Server 2000, embedded SQL is also precompiled and cached after first use.
*SPs are "safer". DEPENDS...if you must grant a user access to write their own queries, where reasonable just give them a read-only account. If you let them create SPs (ala "GRANT CREATE PROC"), they could write code in the SPs to alter data---hmmmm..."safe"?
*SPs are not vulnerable to SQL-injection. DEPENDS...Microsoft's ADO can be programmed in ways that leave even SPs vulnerable. The key here is to parameterize your statement and trap for escape characters, which is just as easily done with embedded SQL. Many middleware tools can trap injection automatically.
*SPs are "safer" II. DEPENDS...Top-flight security should involve an application role---even more than using SPs alone. Properly used, this prevents users hacking into the DB or SPs thru Access or something they downloaded. Nothing is more irritating than a DBA who thinks his data is safe, because of all his SPs, but then GRANTS EXEC TO THOSE SPs DIRECTLY TO THE NETWORK USER ACCOUNTS!
Here are some very good reasons why you'd use stored procs.....
*Speed is highly critical. Maybe you expect thousands of hits per minute; where poorly-tuned SQL could be lethal.
*Security is unusually critical. Maybe you're storing credit card numbers.
*Uniformity. Maybe an account balance can be changed from multiple apps, or multiple places in one app.
But there is overhead too.....
*Added layer of bureaucracy. Sarbanes-Oxley in the U.S. places heavier red tape on changes to the database (which stored procs count as). App changes are less regulated---for now. What business decision doesn't factor in regulatory burdens?
*Loss of flexibility. If you widen a column in the DB, most engines require all stored proc parameters referring to that column be adjusted too. When stored procs call each other, you may need to change each one in each possible call path. And you may need to change the apps to reflect the altered field size as well.
*Code-intensive. When writing SPs, count on writing code 3 times: first in the SP, second declaring the SP parameters in the SP header, and finally in the app, hooking up the SP parameters to values from your app. An unsung issue here is quality: the more you write by hand, the higher the risk of bugs...even if you are a DBA. Middleware such as JDBC or Microsoft's ADO/ADO.NET allow you to get the job done in fewer lines of code.
*Debuggers. My experience is in the Microsoft world, where app-side tools are vastly superior to SQL tools---which are barely above Notepad in functionality. Like it or not, this is a quality issue: testing is superior in the app world.
So, in sum, I'd say use stored procs where performance or security is of unquestioned importance. Also use them where multiple apps may do the same repetitive tasks---especially if those tasks carry importance.
But if your app fits one/more of these: is small, workgroup-specific, "lower-volume" (realistically this is decided by your hardware), or can be protected only by network security...who cares?
For those interested, here are a few "myths" about stored procs I've observed with Microsoft SQL Server......
*SPs run faster than equivalent embedded SQL. FALSE...with SQL Server 2000, embedded SQL is also precompiled and cached after first use.
*SPs are "safer". DEPENDS...if you must grant a user access to write their own queries, where reasonable just give them a read-only account. If you let them create SPs (ala "GRANT CREATE PROC"), they could write code in the SPs to alter data---hmmmm..."safe"?
*SPs are not vulnerable to SQL-injection. DEPENDS...Microsoft's ADO can be programmed in ways that leave even SPs vulnerable. The key here is to parameterize your statement and trap for escape characters, which is just as easily done with embedded SQL. Many middleware tools can trap injection automatically.
*SPs are "safer" II. DEPENDS...Top-flight security should involve an application role---even more than using SPs alone. Properly used, this prevents users hacking into the DB or SPs thru Access or something they downloaded. Nothing is more irritating than a DBA who thinks his data is safe, because of all his SPs, but then GRANTS EXEC TO THOSE SPs DIRECTLY TO THE NETWORK USER ACCOUNTS!
