Very Slow Running Business View Hierarchy

[horatiog]

Hi,

I have hit a rather tricky SQL problem I hope somebody can help me with.

I have financial data (Ledger Rollup By Month) held in a table, as follows:

Year
Plot
LedgerType
SubAccount
StartOfYearBalance
Month1Amount
Month2Amount
.
.
.
Month12Amount


I require to generate a business view in the following format:

Plot
Year
Month
CostType1ToDate
CostType2ToDate
.
.
.
CostType9ToDate


Each cost type column in this view is the sum of transactions for a ledger type and range of sub-account codes. What makes the situation even worse is that the sub-account column is alpha but the range is quasi-numeric. (E.g "2" through "29999999&quot



The way I have tried to solve this is by creating a heirarchy of 3 + 1 views.

The first uses a set of 12 select statements "Unioned" together to break the data down into rows by year and period and generates a true end of period figure by summing the SOY balance with all the period columns up to and including the period of the row being generated, viz:

Plot
Year
Period
LedgerType
SubAccount
Amount


The second view generates the various cost type columns by grouping and summing amounts based on the the ledger type and sub-account code ranges for each particular cost type. (There are 9 of them.). A union statement is used again:

SELECT
VIEW1_REF VIEW2_REF,
VIEW1_YEAR VIEW2_YEAR,
VIEW1_PERIOD VIEW2_PERIOD,
'Cost Type A' VIEW2_COSTTYPE,
SUM(VIEW1_AMOUNT) VIEW2_AMOUNT
FROM
VIEW1
WHERE
VIEW1_LEDGER = 'HA'
AND
VIEW1_SUBCODE BETWEEN '49' AND '49999999'
GROUP BY
VIEW1_REF,
VIEW1_YEAR,
VIEW1_PERIOD,
4
UNION
SELECT
VIEW1_REF VIEW2_REF,
VIEW1_YEAR VIEW2_YEAR,
VIEW1_PERIOD VIEW2_PERIOD,
'Cost Type B' VIEW2_COSTTYPE,
SUM(VIEW1_AMOUNT) VIEW2_AMOUNT
FROM
VIEW1
WHERE
VIEW1_LEDGER = 'AA'
AND
VIEW1_SUBCODE BETWEEN '2' AND '28999999'
GROUP BY
VIEW1_REF,
VIEW1_YEAR,
VIEW1_PERIOD,
4
UNION

etc.

The view created is:

Plot
Year
Period
CostType
Amount



The final business view then concatenates the various Cost Types by Plot, Year & Period, as described above. We must return a row for each Plot, Year and Period, even if there are no financial transactions. To do this, I have created another view, PLOTPERIODS, that cartesians the Plot Master Table and the Calendar Table. I have then joined this back to the Plot Master (from which various other columns on the final view are to be sourced) and also left outer joined it to each of the 9 Cost Type row sets (under table aliases). The code is as follows:

SELECT
PLOTPERIODS.REF VIEW_REF,
PLOTMASTER.TYPE VIEW_TYPE,
.
.
PLOTPERIODS.YEAR VIEW_YEAR,
PLOTPERIODS.PERIOD VIEW_PERIOD,
COSTTYPEA.VIEW2_AMOUNT VIEW_COSTA,
COSTTYPEB.VIEW2_AMOUNT VIEW_COSTB,
.
.
COSTTYPEI.VIEW2_AMOUNT VIEW_COSTI
FROM
PLOTMASTER,
PLOTPERIODS,
VIEW2 COSTTYPEA,
VIEW2 COSTTYPEB,
.
.
VIEW2 COSTTYPEI
WHERE
PLOTMASTER.REF = PLOTPERIODS.REF
AND
( COSTTYPEA.VIEW2_REF (+) = PLOTPERIODS.REF
AND
COSTTYPEA.VIEW2_YEAR (+) = PLOTPERIODS.YEAR
AND
COSTTYPEA.VIEW2_PERIOD (+) = PLOTPERIODS.PERIOD
AND
COSTTYPEA.VIEW2_COSTTYPE (+) = 'Cost Type A'
)
AND
( COSTTYPEB.VIEW2_REF (+) = PLOTPERIODS.REF
AND
COSTTYPEB.VIEW2_YEAR (+) = PLOTPERIODS.YEAR
AND
COSTTYPEB.VIEW2_PERIOD (+) = PLOTPERIODS.PERIOD
AND
COSTTYPEB.VIEW2_COSTTYPE (+) = 'Cost Type B'
)
.
.
AND
( COSTTYPEI.VIEW2_REF (+) = PLOTPERIODS.REF
AND
COSTTYPEI.VIEW2_YEAR (+) = PLOTPERIODS.YEAR
AND
COSTTYPEI.VIEW2_PERIOD (+) = PLOTPERIODS.PERIOD
AND
COSTTYPEI.VIEW2_COSTTYPE (+) = 'Cost Type I'
)

My problem is that the whole shebang (not unsurprisingly!) runs like a three-legged arthritic dog! It takes 3.5 minutes to return the result set for a given plot, and this with only 3 years on the calendar, 21,000 rows in the ledger roll-up table and 1300 plots. These numbers are due to get much bigger in the production system.

I am sure there is a way to achieve what I want in only one view and far more efficiently, but I'm under the gun to provide a solution and inspiration is not forthcoming. I know the original data structure is not the best but I have no control over this.

Thanks for any help that can be offered.

Regards,
Harry Haines
Report Analyst
Miller Group

 

[dnoeth]

Which RDBMS, your joins look like Oracle?

"The first uses a set of 12 select statements "Unioned" together to break the data down into rows by year and period and generates a true end of period figure by summing the SOY balance with all the period columns up to and including the period of the row being generated"

I'd use a cross join to a helper table containing (at least) consecutive numbers between 1 and 12:
select
r.Plot,
r.Year,
h.x Period,
r.LedgerType,
r.SubAccount,
StartOfYearBalance +
case h.x
when 1 then Month1Amount
when 2 then Month1Amount + Month2Amount
when 3 then Month1Amount + Month2Amount + Month3Amount
....
end Amount
from ledger_rollup r,
(select x from helper where x between 1 and 12) h

IIRC Oracle 8i+ supports CASE, but you can use DECODE, too.


"WHERE
VIEW1_LEDGER = 'HA'
AND
VIEW1_SUBCODE BETWEEN '49' AND '49999999'"

This sounds like CASE again,
SELECT
VIEW1_REF VIEW2_REF,
VIEW1_YEAR VIEW2_YEAR,
VIEW1_PERIOD VIEW2_PERIOD,
CASE
WHEN VIEW1_LEDGER = 'HA'
AND VIEW1_SUBCODE BETWEEN '49' AND '49999999'
THEN 'Cost Type A'
WHEN VIEW1_LEDGER = 'AA'
AND VIEW1_SUBCODE BETWEEN '2' AND '28999999'
THEN 'Cost Type B'
...
END VIEW2_COSTTYPE,
SUM(VIEW1_AMOUNT) VIEW2_AMOUNT
FROM
VIEW1
WHERE
GROUP BY
VIEW1_REF,
VIEW1_YEAR,
VIEW1_PERIOD,
4



"The final business view then concatenates the various Cost Types by Plot, Year & Period, as described above. We must return a row for each Plot, Year and Period, even if there are no financial transactions. To do this, I have created another view, PLOTPERIODS, that cartesians the Plot Master Table and the Calendar Table. I have then joined this back to the Plot Master (from which various other columns on the final view are to be sourced) and also left outer joined it to each of the 9 Cost Type row sets (under table aliases)."

CASE again ;-)

SELECT
PLOTPERIODS.REF VIEW_REF,
PLOTMASTER.TYPE VIEW_TYPE,
.
.
PLOTPERIODS.YEAR VIEW_YEAR,
PLOTPERIODS.PERIOD VIEW_PERIOD,
SUM(CASE WHEN VIEW2_COSTTYPE = 'Cost Type A'
THEN COSTTYPEA.VIEW2_AMOUNT
END)
FROM...
GROUP BY
PLOTPERIODS.REF VIEW_REF,
PLOTMASTER.TYPE VIEW_TYPE,
.
.
PLOTPERIODS.YEAR VIEW_YEAR,
PLOTPERIODS.PERIOD VIEW_PERIOD



It's hard to tell if i'm on the right track, without DDL Inserts and expected output, but you already tried to post less text ;-)

Dieter