Sometimes keeping the processing in server is more effecient. For example if a complex insurance or banking system which processes customer details in bulk, such as premium renewals for tens if thousands of customers, these never need to leave the server. It would daft storing these business rules off the server.
I have worked on systems where there is a lot of logic in the server and systems where there is barely any. Each solution was appropriate to the problem.
Calling "no stored procedures" as "best practice" is the biggest load of bs I have seen put out in the Internet in a long time.
You might feel the need to reduce strain on your server if your hardware is crap, but all you have done is move it
Things to think about:
1. Excessive disk io. This is due to badly written SQL. Stored procedures execute in memory. They use much the same SQL you would have used if you didn't use sp's
2. Web services
There is always a price in connecting outside of you network, but there are many cases where it's worthwhile.
3. Connecting to the db
This is always expensive. Connection pooling solves this if used properly. It's not on issue for users who connect and stay connected but can be disaster for web users and bulk processes which are not setup correctly to leverage connection pooling.
4. Network
Why hit your db server with hundreds of small queries when you only need to send minimal info to return a result? I'm sure everyone is aware of what excessive network activity can do to a server ? Consider a standard query which returns the customer details as well as info held in other tables. Call a sp by passing the customer I'd and getting back the results. One call. Simple. Instead of making 3, 4 or more. Per call. Per user.
5. Code maintenance
Having complex business ruled stored on the db server is far more effecient than having them duplicated in multiple systems. Less room for mistakes where multiple copies need updating(I suspect this is the main reason why some so called experts don't use sp's as a matter of principle. They don't know the transact or pl/SQL language. Lack of skills) Interestingly, some of the largest and most successful companies use sp's. I would trust their judgement over that of some of the "experts" we have here
6. Security
Stored procedures hide your data structures and ensure that outside users cannot construct their own queries which may cause problems. The last thing you want when running critical batch job is for some knob(usually senior) from executing a query which does full table scan on your largest transaction table.
Last edited:
