SP! SP! SP! SP! SP!

Last Friday, during a project meeting I was challenged about the performance of SQL Server stored procedures and XML support on SQL Server 2000.

You may not know, but I’m a strong believer of SQL Server 2000 (I think it is such a great product) and even more of stored procedures. I’m also very keen of the XML support on it, as I have achieved many good results on solving complex problems with these tools.

I couldn’t then sleep with it and I thought that I had to prove my point and technically demonstrate that by using a stored procedure encapsulated transaction with XML data could be much faster than running a transaction controlled by the client application.

I have then created a couple of tables in a classic Master-Detail model and replicated this table structure with different names. I have then written a SQL stored procedure that accepted as parameter a single XML document. Everything it does is:

1 – Parse the document
2 – Set the transaction isolation level to READ COMMITTED
3 – Begin the transaction
4 – Extract the master data from the root element’s attributes and insert in the master table using the INSERT INTO/SELECT statement.
5 – Extract the data from the child elements’ attributes and insert it in the details table using another INSERT INTO/SELECT statement.
6 – Check if there are any errors (I do it after each INSERT statement)
7 – Given that everything went OK, commit the transaction. Otherwise, roll it back.

It was working with the first pair of tables.

For the second pair of tables, identical to the first one, I have created two stored procedures. They accepted as parameters all the data needed by the table and just did an insert of such data.

After that I went to the client app. It was a .NET Console app. The main method had 10000 iterations launching two threads by iteration and these threads did the following:

First thread was calling a method to create an XML document with one master element and ten children elements. This method used an System.Xml.XmlDocument to create such XML. It then passed the XML as parameter to the SqlCommand and called the first stored procedure.

The second thread called one time the first stored procedure for the second set of tables passing plain strings and ints as parameters. It then looped 10 times calling the second stored procedure for the second set of tables passing other strings and ints as parameters.

I tracked the time each thread took to complete a transaction and if it completed the transaction at all.
The results of my tests were quite surprising:

Under low workload with the database and the client application running in the same computer, the performance of both transactions was very similar. Some times the local transaction was completing in slightly less time, but as we are talking about milliseconds here, the difference was next to nothing.

When moving the database to another computer and calling it over the network, things started to change in favour of the first stored procedure.

I have then started to launch the client application many times in each of my three computers at home, loading the database with thousands of simultaneous requests and mainly overloading the network.

As I increased the workload the first stored procedure performed consistently good, while the second scenario with the application controlled transaction increasingly degraded its performance. I have consistently reached between 500 and 1300% more performance with the stored procedure transaction.

When I reached critical overloads consuming almost my entire network throughput and some transactions started to fail, one other thing became obvious: The server controlled transaction was completing in a rate of 5 for 1 application controlled transaction. It is easily explainable:

To process the first transaction, the stored procedure one, I have only to send one network request. It means that if I’m able to send that single request to the server, I’m very likely to complete the transaction.

To process the application controlled transaction, in the other hand, I have to make (in the case of this example) 11 network calls. If ANY of those fail, the entire transaction fails! It has much more failing points than the first one.

In the end I was able to prove my point and highlight the following points:

- XML processing on the database is a slight overhead that is completely compensated by the performance gains of a single database call when you have more than a dozen transactions.
- Encapsulating complete transactions in a single stored procedure call (being it a single or nested procedures) improves performance and reliability
- Stored procedure transactions scale immensely better
- The maintainability of a complex stored procedure may be lower as you have more code in a single place, but it is just a question of moving code from one place to another. The procedure code will be inside the stored procedure or in the client application and whichever place it is, the maintainability of such a place will lower down.

Given that, I’m believing SQL Server and stored procedures even more and that’s why I tend to write all my data transactions entirely in T-SQL/XML and put then in SQL Server.

When designing an application you have to divide what is a business transaction and what is a data transaction. The first one you leave in the application because that’s its natural place. The second do a favour for yourself and put then in the database. The gains increase as your user base grows.

If someone would like to see the source code for my tests, just drop me an e-mail and I will send them. It’s not rocket science and I can share it with anyone.

That’s all folks, go to bed and tomorrow, when you wake up in the morning remember to thank Bill Gates for SQL Server stored procedures! :-D