Disclaimer: I'm someone with a background mainly in application programming and generally find databases boring to deal with. I'd rather write a rdbms than use one.
I've spent a lot of time with a particular web-application recently which relies very heavily (way too heavily IMO) on stored procedures.
Whenever a bug in the system arises, 80% of the time it's a buggy stored procedure (not the fault of SPs - just illustrating the degree to which they are used in this system). A great deal of business logic is evidently present on the database server.
One stated pro of stored procedures is performance. I'm not going to argue that in some circumstances they can give a performance boost. Wasting time running between the app and database servers is clearly to be avoided. Thus data intensive processing seems to be the prime candidate for implementation in stored procedures. But it's not as simple as saying "sp's are faster (especially for data intensive processing) so let's use them lots".
What I see when I look at the load average on our DB server is this:
load averages: 0.30, 0.20, 0.14
And over on the App server we have this:
load averages: 0.05, 0.04, 0.07
With just a limited amount of business logic implemented in the application server, it mainly just shovels requests and
responses between the web server and the database server.
Currently the system has just a few users on it, but in future there will be something like hundreds of users. When that day comes (well before that day comes in fact), the database is just going to choke under the increased load.
Let me recall the worst case of sp abuse that I can identify. When the system was "designed", rather than keeping running totals of a certain set of values cached at the app server, and only updating the database when there was a change in the underlying figures, the system was designed to call an sp which would recalculate the totals right from the start, everytime a user made a request to see the latest data. At the technical review, I raised this as an issue, but the allknowing consultant rejected the concern saying that "it's fine, the database will be very fast".
Eventually, the system was released and the users found themselves waiting for up to 40 seconds for a their page to be refreshed. Their existing system (to be retired) can display a similar set of data within 1 to 2 seconds, at the most.
Here's the thing: The designer could rightly have used the justification that "we'll use a stored procedure because this is data intensive processing". As our frustrated end-users discovered, it certainly was!
So I'd add an additional rule of thumb regarding the use of stored procedures:
Stored procedures may be fast, but not calling a stored procedure at all is potentially magnitudes of times faster. If you can cache results and avoid recalculation, you don't even need to go to the database in the first place.
This experience illustrates to me very clearly that you don't want a "specialist" in a given technology designing your system.
Disclaimer: I'm someone with a background mainly in application programming and generally find databases boring to deal with. I'd rather write a rdbms than use one.
I've spent a lot of time with a particular web-application recently which relies very heavily (way too heavily IMO) on stored procedures.
Whenever a bug in the system arises, 80% of the time it's a buggy stored procedure (not the fault of SPs - just illustrating the degree to which they are used in this system). A great deal of business logic is evidently present on the database server.
One stated pro of stored procedures is performance. I'm not going to argue that in some circumstances they can give a performance boost. Wasting time running between the app and database servers is clearly to be avoided. Thus data intensive processing seems to be the prime candidate for implementation in stored procedures. But it's not as simple as saying "sp's are faster (especially for data intensive processing) so let's use them lots".
What I see when I look at the load average on our DB server is this:
load averages: 0.30, 0.20, 0.14
And over on the App server we have this:
load averages: 0.05, 0.04, 0.07
With just a limited amount of business logic implemented in the application server, it mainly just shovels requests and
responses between the web server and the database server.
Currently the system has just a few users on it, but in future there will be something like hundreds of users. When that day comes (well before that day comes in fact), the database is just going to choke under the increased load.
Let me recall the worst case of sp abuse that I can identify. When the system was "designed", rather than keeping running totals of a certain set of values cached at the app server, and only updating the database when there was a change in the underlying figures, the system was designed to call an sp which would recalculate the totals right from the start, everytime a user made a request to see the latest data. At the technical review, I raised this as an issue, but the allknowing consultant rejected the concern saying that "it's fine, the database will be very fast".
Eventually, the system was released and the users found themselves waiting for up to 40 seconds for a their page to be refreshed. Their existing system (to be retired) can display a similar set of data within 1 to 2 seconds, at the most.
Here's the thing: The designer could rightly have used the justification that "we'll use a stored procedure because this is data intensive processing". As our frustrated end-users discovered, it certainly was!
So I'd add an additional rule of thumb regarding the use of stored procedures:
Stored procedures may be fast, but not calling a stored procedure at all is potentially magnitudes of times faster. If you can cache results and avoid recalculation, you don't even need to go to the database in the first place.
This experience illustrates to me very clearly that you don't want a "specialist" in a given technology designing your system.