Writing queries best practice

  • what is the best way to write this query and why?

    select *

      from Table01

     where substring(field01,1,3) = '254'

       and field02 is null

       and field03 not in (select field03 from Table02)

       and field03 not in (select field03 from Table03)

    order by field04,field05

    thanks


    Kindest Regards,

    Chaz

  • First try to avoid using the '*' in your select, wirte just the columns that you need.

    Second never use function in the left side of your conditions, becuase this avoid the use of indexes

    The last one, never use not in in your queries try to change it to something diferent.

     

  • I mostly agree with Marco. Here's a suggested rewrite

    SELECT Table01.field01, Table01.field02, Table01.field03, Table01.field04, Table01.field05

     FROM Table01 LEFT OUTER JOIN Table02 ON Table01.field03 = Table02.field03

     LEFT OUTER JOIN Table03 ON Table01.field03 = Table03.field03

     WHERE Table01.field01 LIKE '256%' AND Table02.field03 IS NULL AND Table03.field03 IS NULL

     ORDER BY Table01.field04, Table01.field05

    Regarding LEFT OUTER JOINS  or NOT IN, test your queries with both and check the execution plan. I have seen occations when a NOT IN is faster with fewer IOs than a LEFT JOIN.

    The LIKE is better than the substring because the first few letters are not wildcards, hence an index can be used if there is one on the field. If the substring had read substring(field01,2,5) then changing it to like '_256%' would not be faster.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Of course the correct answer is, no matter how you actually write the query, to lay it out in a readable way so that the poor bugger who has to debug your code in 12 months time has their job made a little bit easier.

    I tend to favour something like this:

    SELECT

      t01.field01,

      t01.field02,

      t01.field03,

      t01.field04,

      t01.field05

    FROM

      Table01 t01 LEFT JOIN Table02 t02 ON t01.field03 = t02.field03

      LEFT JOIN Table03 t03 ON t01.field03 = t03.field03

    WHERE

      t01.field01 LIKE '256%'

      AND t02.field03 IS NULL

      AND t03.field03 IS NULL

    ORDER BY

      t01.field04,

      t01.field05

    But then I suppose that's the subject of an entirely different thread :-).

Viewing 4 posts - 1 through 3 (of 3 total)

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