Stored Procedure Approach for developing Applications: An Architecture Viewpoint

• When I was talking to my Database Architect he came out with this analogy that if its single or few records updates use parameterised queries else if its batch records updating 100's or more of records use Stored Procs. Something which I found very hard to digest. Of course I have seen situations where people put everything and anything into SP's with looking at a holistic view but havent come across this sought of explanation for a very long time.
• Another interesting issue that the Lead Architect brought about was scalability. He is of the opinion that moving logic to database affects scalability in the long run as databases by its very nature cannot be scaled horizontally unless one does a lot of data jugglery.He further adds that it can be scaled vertically but a typical vertical scale costs more $ than horizontal. He finally then summarized it as volumes grow larger most of the ecommerce sites drop the following capabilities from their database
• Stored Procedures
• Triggers
• Joins & Indexes
• He passed on a wonderful case study of eBay (http://www.addsimplicity.com.nyud.net:8080/downloads/eBaySDForum2006-11-29.pdf) If you look specifically on Page 21 under the heading of "Scaling Data Tier: Minimize DB Resources" they have given recommendations on No Stored Procs etc.

Rogers and Diego comments on this post propmpts me to ask a question here.
I have a doubt in my mind of a scanerio in which we implement System.Transactions in business layer and then call two stored procedures
from inside System.Transaction.Now each stored procedure have a Begin Distributed transaction inside it.
So what i need to know from here is which transaction will take precedence and why.
I need to know some internals about how it take place and how sql server 2005 enhacements do increses the performance of stored procedures

regards

They're ganging up on you. It doesn't sound like this is a fight you're going to win. Like any blanket statement; saying stored procedures don't perform is way too general to be meaningful. If stored procedures were uniformly a bad thing, the database vendors wouldn't have spent millions of dollars implmenting them. The whole concept of basing the need for a stored procedure on how many rows it processes is kind of silly. SELECT * FROM a billion row table processes a whole bunch of rows but would be silly to put into a stored procedure while a stored procedure might do a hundred operations to validate and format the data to insert one row. As Diego said, one of the main performance gains with stored procedures is reducing the number of network round trips by doing more work with each command from the middle tier. The other big benefit of stored procedures is they are usually compiled less often than queries form the middle tier which can cut down on the processor load on the database server and increase scalability.

Basically you need to look at the tradeoffs. Stored procedures in general increase theperformance of database intensive operations with a possible loss of scalability. This tradeoff point changes a little bit in SQL 2005 because of CLR stored procedures. We've had people move significant batch logic from the middle tier into CLR stored procedures and get huge performance increases. I would agree that if scalability is more important than performance as it is for some ecommerce sites then moving as much processing to the middle tier as possible is probably a good move. The middle tier is obviously a lot cheaper and easier to scale out than the database.

I enjoyed the no joins and no indexes comment. There's a word for a database with no joins and no indexes - it's called a file. I'm guessing that Ebay doesn't do a sequential scan through a hundred million items every time someone submits a bid. I'm pretty sure even eCommerce sites are going to need indexes.

Bottom line is you need to consider what's important to your application and what the business logic is doing to decide whether SP's are appropriate or not. The real high volume applications do as little work as possible against as simple a schema as possible online and do the complex processing asynchronously in the background. That usually means no SP's in the front-end databases and a lot of SP's in the back end. The real decision has to be based on meeting the business requirements. I can make an ecommerce site really fly by not updating the inventory or accounts receivable but chances are the CEO wouldn't approve.