|
Home > Archive > Microsoft SQL Server forum > April 2005 > DBA HELP: Performane Tune SELECT, SUM, & CASE
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
DBA HELP: Performane Tune SELECT, SUM, & CASE
|
|
| gilgantic 2005-04-20, 8:23 pm |
| 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
| |
| Anthony Mandic 2005-04-21, 3:23 am |
| 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
|
|
|
|
|