More Than One Way
-
In SQL Server there are a number of ways to solve most problems. We see this everyday with various reports and business needs presented to us from sources. Different developers have different techniques and methods that they like and use to meet their needs.
But there are often better ways of solving a problem than your first instinct, even for experienced DBAs. There's been a recent set of blog postings showing the evolution of a solution that gets better and better.
It started with a solution for running sums by Adam Machanic. Then a better solution without a cursor and finally still one more using SQL CLR.
Not necessarily every single query you write needs to be refactored, rewritten, made better. The point to this is that you can do better and if there are critical items, very important queries or processes, then you should spend some time or seek other advice to see if you can improve performance. Lowering the time for a quarterly query from 1 hour to 2 minutes is great, but if you can speed one that runs every 5 minutes to 4 sec from 14, you have probably made a much bigger impact.
Don't get too locked into one way of doing things. I hate temp tables in code, but I do recognize that they can outperform other solutions in place and make my application run much better.
After all, that's the idea.
Steve Jones
-
Great editorial (again), Steve!
It was cool of Adam to share his code and thinking as it evolved - thanks Adam!
Way too often, I find myself dealing with someone's perception of "the rules" of SQL Server development. My experience has shown there are a number of factors which influence SQL Server performance and hence, best practices. It's just not one of those generic environments that lives in a vacuum. Everything affects performance.
I've seen experienced DBAs (different "flavors" of DBAs...) clash over this - sometimes to the extreme. I know of one situation where a System Admin DBA so disagreed with the performance tuning methodology of an Application DBA, the System guy regularly deleted table indexes applied by the App guy!
When it comes to performance tuning, design, development, and troubleshooting SQL Server I agree with the First Commandment of Engineering: If it works, it is good.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
-
"If it works, it is good" - I like that a lot!
-
I could be missing something but I'm a little nervous about Thomson's approach, it seems to be depending on a specific sequence of operation which is not really guaranteed. While a cursor is not preferred because it is not a set based approach, at least its sequence is guaranteed.
...
-- FORTRAN manual for Xerox Computers --
-
I should agree that temp tables and cursors are not the most elegant constructs, but this is exactly like it used to be with the goto operator in many languages - it used to be a taboo in structured programming, however it often worked much better than anything else, so, sure - whatever works
-
Steve - could you elaborate on your "I hate temp tables" statement? ... is it because of problems with locking, or are there other reasons?
Thanks!
SteveR
-
Mostly a locking issue. It's better than before and I haven't done any large scale development since early ss2k, so I might revise. Used to see too many "intermediate" results dropped into temp tables and either tempdb locking while the table was built (select into #xx) or growth in tempdb beyond reasonable.
Mostly I've seen the vast majority of my queries solved without cursors or temp tables. I think they should be avoided, at least that's always been my experience, but judiciously used, I do know they can be a tool.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply