Could someone help optimize this query?

Khy2shy 2014-06-26 15:54:20

SELECT
a.acct_type,
a.RECORD_CODE_CD,
SUM(a.amount) AS amt,
COUNT(a.amount) AS cnt
FROM
(
SELECT
tr.acct_curr_amount AS amount,
tt.RECORD_code_cd,
CASE
WHEN cc.AC_CLASS_CD IN (
'EMP-CDU',
'EMP-CHK',
'EMP-DDU',
'EMP-SVU',
'FID-CD',
'FID-CHK',
'FID-HCHK',
'FID-MMDA',
'FID-SV',
'IND-CD',
'IND-CHK',
'IND-FXCHK',
'IND-HCHK',
'IND-MMDA',
'IND-SV',
'NRA-CD',
'NRA-CHK',
'NRA-MMA',
'NRA-MMDA',
'NRA-SV',
'UKN-CDU',
'UKN-CHK',
'UKN-DDU',
'UKN-HCHK',
'UKN-MMA',
'UKN-SVU') THEN 'CON'
WHEN cc.AC_CLASS_CD IN (
'CORP-CD',
'CORP-CHK',
'CORP-DD',
'CORP-FXCHK',
'CORP-HCHK',
'CORP-MMA',
'CORP-MMDA',
'CORP-SV',
'FI-CD',
'FI-DD',
'FRB-CD',
'FRB-CHK',
'FRB-INTRN',
'FRB-MMA',
'FRB-MMDA',
'FRB-SV',
'GOV-CD',
'GOV-CHK',
'GOV-MMDA',
'GOV-SV',
'LLC-CD',
'LLC-CHK',
'LLC-FXCHK',
'LLC-HCHK',
'LLC-MMA',
'LLC-MMDA',
'LLC-SV',
'NGO-CD',
'NGO-CHK',
'NGO-MMA',
'NGO-MMDA',
'NGO-SV',
'PART-CD',
'PART-CHK',
'PART-FXCHK',
'PART-MMA',
'PART-MMDA',
'PART-SV',
'SP-CD',
'SP-CHK',
'SP-MMDA',
'SP-SV',
'TRUST-CD',
'TRUST-CHK',
'TRUST-HCHK',
'TRUST-MMDA',
'TRUST-SV',
'TRUST-UNKNOW') THEN 'BUS'
ELSE 'UKN'
END AS acct_type
FROM
RECORDS tr INNER JOIN
ACCOUNT ac ON tr.ACCOUNT_SK = ac.ENTITY_SK JOIN
RECORD_CODE tt ON tr.RECORD_CODE_SK = tt.ENTITY_SK JOIN
AC_CLASS cc ON ac.AC_CLASS_SK = cc.entity_sk
WHERE
tr.EXECUTION_LOCAL_DATE_TIME BETWEEN '2014-05-01 00:00:00.000' AND '2014-05-06 00:00:00.000'
) a
GROUP BY a.RECORD_CODE_CD, a.acct_type
Dan Holmes 2014-06-26 16:01:28
That is not enough information. No DDL. No plan. We will all need more than this SQL to suggest anything.
Patrick 2014-06-26 16:04:13
would it improve performance to create a table mapping the cc.AC_CLASS_CD values to the account_type you want and doing a join instead?