x
Solved

Posted on 2013-10-31
Medium Priority
351 Views
This query is having performance issues..

was wondering if anyone knows a way to simplify it

``````WITH DESCR AS (SELECT
RMCUSP.CUSREGNUM AS CHAIN,
RMCODP.CODLNGDES AS CHAIN_DESCRIPTION
FROM
RMCODP,
RMCUSP
WHERE CUSREGNUM = CODKEYNUM AND CODCODTYP='RN'
GROUP BY CUSREGNUM, CODLNGDES),

BRANDS AS (SELECT  DISTINCT
RMITMP.ITMITMBRD AS BRAND_NO,
RMCODP.CODLNGDES AS BRAND
FROM
RMITMP,
RMCODP
WHERE ITMITMBRD = CODKEYNUM AND CODCODTYP = 'IB'),

ORDERS AS (

SELECT
RMCUSP.CUSREGNUM AS CHAIN,
RMCODP.CODLNGDES AS CHAIN_DESCRIPTION, BRAND,
round((Sum(RMORDP.ORDORDQTY/RMDCIP.DCICASFAC)),2) AS ORDERS,
RMORHP.ORHDELDTE AS ORD_DATE
FROM
RMCODP,
RMCUSP,
RMITMP,
RMORDP,
RMORHP,
RMDCIP,
BRANDS
WHERE CODKEYNUM = CUSREGNUM
AND ORDITMNUM = ITMITMNUM
AND CUSCUSNUM = ORHCUSNUM
AND CUSCUSCHN = ORHCUSCHN
AND ORHORDNUM = ORDORDNUM
AND DCIDSTCTR = ORHDSTCTR
AND DCIITMNUM = ORDITMNUM
AND BRAND_NO = ITMITMBRD
AND CODCODTYP='RN'
AND CUSREGNUM <> 185
AND ORDITMNUM NOT IN(98,99, 994)
AND ORHDELDTE BETWEEN 20131013 AND 20131019
AND DCICASFAC<>0
GROUP BY RMCUSP.CUSREGNUM, RMCODP.CODLNGDES, RMCODP.CODCODTYP,RMORHP.ORHDELDTE,BRAND
UNION ALL
SELECT
CUSREGNUM AS CHAIN,
CODLNGDES,BRAND,
round((Sum(RMORDP.ORDORDQTY/RMDCIP.DCICASFAC)),2) AS ORDERS , RMORHP.ORHDELDTE AS ORD_DATE
FROM RMCODP, RMCUSP, RMITMP, RMORDP, RMORHP,RMDCIP ,BRANDS
WHERE CODKEYNUM = CUSREGNUM
AND ORDITMNUM = ITMITMNUM
AND CUSCUSNUM = ORHCUSNUM
AND CUSCUSCHN = ORHCUSCHN
AND ORHORDNUM = ORDORDNUM
AND DCIDSTCTR = ORHDSTCTR
AND DCIITMNUM = ORDITMNUM
AND BRAND_NO = ITMITMBRD
AND CODCODTYP = 'RN'
AND ITMPRDGRP <> 480
AND CUSREGNUM = 185
AND ORHDELDTE BETWEEN 20131013 AND 20131019
AND DCICASFAC<>0
AND ORDITMNUM NOT IN(98,99, 994)
GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP, RMORHP.ORHDELDTE, BRAND),

SALES AS (     SELECT CUSREGNUM AS CHAIN,
CODLNGDES, BRAND, RMSLDP.SLDDELDTE AS SALE_DATE ,
round((Sum(RMSLDP.SLDTRNQTY/RMDCIP.DCICASFAC)),2) AS SALES
FROM
RMCODP,
RMCUSP,
RMITMP,
RMSLDP,
RMDCIP,
BRANDS
WHERE SLDITMNUM = DCIITMNUM
AND CODKEYNUM = CUSREGNUM
AND SLDITMNUM = ITMITMNUM
AND CUSCUSNUM = SLDCUSNUM
AND CUSCUSCHN = SLDCUSCHN
AND CUSDFTDCN = DCIDSTCTR
AND BRAND_NO = ITMITMBRD
AND CODCODTYP='RN'
AND (CUSREGNUM <> 185)
AND SLDITMNUM NOT IN(98,99, 994)
AND SLDDELDTE BETWEEN 20131013 AND 20131019
AND DCICASFAC <> 0
GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP , RMSLDP.SLDDELDTE ,BRAND

UNION ALL

SELECT CUSREGNUM AS CHAIN,
CODLNGDES, BRAND, RMSLDP.SLDDELDTE AS SALE_DATE,
round((Sum(RMSLDP.SLDTRNQTY/RMDCIP.DCICASFAC)),2) AS SALES
FROM RMCODP, RMCUSP, RMITMP, RMSLDP , RMDCIP , BRANDS
WHERE SLDITMNUM = DCIITMNUM
AND CODKEYNUM = CUSREGNUM
AND SLDITMNUM = ITMITMNUM
AND CUSCUSNUM = SLDCUSNUM
AND CUSCUSCHN = SLDCUSCHN
AND CUSDFTDCN = DCIDSTCTR
AND BRAND_NO = ITMITMBRD
AND CODCODTYP = 'RN'
AND ITMPRDGRP <> 480
AND CUSREGNUM = 185
AND SLDDELDTE BETWEEN 20131013 AND 20131019
AND DCICASFAC <> 0
AND SLDITMNUM NOT IN(98,99, 994)
GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP, RMSLDP.SLDDELDTE, BRAND)

select descr.chain,
descr.chain_description,
orders.ord_date,
sales.sale_date,
orders.orders,
sales.sales,
sales.brand
from (descr LEFT OUTER join orders on orders.chain = descr.chain) LEFT OUTER join sales on sales.chain = descr.chain
where ord_date = sale_date and orders.brand = sales.brand

From: John Terrero [mailto:johnt@dorasnaturals.com]
Sent: Thursday, October 31, 2013 1:22 PM
To: julios@dorasnaturals.com
Subject: try this

SELECT
chain,
chain_description,
round(sum(orders)) orders,
round(sum(sales)) sales,
round(sum(orders - sales)*-1) as difference
FROM casediflv0
WHERE ord_date BETWEEN 20131013 AND 20131019
GROUP BY
chain, chain_description
ORDER BY chain

``````
0
Question by:FutureDBA-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
1 Comment

LVL 25

Accepted Solution

chaau earned 2000 total points
ID: 39615691
A side note: it may not improve performance, but the syntax like this:
``````FROM
RMCODP,
RMCUSP,
RMITMP,
RMORDP,
RMORHP,
RMDCIP,
BRANDS
``````
is now considered not standard. To make it ANSI-compliant you should switch to INNER JOIN. As I said it is a syntax change only and may not improve performance.

To check the performance please run this query with EXPLAIN PLAN. Then check what is taking too much time. You may need to add some indices to tables
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…