I have to admit I don’t like stored procedures. I understand their advantages, but I still don’t like them, so it’s probably irrational thinking.
Anyway, I think SQL Server programmers have an equally irrational love for stored procedures, and I’ll try to explain why, so I do not feel alone in my irrationality ;)
There are several reasons to use stored procedures. My focus here is in the ‘it has better performance/it scales better’ argument.
The SQL Server best practices suggest writing a T-SQL stored procedure for each sentence you want to execute. There was a good discussion in the developmentor CLR list about this some time ago.
Oracle programmers code in PL/SQL, but it’s not very common to see ‘one-sentence-stored-procedures’ in PL/SQL. I’m not an Oracle expert, but in the Oracle developments I was involved, there was no PL/SQL, or complex PL/SQLs, but not the kind of stored procedures you see in SQL Server.
There are a lot of Oracle applications that have high performance and scale, and that are not using stored procedures ‘a la SQL Server’, they are probably using ‘Prepared Statements’ from the middle tier.
Java programmers seldom use stored procedures. They are not portable, it breaks the ‘write once run anywhere’ motto, and it means to not to write in Java, and we know Java programmers just code in Java (this is a joke, I can explain it upon request).
Java programmers build high performance applications that scale.
So, the theory that if you don’t use stored procedure your applications won’t have good performance or scale does not seem to be right.
Anyway, SQL Server programmers insist to make their lives more complicated and write stored procedures. I think this is for the combination of two reasons:
First, in SQL Server, you cannot have two SQL commands/sentences executing at the same time. If you want to have two open cursors, you cannot do it. If you want to have an open cursor and do an ‘Update’, you cannot do it. The way to be able to do this kind of thing was to use ‘server cursors’. There are a set of stored procedures that work with server cursors. One lets you create a cursor and returns a handle, other lets you fetch the next block of records, another lets you close it. So, if you work with ‘server cursors’, you are really executing stored procedures, so you do can have any number of open cursors. ODBC/OleDb/JDBC drivers usually mask this behavior with their cursor support. Server cursors impose more load in the server, so they should be avoided if you want to have good scalability.
Second, in VB apps/ASP pages that used ADO, it was very easy to use server cursors, you could do it without noticing it, so naively written ADO apps usually had bad performance and do not scale well. The best way to fix this was to convince the developers to use stored procedures instead of doing SELECT statements in the client. This way, there was no way to use server cursors, and the apps will scale better.
In my opinion these are the reason why some SQL Server developers always use stored procedures. It was the way Microsoft found to discourage the use of server cursors.
Some other facts that seem to be in line with my thoughts:
- ADO.NET has no support for server cursors. This way naive programmers can't go wrong. ADO.NET applications usually have good performance/scalability without the use of stored procedures.
- Microsoft’s latest implementation of the .NET PetShop application does not use stored procedures.
1:15:12 AM 
|
