Re: No distinct in a select into stement ?

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@xxxxxxxxxxxxx> wrote in message
news:1145969845.021110.74070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Scalar Functions in Group By Clause returning invalid values
    ... Hi Mike, ... Are you aware that the function will be executed twice for each row in ... I'm afraid that your query won't scale well... ... SELECT COALESCE AS ClientType, ...
    (microsoft.public.sqlserver.programming)
  • RE: Conditional Join?
    ... Note that this will always exclude records where c_language_skills is null. ... > Is it possible to use a CASE statement or maybe COALESCE? ... > the language skills. ... If I used Coalesce and had Null would it query all ...
    (microsoft.public.sqlserver.programming)
  • Conditional Join?
    ... I currently have a stored proc that is dynamic. ... variety of variables passed in. ... Is it possible to use a CASE statement or maybe COALESCE? ... The join is optional because the user didn't need to query, in this case, on ...
    (microsoft.public.sqlserver.programming)
  • Re: WITH CUBE, Null and Empty string
    ... Go the the FROM clause and create a derived table with a COALESCE ... (foobar, '') ... then do the rest of the query. ...
    (microsoft.public.sqlserver.programming)
  • Re: No distinct in a select into stement ?
    ... WHERE COALESCE (title1, title2, title3) IS NOT NULL ...
    (comp.databases.ms-sqlserver)