It typically goes like this.
You started with procedural solutions because that's a 'natural' way of solving a problem. Then, you read articles and books and went to presentations, and everyone was preaching set-based solutions. You felt ashamed and started experimenting with set-based solutions. Your initial success plus even more bombardment from set-based solution best practice preachers led you to apply set-based solutions everywhere, or more modestly whenever possible. But as you ran into more real-world problems, you started to bump into cases that led you to question whether set-based solutions were really the panacea to cure the world's SQL performance problems.
Well, like life, there is a limit to everything. The limit to set-based solutions in the real world is the limit of the SQL optimizer. The set-based best practice preachers assume that the optimizer is all mighty. You don't need to tell it how, just tell it what you want in the form of a SQL query. It'll figure out how for you.
A case in point is the use of views. So ideally, a view encapsulates what you want in its resultset. And then you want something else, so you just use a view, or two, in another view, and the latter gives you what you want without your being bothered with the how question. As you heap more and more views on top of each other, you get great satisfaction with the quintessential set-based solution because at the top of the view you can just do a simple SELECT ABC FROM v_YourView, and let SQL Server figure out how. Simple and elegant, exactly wht you expect from a set-based solution!
Except that it may reach a point that the simple SELECT now runs for much longer than you can tolerate. So you start to investigate, and start to add a bit of procedural solution here and there by adding a hint here or a hint there. And then, you realize that a little bit of procedural nudge isn't enough to get you the performance, and start to break the seemingly simple/elegant but really complex query into smaller pieces and stitch them together with procedural code.
After all, some procedural solutions are not so bad!
Now, it's time for me to dodge the set-based solution crowd.
Source Click Here.
Post a Comment