Sai A

I have a designed a smart client application which talks to WCF Services --> BL--> DAL and finally the database. Since I know that I would using only SQL Server for this application, I am of the opinion for going in for Stored Procedures(for its obivious benefits) and having transactions being handled at Business layer using System.Transactions.

My Lead Architect is against the idea of using Stored Procs. His argument is that with SP approach it would be a question of getting the right transcation boundaries and also ensuring that the messaging and the transction are in a single distributed XA transaction. He says that these things are best achieved through individual statements and not in SP since SP's have their own txns.
But wouldnt transactions be managed eventually by the respective resource managers ( in this case SQL Server). I dont quite follow his argument. He comes from a J2EE background wherein I have seen quite a few applications then were written to make it database agnostic. I have done lot of applications starting from .Net 1.0 and I never faced any issues with using Stored Procs.

I am somehow not convinced with his explanation. I have used Stored procs in couple of large scale projects and they worked fine with transactions being controlled at Enterprise Services / COM+.

I am of the opinion that if I know that I would be using SQL Server its better that I tried to leverage the database specific features for performance.

Would appreciate if someone can shed some light on this.


Re: Architecture General Stored Procedure Approach for developing Applications: An Architecture Viewpoint

Diego Dagum

Hi Sai,

   I'm not a master in SQL Server 2005 but as far as I know what your leader says isn't correct

   You can have an SP with own trxs in SQL Server if you explicitly declared inside one of these two commands:

   Without these you do not control the transactional unit from the SP. More on controlling transactions here:


   Btw, SPs has an important benefit compared with single commands that is, imagine you have to load several records associated with one object instance. Typical case, the Purchase Order header and its items (in the object space is a PurchaseOrder instance which has a collection of PurchaseOrderItems)

   You can serialize that as XML, send the XML stream as argument to an SP, parse the XML argument thanks to the XML features of SQL Server and apply all the INSERTs/ UPDATEs in just one round trip

   With single commands, you'll have n+1 round trips. n being the quantity of items, the 1 being the header


(If you consider this answer solves the thread, pls tag it as USEFUL -- Thanx!)

Re: Architecture General Stored Procedure Approach for developing Applications: An Architecture Viewpoint


I am a SQL Server 2005 master and Diego is correct,using or not using stored procedures has nothing to do with transaction management from your middle tier components. System.transactions will make your middle tier more efficient by only enlisting is a distributed transaction when it needs to and the is the same whether you are calling a stored procedure or executing queries directly from the middle tier logic. Even if the stored procedure has explicit BEGIN and COMMIT TRANSACTION statements, the transaction in effect when the stored procedure is called will take precidence over the internal transation.

Re: Architecture General Stored Procedure Approach for developing Applications: An Architecture Viewpoint

Sai A

Thanks Diego and Roger for your valuable insight. These past couple of days I have been having heavy discussions on this with the concerned people in my team. Some of the interesting things that came to my notice were

  • 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 ( 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.
To tell you the truth, I never knew that Stored Procs and scalability had any relation until I read this article. Though I am still not clear on how Stored Proc does affect stability. I am still to see a connection.

Would be very eager to know your opinions on the same.


Re: Architecture General Stored Procedure Approach for developing Applications: An Architecture Viewpoint


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


Re: Architecture General Stored Procedure Approach for developing Applications: An Architecture Viewpoint


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.