Last post on the topic — at least for now!

Wow! It’s great to see discussion, that’s the best way to learn new things.

Frans makes some very valid counter-points. I’ll eat crow over my original posting for stating:

‘When stored procedures are used SQL Server can cache or pre-compile the ‘execution plan’ that it uses to execute the SQL vs. having to recalculate the execution plan on each request.

I’ve edited the original post to reflect the fact that SQL Server does not pre-compile the stored procedure. I was incorrectly using cache/pre-compile interchangeably and semantically this was incorrect, mea culpa.

I also edited the post to add a note on using parameterized ad-hoc SQL which is also an effective counter-measure to SQL Script injection attacks – as many people pointed out, and which I should have included originally.

At the end of the day it’s important to make your own decisions. However, I do find it somewhat interesting that the two biggest critics of the stored procedure approach build O/R mappers; I expected to get some feedback from Thona - who I am looking forward to eventually meeting some day! I do want to call out that O/R mappers are incredibly valuable technology solutions that can save a ton of time. I don’t have any personal experience with either Thona’s or Frans’ solutions, but I’m sure both can save a ton of dev-time – personally looking forward to replacing my stored procedures with CLR code in SQL Server ‘Yukon’. 

I did stumble across this while catching up this morning:

http://radio.weblogs.com/0101986/stories/2003/11/12/whenDidDynamicSqlGetSuchABadName.html

This benchmark is both invalid and valid – all depending on how you write your code. The stored procedure examples used both encapsulate checks on the parameters, while the ad-hoc SQL approach does not. A more valid approach (IMHO) would be to encapsulate either the ad-hoc SQL or the stored-proc call in a data access layer class. The data access layer (or a higher level business logic class) should validate the inputs – the validation should not occur in the stored procedure. In this case the sproc should look something like:

CREATE PROCEDURE GetOrders
 @sCustomerID nchar(5),
 @iEmployeeID int,
 @iShipperID int
AS
SELECT  *
FROM Orders
WHERE
 
CustomerID= @sCustomerID AND
 EmployeeID= @iEmployeeID AND
 ShipVia= @iShipperID

In this case it’s pretty obvious that a sproc is now overkill. I’ve re-posted my original side-note from the first article:

Side-note: if you jumped to the conclusion that the point of all this is to advocate all business logic residing in stored procedures, you missed the point. There needs to be balance. My general rule of thumb is: return only the necessary data, no-more no less. The more efficient you can make your data access code by rapidly opening and closing the connection to the database the better; stored procedures provide you with an effective tool for accomplishing this. Performing business logic is frowned upon, i.e. transforming the data or performing calculations on the data. Use the power of the database to thresh the wheat from the chaff. Use your business logic to turn the wheat into dough ;)

Several people have missed the point . Stored procedures provide an effective way for accessing the data, but they are not the only way. But no matter which you choose neither should enforce business logic rules on the data.

In cases such as the above sproc it doesn’t take a rocket scientist to tell you that ad-hoc SQL is a better/simpler solution. However, take a more complex application such as the ASP.NET Forums used for solutions such as the Xbox forums. There are 472,000 posts in the Xbox forums Game Discussions forum. When viewing posts does it make sense to retrieve all 472K posts and then throw away all but the current 25 that you are viewing? Most likely not (if you didn’t guess, the correct answer is no). In this case a stored procedure is used to page the data in the database and only return the set of data that is applicable. If you think ad-hoc SQL is a better solution for this and to then filter the data in your DAL/BAL … please email me I’ve got some spectacular beach property in Montana that I’d like to sell you ;)

To state it one more time: there needs to be balance. Choose the solution that best solves the problem. If you take the attitude that one approach is always right one is always wrong, the only guarantee is that you’ll always be wrong.

Edit:
In the forums example, when stating the filtering of data, I don’t simply mean using a WHERE clause to reduce the set. Instead, the ability to selectivly choose a smaller subset from the set that SQL would create to satisify the original WHERE clause, i.e. when there are 1000 results returned but you now want to selectivly page through that set.


11 Comments

  1. ::I expected to get some feedback from Thona

    ::<g> – who I am looking forward to eventually

    ::meeting some day

    Did you not email me once you come to Germany soon? When can I expect you? Otherwise – well, we can spend some time at the next MVP Summit. Someone decided I am allowed another year as MVP, so I will come.

    ::personally looking forward to replacing my

    ::stored procedures with CLR code in SQL

    ::Server ‘Yukon’.

    Personally I am looking forward to move part of my O/R mapper INTO yukon – something the ObjectSpaces people seem to simply ignore as a possibility.

    ::When viewing posts does it make sense to

    ::retrieve all 472K posts and then throw away

    ::all but the current 25 that you are viewing?

    Not at all. Dynamic SQL does not mean you are stupid enough to fetch all – you still can put conditions into the dynamic SQL query, you know :-)

    ::If you think ad-hoc SQL is a better solution

    ::for this and to then filter the data in your

    ::DAL/BAL … please email me I’ve got some

    ::spectacular beach property in Montana that

    ::I’d like to sell you ;)

    Why do you go so extreme?

    I would say that basically dynamic SQL is a much better approach, but you would STILL filter at the database.

  2. As you say Rob, it’s a question of balance. I have exactly an opposite case on a project where I have to do a CRUD for a Census type of form. Huge number of questions, and I started with Dynamic SQL. What a mistake ! Slow, slow, slow. So I rewrite everything with an SP, sending the big data block to SQL using a User Function to split the data(no split function by default in SQL :-(), and yes it was the right choice. Much faster, giving back instantly the hand to the user, letting SQL working in the back end. In this debate, I didn’t see about Transactions. Because I use this a lot in my SP, and yes batch processing are very fast. What’s about Transactions and dynamic queries ? some info on this, performance for example?

  3. The question about paging is an interesting one. I bounce it back with a simple remark: Oracle has something as silly as ‘rownum’, which can be used in a select procedure to retrieve row n till n+m using rownum.

    SqlServer doesn’t have that. You therefore have to jump through hoops to retrieve pages. ALL solutions require executing the complete query at every page, plus you can’t get row 100 till 150 for example: you have to select it into a temptable or use knowledge about a PK value which is increasing.

    This isn’t a burden per se though. Most paging issues can be solved on the client, and most amount of pages is not that high. There are extreme cases where this is different, and in those cases perhaps a temptable using sproc is helping out. Ok, that’s 1 sproc per app :)

    Thanks for the reply, Rob. :)

    FB, who wrote 1 SP in the last 6 months: an oracle sequence emulator for sqlserver.

  4. Paschall: ADO.NET transactions are implemented on the connection object. If you do a trace on SqlConnection’s methods, you’ll see it executes a BEGIN TRAN statement when you create a transaction object using a connection. I don’t think there is much of a difference there.

    In ADO this was different, since ADO supported nested transactions, ADO.NET’s SQLServer client doesn’t , because SQLServer doesn’t (T-SQL doesn’t support nested transactions: you can’t roll back a subtransaction inside a transaction. You can use savepoints to emulate this a bit… Savepoints can be created in ADO.NET too.)

  5. > have to select it into a temptable

    Yup, but in this case if you can use a covered query. This is still pretty fast.

  6. I may have missed this in a previous post but I will state it anyway…The majority of devs dont use SPs for simple one off DML statements. One of the great values can be found when subsequent biz logic needs to be encapsulated around updates of a certain type.

  7. Re: "Oracle has something as silly as ‘rownum’, which can be used in a select procedure to retrieve row n till n+m using rownum. "

    This is addressed in changes in Transact SQL for Yukon (RANK and DENSE_RANK, as I recall). This was covered in a talk at PDC (yes, I know Yukin is still a ways off from production environments…).

  8. Well Frans I still think SP are great for some tasks and also for the flexibility of changing them quickly in SQL.

    Don’t kill me Frans ;-), I also use Dynamic Queries.

    I am not really agree about transactions, they are IMHO faster with SP.

    What I like with SP as I said is that when a project is finish, and I talk about web projects, I can still add a new field in my databse, and after modify my procdures, ading the new field where I need it and hop, it works.

    If I code my sql statements, I will have to recompile and deploy my codee again to my production servers, thing I find a bit silly, but as I said this is my honest opinion.

  9. Rob – I’ve practically grown up with the mantra outlined in your original post. It goes all the way back to the client/server days of VB4 :) So I understand. But perhaps I didn’t make my point strongly enough. A result of this mantra has been many many projects that rely on SP’s to the exclusion of other techniques. Anyway, I believe I did mention that the test proc is small in comparison to other sp’s I’ve seen in production that use both techniques described. In the end I think I would follow Frans’ lead to solve this particular issue.

  10. Seems to me this is more of a client side thing :)

    in ASP, the SP wasn’t the way to go at least according to:

    http://www.adopenstatic.com/experiments/recordsetpaging.asp

  11. http://

    i want to call a web service using javascript

    I need to call some data from server using java script