February 7, 2006 at 12:25 pm
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
Chaz
February 7, 2006 at 2:50 pm
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.
February 8, 2006 at 2:39 am
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
February 8, 2006 at 5:09 am
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