DBA HELP: Performane Tune SELECT, SUM, & CASE

[gilgantic]

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

[Serge Rielau]

gilgantic wrote:

1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both
have a value. == i.e. FIELD_1 has 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) == This can be done "later" 3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.

SELECT count1, count2,
count1 + count2 as total-count,
none_count FROM
(SELECT
SUM(CASE
WHEN (FIELD_1 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 NULL)
THEN 1
ELSE 0
END) AS None_Count,
FROM TABLE_1) as t

I'm wondering what you mean by slow.
You should end up with a table scan an an aggregation. Not much choice.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[DA Morgan]

gilgantic wrote:

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.

Lets see ... you posted this to pretty much every usenet group
you could spell. Generic "databases" , "theory", "IBM-DB2", "Oracle".

When you decide which product you are using
And you provide a version number
And you provide an SLA
And you provide, assuming Oracle, an explain plan,
Help may be possible.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)

[DA Morgan]

Serge Rielau wrote:

gilgantic wrote:

Cheers
Serge

I found this in the Sybase group too. I think the OP has
some 'spainin' to do.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)