Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Slow view needs optimization help

Slow view needs optimization help

From: Jon Davis <jon_at_REMOVE.ME.jondavis.net>
Date: Tue, 31 May 2005 06:45:27 -0700
Message-ID: <XTZme.159$Zt.88@okepread05> 





This might be too much hassle for most of you but someone might be up for a 
good challenge. I need to figure out how to optimize this query (at bottom 
of this post) for Oracle 9i.



I have a table of sales figures (C_ACC_SALES) based on the following 
grouping:



    Month (1 - 12), Year (2004, 2005), AccountID, Product, Catalog_Num, 
Distributor, Quantity, Amount (in dollars)



As you can see, the level of granularity is MONTH.



Based on this data, I need a View to display the current and previous years 
of data in the row format (plus some columns):



     Account  |  Product  |  Catalog_Num  |  Distributor |  JAN_QTY  | 
FEB_QTY | ... | DEC_QTY | YEAR_QTY (sum of months) | JAN_AMT  | ... | 
DEC_AMT |  YEAR_AMT (sum of months)




I've created the query at the bottom of this post which takes about 5 
minutes(!!) for Oracle to execute. This is awful.



Originally this executed in a couple seconds without problems. However, I 
originally based it off the month of January (joining the other months to 
January), but I soon realized that January could be NULL (for values of 
zero). So the first major slowdown was found in making the base table upon 
which all months would be joined to be a derived table of:




(SELECT EXTRACT(YEAR FROM SYSDATE) YR FROM C_ACC_SALES


UNION



SELECT EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) YR

FROM sysdba.C_ACC_SALES)




I'm using the above to join the month values with the current year and last 
year, and any given month might have nulls in it so I can't base it off a 
particular month, and month data the level of granularity that we currently 
store. I don't need the above to be "FROM C_ACC_SALES" but it wouldn't 
execute without a FROM clause.



The resulting parse time lapse jumped from a few short seconds all the way 
up to one minute and thirty-five seconds (for still only a few hundred 
records).



The second major slowdown was that I also needed to join area information 
stored in the account's manager's custom text field. The "USERPROFILE" 
elements in the query below, coupled with the above-mentioned slowdowns, has 
resulted in four and a half to five minutes execution time (for only a few 
hundred records). The reason for this is because I am using more derived 
tables to extract the custom text field value only if another custom text 
field value matches up.....



LEFT OUTER JOIN



(SELECT USERPROFILE.USERID, AreaData.Title, AreaData.AreaID, 

AreaData.AreaType, TerritoryData.TerritoryID, DistrictData.DistrictID,
RegionData.RegionID


FROM USERPROFILE LEFT OUTER JOIN



(SELECT AREAUSERPROFILE.USERID UserID, USERINFO.TITLE Title, 

AREAUSERPROFILE.USERTEXT1 AreaID,


AREAUSERPROFILE.USERTEXT3 AreaType


FROM USERPROFILE AREAUSERPROFILE INNER JOIN

USERINFO ON AREAUSERPROFILE.USERID = USERINFO.USERID) AreaData ON
USERPROFILE.USERID = AreaData.UserID LEFT OUTER JOIN


(SELECT USERID, USERTEXT1 TerritoryID



FROM USERPROFILE SrcTerritoryData


WHERE (UPPER(USERTEXT3) = 'TERRITORY')) TerritoryData ON USERPROFILE.USERID 
= TerritoryData.USERID LEFT OUTER JOIN


(SELECT USERID, USERTEXT1 DistrictID



FROM USERPROFILE SrcDistrictData


WHERE (UPPER(USERTEXT3) = 'DISTRICT')) DistrictData ON USERPROFILE.USERID = 
DistrictData.USERID LEFT OUTER JOIN


(SELECT USERID, USERTEXT1 RegionID



FROM USERPROFILE SrcRegionData


WHERE (UPPER(USERTEXT3) = 'REGION')) RegionData ON USERPROFILE.USERID = 
RegionData.USERID) UserAreaData ON


ACCOUNT.ACCOUNTMANAGERID = UserAreaData.USERID





SQL Server 2000 handles the entire view below (with EXTRACT() changed to 
YEAR(), etc.) without so much as a hiccup. The entire view below (in 
Transact-SQL format) executes in about one or two seconds on my laptop using 
SQL Server 2000.



If anyone could help me optimize this for Oracle 9i I'd appreciate it!





CREATE OR REPLACE VIEW C_SALES_VIEW AS



SELECT C_ACC_SALES_JAN.ACCOUNTID, ACCOUNT.ACCOUNT, UserAreaData.AreaID, 
UserAreaData.AreaType, UserAreaData.TerritoryID,
UserAreaData.DistrictID, UserAreaData.RegionID, C_ACC_SALES_JAN.PRODUCT, 
C_ACC_SALES_JAN.CATALOG_NUM,



C_ACC_SALES_JAN.DISTRIBUTOR, C_ACC_SALES_JAN.YR, C_ACC_SALES_JAN.QUANTITY 

JAN_QTY, C_ACC_SALES_FEB.QUANTITY FEB_QTY,


C_ACC_SALES_MAR.QUANTITY MAR_QTY, C_ACC_SALES_APR.QUANTITY APR_QTY, 
C_ACC_SALES_MAY.QUANTITY MAY_QTY,
C_ACC_SALES_JUN.QUANTITY JUN_QTY, C_ACC_SALES_JUL.QUANTITY JUL_QTY, 
C_ACC_SALES_AUG.QUANTITY AUG_QTY,
C_ACC_SALES_SEP.QUANTITY SEP_QTY, C_ACC_SALES_OCT.QUANTITY OCT_QTY, 
C_ACC_SALES_NOV.QUANTITY NOV_QTY,
C_ACC_SALES_DEC.QUANTITY DEC_QTY,
C_ACC_SALES_JAN.QUANTITY + C_ACC_SALES_FEB.QUANTITY + 
C_ACC_SALES_MAR.QUANTITY + C_ACC_SALES_APR.QUANTITY + 
C_ACC_SALES_MAY.QUANTITY




+ C_ACC_SALES_JUN.QUANTITY + C_ACC_SALES_JUL.QUANTITY + 

C_ACC_SALES_AUG.QUANTITY + C_ACC_SALES_SEP.QUANTITY + 

C_ACC_SALES_OCT.QUANTITY



+ C_ACC_SALES_NOV.QUANTITY + C_ACC_SALES_DEC.QUANTITY YR_QTY, 


C_ACC_SALES_JAN.SALES_AMOUNT JAN_AMT,
C_ACC_SALES_FEB.SALES_AMOUNT FEB_AMT, C_ACC_SALES_MAR.SALES_AMOUNT MAR_AMT,
C_ACC_SALES_APR.SALES_AMOUNT APR_AMT, C_ACC_SALES_MAY.SALES_AMOUNT MAY_AMT,
C_ACC_SALES_JUN.SALES_AMOUNT JUN_AMT, C_ACC_SALES_JUL.SALES_AMOUNT JUL_AMT,
C_ACC_SALES_AUG.SALES_AMOUNT AUG_AMT, C_ACC_SALES_SEP.SALES_AMOUNT SEP_AMT,
C_ACC_SALES_OCT.SALES_AMOUNT OCT_AMT, C_ACC_SALES_NOV.SALES_AMOUNT NOV_AMT,
C_ACC_SALES_DEC.SALES_AMOUNT DEC_AMT,
C_ACC_SALES_JAN.SALES_AMOUNT + C_ACC_SALES_FEB.SALES_AMOUNT + 
C_ACC_SALES_MAR.SALES_AMOUNT + C_ACC_SALES_APR.SALES_AMOUNT


+ C_ACC_SALES_MAY.SALES_AMOUNT + C_ACC_SALES_JUN.SALES_AMOUNT + 

C_ACC_SALES_JUL.SALES_AMOUNT + C_ACC_SALES_AUG.SALES_AMOUNT

+ C_ACC_SALES_SEP.SALES_AMOUNT + C_ACC_SALES_OCT.SALES_AMOUNT + 

C_ACC_SALES_NOV.SALES_AMOUNT + C_ACC_SALES_DEC.SALES_AMOUNT

YR_AMT, C_ACC_SALES_JAN.CREATEDATE, C_ACC_SALES_JAN.CREATEUSER, 

C_ACC_SALES_JAN.MODIFYDATE,



C_ACC_SALES_JAN.MODIFYUSER



FROM (SELECT EXTRACT(YEAR FROM SYSDATE) YR FROM C_ACC_SALES

UNION



SELECT EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) YR

FROM sysdba.C_ACC_SALES) C_ACC_SALES_YEAR LEFT OUTER JOIN C_ACC_SALES 
C_ACC_SALES_JAN ON C_ACC_SALES_YEAR.YR = C_ACC_SALES_JAN.YR AND

C_ACC_SALES_JAN.MO = 1 LEFT OUTER JOIN



sysdba.C_ACC_SALES C_ACC_SALES_FEB ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_FEB.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_FEB.YR AND C_ACC_SALES_FEB.MO = 2 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_FEB.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_FEB.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_FEB.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_MAR ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_MAR.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_MAR.YR AND C_ACC_SALES_MAR.MO = 3 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_MAR.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_MAR.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_MAR.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_APR ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_APR.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_APR.YR AND C_ACC_SALES_APR.MO = 4 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_APR.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_APR.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_APR.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_MAY ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_MAY.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_MAY.YR AND C_ACC_SALES_MAY.MO = 5 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_MAY.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_MAY.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_MAY.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_JUN ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_JUN.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_JUN.YR AND C_ACC_SALES_JUN.MO = 6 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_JUN.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_JUN.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_JUN.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_JUL ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_JUL.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_JUL.YR AND C_ACC_SALES_JUL.MO = 7 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_JUL.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_JUL.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_JUL.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_AUG ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_AUG.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_AUG.YR AND C_ACC_SALES_AUG.MO = 8 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_AUG.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_AUG.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_AUG.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_SEP ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_SEP.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_SEP.YR AND C_ACC_SALES_SEP.MO = 9 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_SEP.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_SEP.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_SEP.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_OCT ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_OCT.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_OCT.YR AND C_ACC_SALES_OCT.MO = 10 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_OCT.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_OCT.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_OCT.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_NOV ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_NOV.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_NOV.YR AND C_ACC_SALES_NOV.MO = 11 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_NOV.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_NOV.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_NOV.DISTRIBUTOR LEFT OUTER JOIN


sysdba.C_ACC_SALES C_ACC_SALES_DEC ON C_ACC_SALES_JAN.ACCOUNTID = 

C_ACC_SALES_DEC.ACCOUNTID AND
C_ACC_SALES_JAN.YR = C_ACC_SALES_DEC.YR AND C_ACC_SALES_DEC.MO = 12 AND
C_ACC_SALES_JAN.PRODUCT = C_ACC_SALES_DEC.PRODUCT AND 
C_ACC_SALES_JAN.CATALOG_NUM = C_ACC_SALES_DEC.CATALOG_NUM AND
C_ACC_SALES_JAN.DISTRIBUTOR = C_ACC_SALES_DEC.DISTRIBUTOR LEFT OUTER JOIN


sysdba.ACCOUNT ON C_ACC_SALES_JAN.ACCOUNTID = sysdba.ACCOUNT.ACCOUNTID LEFT 
OUTER JOIN



sysdba.USERPROFILE ON sysdba.ACCOUNT.ACCOUNTMANAGERID = 
sysdba.USERPROFILE.USERID LEFT OUTER JOIN


(SELECT USERPROFILE.USERID, AreaData.Title, AreaData.AreaID, 

AreaData.AreaType, TerritoryData.TerritoryID, DistrictData.DistrictID,
RegionData.RegionID


FROM USERPROFILE LEFT OUTER JOIN



(SELECT AREAUSERPROFILE.USERID UserID, USERINFO.TITLE Title, 

AREAUSERPROFILE.USERTEXT1 AreaID,


AREAUSERPROFILE.USERTEXT3 AreaType


FROM USERPROFILE AREAUSERPROFILE INNER JOIN

USERINFO ON AREAUSERPROFILE.USERID = USERINFO.USERID) AreaData ON
USERPROFILE.USERID = AreaData.UserID LEFT OUTER JOIN


(SELECT USERID, USERTEXT1 TerritoryID



FROM USERPROFILE SrcTerritoryData


WHERE (UPPER(USERTEXT3) = 'TERRITORY')) TerritoryData ON USERPROFILE.USERID 
= TerritoryData.USERID LEFT OUTER JOIN


(SELECT USERID, USERTEXT1 DistrictID



FROM USERPROFILE SrcDistrictData


WHERE (UPPER(USERTEXT3) = 'DISTRICT')) DistrictData ON USERPROFILE.USERID = 
DistrictData.USERID LEFT OUTER JOIN


(SELECT USERID, USERTEXT1 RegionID



FROM USERPROFILE SrcRegionData


WHERE (UPPER(USERTEXT3) = 'REGION')) RegionData ON USERPROFILE.USERID = 
RegionData.USERID) UserAreaData ON


ACCOUNT.ACCOUNTMANAGERID = UserAreaData.USERID
ORDER BY C_ACC_SALES_JAN.ACCOUNTID, C_ACC_SALES_JAN.YR DESC, 

C_ACC_SALES_JAN.PRODUCT, C_ACC_SALES_JAN.DISTRIBUTOR,

C_ACC_SALES_JAN.CATALOG_NUM 

Received on Tue May 31 2005 - 08:45:27 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US