|
|
Posted by Tony Rogerson on 04/25/06 17:26
> Minor trick to make the code easier to read and maintain:
>
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL
and.... bang goes performance too other than a probable clustered index scan
/ table scan.
Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums....
There is a non-clustered index on nntp_author and there is non-clustered
index on author_id
-- Query 1
select count(*)
from mb_message_detail
where nntp_author is not null
or author_id is not null
-- Query 2
select count(*)
from mb_message_detail
where coalesce( nntp_author, author_id ) is not null
Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan
Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%
Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk......
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1145969845.021110.74070@t31g2000cwb.googlegroups.com...
> Minor trick to make the code easier to read and maintain:
>
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL
>
> Unfortunately these columns look like repeated and a really bad 1NF
> problem. I have the feeling that you might have wanted to use
> COALESCE() in the SELECT list to get a non-null title and non-null
> aggregation_title instead of this convoluted query.
>
Navigation:
[Reply to this message]
|