Time Bomb Coding

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

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

    --Divya

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

  • 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

    😛

  • 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 😀

  • 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

  • lovely article thx

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

  • Instead of:

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

    Would the following be quicker still?

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

  • 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 ?

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

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

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

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

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

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

  • 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 74 total)

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