DBA HELP: Performane Tune SELECT, SUM, & CASE
From: gilgantic <gilgantic_at_gmail.com>
Date: 20 Apr 2005 16:06:42 -0700
Message-ID: <1114038402.110702.102330_at_l41g2000cwc.googlegroups.com>
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
SELECT
Date: 20 Apr 2005 16:06:42 -0700
Message-ID: <1114038402.110702.102330_at_l41g2000cwc.googlegroups.com>
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
- Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both have a value.
- Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.
- TotalCount = Count1 + Count2 -- (Below, basically had to reuse the SQL from both Count1 and Count2)
- 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_1Received on Thu Apr 21 2005 - 01:06:42 CEST