Worried About Yukon - Iterative vs. Set Based

Sun Sep 28, 2003 by Ajarn Mark Caldwell in musings-and-ramblings

Tara's response to this post on SQLTeam got me to thinking about one of my big concerns with Yukon (the upcoming version of SQL Server). Last year, one of the features that was being promoted big-time was the idea that you would be able to use the .Net languages to write your stored procedures. Now, I haven't heard any details on the actual final implementation of this (maybe we'll find out at the PASS Community Summit in Seattle this year) but it makes me worried, and here's why: People who are used to writing code in VB.Net, C#, or whatever other non-database programming language have a tendency to approach problems in an iterative approach. That is, they like to use loops to do a repetetive process. BUT in SQL Server, it is FAR more efficient to use a set-based approach where you can work with multiple records in one shot.

OK, here's an oversimplified example of what I fear will be coming... Suppose your business sells nuts & bolts, and you have decided that you need to raise prices across the board by 10%. The iterative approach, which would be the tendency for your .Net programmer, would be to select all of the rows to be updated, and loop through them one by one, calculating the new price for the row, updating the price, and writing the updated row back to the database. VERY SLOW! Especially when compared to the single UPDATE statement that the set-based developer would use. Even without the .Net languages being introduced, I have seen a lot of people try the iterative approach because they just didn't know better. (Hint: if your stored procedure uses a cursor, you have gone iterative.)

I'm not saying the .Net integration is a bad thing, per se. In fact, I'm looking forward to the better error trapping (try...catch) that will be coming along with it. But just beware that you don't get caught up destroying the performance of your database just because you can now use the language you are used to. Pay attention to the approach (iterative or set-based), and use whichever one is most appropriate for the task.

Legacy Comments


Brett 2003-10-01	Worried About Yukon - Iterative vs. Set Based But Mark? What if I want to find the 300 rows in the middle of my Data..a very common request... I just don't know why.... Have you seen my salt shaker?

Tara 2003-10-01	re: Worried About Yukon - Iterative vs. Set Based Oh Brett, you're too funny.

AjarnMark 2003-10-01	re: Worried About Yukon - Iterative vs. Set Based Well, Brett, in a very encouraging voice I'd say, "Gosh, that's a great question! Why don't you go post it on SQLTeam and see what the experts have to say." :) And try this query: SELECT @Shaker = CASE WHEN SUBSTRING(Region.RegionDescription, 1, 10) = 'Maragarita' THEN 'FOUND' WHEN SUBSTRING(Customer.LastName, 1, 7) = 'Buffett' THEN 'LOOKING' ELSE 'LOST' END FROM Customer LEFT JOIN Region on Customer.Region = Region.RegionID

Abdu 2003-10-03	re: Worried About Yukon - Iterative vs. Set Based Make the tools available and let the developer worry on which tool to use best. You don't stop making matches because some idiots or kids might hurt themselves. Many sql newbies still loop using cursors so looping using another language is still the same pitfall. Just a different syntax. Also many people think that when you can create stored procedure in a different languages, you can go without SQL. It just means SQL is mixed with another language. You still have to do a SELECT to get rows and so on. Abdu

AjarnMark 2003-10-03	re: Worried About Yukon - Iterative vs. Set Based Abdu, I agree they should still make the tools available. I just wanted to highlight one pitfall and help people to stop and think about what they're doing. You're right that some developers still use cursors in T-SQL. We see that a lot in the posts on SQLTeam. And there are even a couple of articles (on SQLTeam) putting forth reasonable arguments in favor of cursors in certain circumstances. But most of the time we help the user rewrite their lengthy cursor process into a much shorter, much faster set-based command. Maybe I've just spent too much time cleaning up other people's junk code. On the other hand, they pay me well to do it, and everyone else's junk makes me look like a genius. Bring on the .Net!