all groups > sql server (alternate) > april 2005 >
You're in the

sql server (alternate)

group:

DBA HELP: Performane Tune SELECT, SUM, & CASE


DBA HELP: Performane Tune SELECT, SUM, & CASE gilgantic
4/20/2005 4:07:05 PM
sql server (alternate): HELP!!!

I am trying to fine tune or rewrite my SELECT statement which has a
combination of SUM and CASE statements. The values are accurate, but
the query is slow.
BUSINESS RULE
=============
1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both
have a value.
2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.
4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse the
SQL from both Count1 and Count2)
3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.


SQL Code
========
SELECT
SUM(CASE
WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR (FIELD_1 IS NOT
NULL AND FIELD_2 IS NOT NULL))
THEN 1
ELSE 0
END) AS Count1 ,
SUM(CASE
WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
THEN 1
ELSE 0
END) AS Count2,
SUM(CASE
WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
THEN 1
ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR FIELD_1
IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)
END) AS Total_Count,
SUM(CASE
WHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)
THEN 1
ELSE 0
END) AS None_Count,
FROM
TABLE_1
Re: DBA HELP: Performane Tune SELECT, SUM, & CASE Anthony Mandic
4/21/2005 12:00:00 AM
[quoted text, click to view]

I can see a few problems here.

[quoted text, click to view]

Since this case looks at FIELD_1 being not null regardless of
the state of FIELD_2, why not just test FIELD_1?

[quoted text, click to view]

Since this is the sum of Count1 and Count2, why not
just present Total_Count as Count1 + Count2?

[quoted text, click to view]

In all cases, the "ELSE 0" is redundant. However, since
you posted to newsgroups for 3 separate products, its not
clear which one you're using. I'd be more inclinded to
rewrite this using charactistic functions.

AddThis Social Bookmark Button