Time Bomb Coding

Question

Post reply

Time Bomb Coding

David.Poole

SSC Guru

Points: 76035
More actions
February 9, 2010 at 9:27 pm

#100068

Comments posted to this topic are about the item Time Bomb Coding
LinkedIn Profile

Viewing 15 posts - 1 through 15 (of 73 total)

You must be logged in to reply to this topic. Login to reply

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 1

Excellent Article !! covered almost all points for time bomb coding.

--Divya

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

thanks David :-)... really good article... i m facing the archiving issue now...

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 3

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

😛

Ryan C. Price Ten Centuries Points: 1184 More actions · Answer 4

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 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 6

Ritesh Medhe

SSCommitted

Points: 1941

February 10, 2010 at 12:38 am

#1116600

lovely article thx

dungimon Valued Member Points: 71 More actions · Answer 7

Brilliant article! opened my eyes to a few practices I hadn't realised were soo poor.

nicholasw SSC Rookie Points: 37 More actions · Answer 8

Instead of:

IF EXISTS(SELECT 1 FROM ......WHERE....)

Would the following be quicker still?

IF EXISTS(SELECT TOP 1 1 FROM ......WHERE....)

Ryan C. Price Ten Centuries Points: 1184 More actions · Answer 9

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 ?

CraigIW SSCrazy Points: 2301 More actions · Answer 10

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".

peter-757102 SSCertifiable Points: 6877 More actions · Answer 11

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.

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 12

Excellent article... Especially the list!!!!

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 13

Excellent article... Especially the list!!!!

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

peter-757102 SSCertifiable Points: 6877 More actions · Answer 14

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.