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.
posted @ Sunday, September 28, 2003 10:13 PM