February 9, 2010 at 9:27 pm
Comments posted to this topic are about the item Time Bomb Coding
February 9, 2010 at 11:10 pm
Excellent Article !! covered almost all points for time bomb coding.
--Divya
February 9, 2010 at 11:25 pm
thanks David :-)... really good article... i m facing the archiving issue now...
February 9, 2010 at 11:40 pm
If you are really fighting for microseconds
If Exists ( Select Null from bigtable Where ...) also is a good test with minimal Overhead instead of Select 1
Begin
.... fancy code
End
But I still find the best time bombs are what I refer to as 'Surrogate Key' processing where software allows customisations to things like account codes and part numbers and financial processing is carried on a conglomerate of individual elements with no key contraints.
Thanks David, a timely article to point out to my new Developers.
CodeOn
😛
February 10, 2010 at 12:21 am
Not that I want to encourage bad habits, but I seem to recall a discussion (I thought it was here @ SQL Server Central, but I can't find it) regarding COUNT(*) vs EXISTS, and tests were carried out (in SQL 2005 or 2008) where the compiler was smart enough to recognize that a programmer really intended to use EXISTS not COUNT(*), and so actually generated the same execution plan for both - the SQL Server engineering team compensating for the huge amount of abuse of COUNT(*) that goes on.
...But as I said, that's still no excuse for bad programming - the right tool for the job is EXISTS
EDIT: :blush: Forgot to say 'thanks for the cool list of things to look out for' - so, David, thanks for the cool list of things to look out for 😀
February 10, 2010 at 12:32 am
Nice compilation of some of the practices that make for poorly performing queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2010 at 12:38 am
lovely article thx
February 10, 2010 at 1:20 am
Brilliant article! opened my eyes to a few practices I hadn't realised were soo poor.
February 10, 2010 at 1:27 am
Instead of:
IF EXISTS(SELECT 1 FROM ......WHERE....)
Would the following be quicker still?
IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)
February 10, 2010 at 1:40 am
nicholasw (2/10/2010)
Instead of:IF EXISTS(SELECT 1 FROM ......WHERE....)
Would the following be quicker still?
IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)
No, and it doesn't really make sense - TOP implies 'importance' i.e. TOP 1 means you're looking for the '1' most 'important' row. When using 'EXISTS', there is no concept of importance - there is data or there isn't.
Also, doesn't TOP get lonely without an 'ORDER BY' clause ?
February 10, 2010 at 1:56 am
nicholasw (2/10/2010)
Instead of:IF EXISTS(SELECT 1 FROM ......WHERE....)
Would the following be quicker still?
IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)
No, as the "top 1" is only done after the "select 1 from".
February 10, 2010 at 3:57 am
Outstanding article...clear in meaning, no clutter or distractions, straight to the point.
It is also enlightening to see some of the beginner mistakes I made so many years ago when I tried to use the relational database as storage for a perfect object model and used only a few generic tables holding all the data. Your article correctly describes the problems it had when soon after it wend into production...shockingly quickly I might add, even on brutal hardware. That everything was developed on a memory constraint laptop did not help either ofcourse, always make sure to also test on similar hardware it will eventually be running on. A memory constraint laptop running 5 applications next to SQL server will not correctly separate bad code/models from good code/models. It is like trying to find out which athlete can run faster and should be send to the olympic games, with the constraint that they all have to tug 800kg of metal behind them to find it out...uphill.
As for the bitfield problem (and similar ones), filtered indexes (SQL 2008) can be a big help here.
February 10, 2010 at 4:02 am
February 10, 2010 at 4:02 am
February 10, 2010 at 4:04 am
CraigIW (2/10/2010)
nicholasw (2/10/2010)
Instead of:IF EXISTS(SELECT 1 FROM ......WHERE....)
Would the following be quicker still?
IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)
No, as the "top 1" is only done after the "select 1 from".
Funny enaugh, it is not always that simple. I seen cases where a:
select top 1 max(id)
outperformed
select max(id)
many times over, and I suspect top sometimes acts as a undocumented hint. I got some testing to do on it still, and if it pans out it might also be a trick to control table variables better by using top to provide an estimate other then the constant 1 and thus get better query plans. It could also be just a side effect of the optimizer assigning costs to each operation and in rare cases come to a different plan due to a slightly higher ** estimate ** of the costs. In any case, it does not hurt to test both methods if you see a query perform worse then you anticipated.
Viewing 15 posts - 1 through 15 (of 73 total)
You must be logged in to reply to this topic. Login to reply