469,579 Members | 1,218 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

DBA HELP: Performane Tune SELECT, SUM, & CASE

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

Jul 23 '05 #1
1 4951
gilgantic wrote:

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.
I can see a few problems here.
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 ,
Since this case looks at FIELD_1 being not null regardless of
the state of FIELD_2, why not just test FIELD_1?
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,
Since this is the sum of Count1 and Count2, why not
just present Total_Count as Count1 + Count2?
SUM(CASE
WHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)
THEN 1
ELSE 0
END) AS None_Count,
FROM
TABLE_1


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.

-am 2005
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by gilgantic | last post: by
reply views Thread by ward | last post: by
23 posts views Thread by casper christensen | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.