| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2004-10-20 : 04:06:17
|
So an ex-colleague mailed me to say that he's have a performance problem, what do I think he can do to improve the SQL.I am posting this ONLY to show this SCARY thing - I am not looking for a way to help... I don't even want to think how long it will take:WARNING - This is PHENOMINALLY LONGand it may Just be a WORLD record munber of Joins  86 I thinkSELECT TOP 100000000 'Planno'=RTRIM(um.planno), 'MoveTyp'= CASE WHEN ch.Conttyp is NOT NULL THEN Case When um.MoveTyp=1 Then Case when ch.Conttyp=1 then 101 when ch.Conttyp=2 then 102 else um.MoveTyp end When um.MoveTyp=2 Then Case when ch.Conttyp=1 then 201 when ch.Conttyp=2 then 202 else um.MoveTyp end When um.MoveTyp=5 and sw.switchtype = 3 Then 500 When um.MoveTyp=52 and sw.switchtype = 3 Then 501 When um.MoveTyp=5 and sw.switchtype = 6 Then 502 When um.MoveTyp=52 and sw.switchtype = 6 Then 503 Else um.MoveTyp End ELSE Case When um.MoveTyp=5 and sw.switchtype = 3 Then 500 When um.MoveTyp=52 and sw.switchtype = 3 Then 501 Else um.MoveTyp End END, 'ContDt'=dbo.fn_ConvertDate(ih.CONTDT), ch.CONTTYP, 'ContNo' = Case WHEN um.movetyp in (3,39,41) THEN um.pbtxntriggerid ELSE um.contno END, ih.SeriesNo, 'PBTxnTriggerTargetDate'=dbo.fn_ConvertDate(pbt.PBTxnTriggerTargetDate), Prem_ID=CASE WHEN pid.Prem_ID IS NULL THEN '00' ELSE pid.Prem_ID END, ih.FundNo, um.ElNo, 'UniqueID' = ih.InvHstId, 'Plan_ID' = NULL, 'AllocAmount' = ISNULL(aa.AllocAmount,0), 'TotalFundTarget'= ISNULL(tt.TotalFundTarget,0), 'TotalFundUnits'= ISNULL(tt.TotalFundUnits,0), 'AWComm'= ISNULL(aw.AWComm, 0), 'UpFrontComm'= ISNULL(ufc.UpFrontComm, 0), CreatedDate = convert(char(8),ih.ServerCreatedDate,112) + convert(char(8),ih.ServerCreatedDate,108) +'.'+ ltrim(str(datepart(ms,ih.ServerCreatedDate))), sw.SwitchType, 'CollectionFee'= ISNULL(ch.ContHstCollectionFee,0), 'ContnDisc' = ISNULL(ch.ContHstContnDisc,0), 'EmpeeAmt' = ISNULL(ch.EmpeeAmt,0), 'AdvisorFee'= ISNULL(ch.AdvisorFee,0), 'InterestAmount' = ISNULL(ch.ContHstIntAmt,0), 'AllocFee'=CASE WHEN ch.ContNo IS NULL THEN 0 WHEN um.MoveTyp in (1, 2) THEN ISNULL(ch.ContHstAllocationFee,0) WHEN um.MoveTyp in (5, 18, 43, 44, 52) THEN Case When um.MoveTyp in (5,43,44,52) Then -1 * (ISNULL(ch.AdvisorFee,0) + ISNULL(aw.AWComm, 0) + ISNULL(ufc.UpFrontComm, 0) + ISNULL(ch.ContHstIntAmt,0)) Else (ISNULL(ch.EmpeeAmt,0) + ISNULL(ch.AdvisorFee,0)) - (ISNULL(aw.AWComm, 0) + ISNULL(ufc.UpFrontComm, 0) + ISNULL(inv.InvestAmt,0) + ISNULL(ch.ContHstIntAmt,0)) End ELSE 0 END, ld.LoanRemBalance, 'DateRcd'=dbo.fn_ConvertDate(ch.Datercd), pl.DefPlanFee, 'WOP_Premium'=ISNULL(ch.BENAMT,0), 'InvDt'=dbo.fn_ConvertDate(ih.INVDT), 'SetupFee'=NULL, 'Reversal'=NULL, 'InvestAmt'= CASE WHEN um.MoveTyp in (5,12,43,44,52) THEN ISNULL(inv.InvestAmt,0) ELSE ISNULL(ch.EmpeeAmt,0) + ISNULL(ch.AdvisorFee,0) ENDFROM Umvtrptb umINNER JOIN InvHst ih ON um.InvHstId = ih.InvHstId INNER JOIN PlanBasedTxnTrigger pbt ON um.PBTxnTriggerId = pbt.PBTxnTriggerId INNER JOIN Plan_ pl ON um.PLANNO = pl.PLANNOLEFT JOIN ContHst ch ON um.CONTNO = ch.CONTNO AND um.PLANNO = ch.PLANNO LEFT JOIN (Select DISTINCT 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM ppi2 inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') AS pid ON um.prodcd=pid.PPItemProductCode and pid.planno=um.planno and pid.Elno=um.Elno LEFT JOIN xSanLoanDet ld ON um.PLANNO = ld.Planno AND ld.LoanStatus=1LEFT JOIN Fundist fd ON um.PLANNO = fd.PLANNO AND um.FUNDNO = fd.FUNDNO LEFT JOIN Switch sw ON pbt.PBTxnInstructionId = sw.PBTxnTriggerIdLEFT JOIN (Select 'InvestAmt'= SUM(ih2.TARGET), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.contno, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId inner join (Select distinct FUNDNO from FUND where uwpind<>3) F ON um2.FUNDNO=F.FUNDNO left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno --Exlcude movetyp in (3,39,41) Where (um2.MOVETYP=1 AND ch2.RECSTAT IN (2,3,4)) or (um2.MOVETYP=2 AND ch2.RECSTAT IN (5,6)) or (um2.MOVETYP IN (5,12,52) and ih2.UNITS > 0) or (um2.MOVETYP IN (9,10,18,19,20,23,24,33,38,40,42,43,44,50,51,53)) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.CONTNO, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate,Prem_ID UNION Select 'InvestAmt'= SUM(ih2.TARGET), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.pbtxntriggerid, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId inner join (Select distinct FUNDNO from FUND where uwpind<>3) F ON um2.FUNDNO=F.FUNDNO left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno Where um2.movetyp in (3,39,41) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.PBTxnTriggerId, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate,Prem_ID) AS inv ON inv.Planno=um.Planno AND inv.Movetyp=um.Movetyp AND inv.CONTDT=ih.CONTDT AND (ch.CONTTYP is null or inv.CONTTYP=ch.CONTTYP) AND ((um.Movetyp IN (3,39,41) and inv.ContNumber=um.PBTxnTriggerId) OR (NOT um.Movetyp IN (3,39,41) AND inv.ContNumber=um.CONTNO)) AND inv.PBTxnTriggerTargetDate=pbt.PBTxnTriggerTargetDate AND (pid.Prem_ID IS NULL or inv.Prem_ID=pid.Prem_ID )LEFT JOIN (Select 'AllocAmount'= SUM(ih2.TARGET), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.contno, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId inner join (Select distinct FUNDNO from FUND where uwpind<>3) F ON um2.FUNDNO=F.FUNDNO left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO --Exlcude movetyp in (3,39,41) Where (um2.MOVETYP=1 AND ch2.RECSTAT IN (2,3,4)) or (um2.MOVETYP=2 AND ch2.RECSTAT IN (5,6)) or (um2.MOVETYP IN (5,12,52) and ih2.UNITS > 0) or (um2.MOVETYP IN (9,10,18,19,20,23,24,33,38,40,42,43,44,50,51,53)) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.CONTNO, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate UNION Select 'AllocAmount'= SUM(ih2.TARGET), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.pbtxntriggerid, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId inner join (Select distinct FUNDNO from FUND where uwpind<>3) F ON um2.FUNDNO=F.FUNDNO left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO Where um2.movetyp in (3,39,41) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.PBTxnTriggerId, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate) AS aa ON aa.Planno=um.Planno AND aa.Movetyp=um.Movetyp AND aa.CONTDT=ih.CONTDT AND (ch.CONTTYP is null or aa.CONTTYP=ch.CONTTYP) AND ((um.Movetyp IN (3,39,41) and aa.ContNumber=um.PBTxnTriggerId) OR (NOT um.Movetyp IN (3,39,41) AND aa.ContNumber=um.CONTNO)) AND aa.PBTxnTriggerTargetDate=pbt.PBTxnTriggerTargetDate LEFT JOIN (Select 'TotalFundTarget'=SUM(ih2.TARGET), 'TotalFundUnits'=SUM(ih2.UNITS), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.contno, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID, um2.Fundno from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno --Exlcude movetyp in (3,39,41) Where (um2.MOVETYP=1 AND ch2.RECSTAT IN (2,3,4)) or (um2.MOVETYP=2 AND ch2.RECSTAT IN (5,6)) or (um2.MOVETYP IN (5,12,52) and ih2.UNITS > 0) or (um2.MOVETYP IN (9,10,18,19,20,23,24,33,38,40,42,43,44,50,51,53)) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.CONTNO, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate,Prem_ID, um2.Fundno UNION Select 'TotalFundTarget'=SUM(ih2.TARGET), 'TotalFundUnits'=SUM(ih2.UNITS), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.pbtxntriggerid, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID, um2.Fundno from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno Where um2.movetyp in (3,39,41) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.PBTxnTriggerId, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, Prem_ID, um2.Fundno) AS tt ON tt.Planno=um.Planno AND tt.Movetyp=um.Movetyp AND tt.CONTDT=ih.CONTDT AND (ch.CONTTYP is null or tt.CONTTYP=ch.CONTTYP) AND ((um.Movetyp IN (3,39,41) and tt.ContNumber=um.PBTxnTriggerId) or (NOT um.Movetyp IN (3,39,41) AND tt.ContNumber=um.CONTNO)) AND tt.PBTxnTriggerTargetDate=pbt.PBTxnTriggerTargetDate AND (pid.Prem_ID IS NULL or tt.Prem_ID=pid.Prem_ID )AND tt.Fundno=um.Fundno LEFT JOIN (Select 'AWComm'=SUM(ci.CommItemAmount), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.contno, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID,um2.Fundno from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno left join CommissionItem ci ON um2.PBTxnTriggerId = ci.PBTxnTriggerId and um2.ElNo = ci.ElNo and ci.commcalcbasis = 5 Where (um2.MOVETYP=1 and ch2.RECSTAT IN (2,3,4) and ci.CommItemAmount > 0) or (um2.MOVETYP=2 and ch2.RECSTAT IN (5,6) and ci.CommItemAmount < 0) or (um2.MOVETYP IN (5,12,52) and ih2.UNITS > 0) or (um2.MOVETYP IN (9,10,18,19,20,23,24,33,38,40,42,43,44,50,51,53)) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.CONTNO, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, Prem_ID, um2.Fundno UNION Select 'AWComm'=SUM(ci.CommItemAmount), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.pbtxntriggerid, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID, um2.Fundno from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno left join CommissionItem ci ON um2.PBTxnTriggerId = ci.PBTxnTriggerId and um2.ElNo = ci.ElNo and ci.commcalcbasis = 5 Where um2.movetyp in (39,41) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.PBTxnTriggerId, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, Prem_ID, um2.Fundno) AS aw ON aw.Planno=um.Planno AND aw.Movetyp=um.Movetyp AND aw.CONTDT=ih.CONTDT AND (ch.CONTTYP is null or aw.CONTTYP=ch.CONTTYP) AND ((um.Movetyp IN (39,41) and aw.ContNumber=um.PBTxnTriggerId) or (NOT um.Movetyp IN (39,41) AND aw.ContNumber=um.CONTNO)) AND aw.PBTxnTriggerTargetDate=pbt.PBTxnTriggerTargetDate AND (pid.Prem_ID IS NULL or aw.Prem_ID=pid.Prem_ID) AND aw.Fundno=um.Fundno LEFT JOIN (Select 'UpFrontComm'=SUM(ci.CommItemAmount), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.contno, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID,um2.Fundno from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno left join CommissionItem ci ON um2.PBTxnTriggerId = ci.PBTxnTriggerId and um2.ElNo = ci.ElNo and ci.commcalcbasis = 1 Where (um2.MOVETYP=1 and ch2.RECSTAT IN (2,3,4) and ci.CommItemAmount > 0) or (um2.MOVETYP=2 and ch2.RECSTAT IN (5,6) and ci.CommItemAmount < 0) or (um2.MOVETYP IN (5,12,52) and ih2.UNITS > 0) or (um2.MOVETYP IN (9,10,18,19,20,23,24,33,38,40,42,43,44,50,51,53)) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.CONTNO, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, Prem_ID, um2.Fundno UNION Select 'UpFrontComm'=SUM(ci.CommItemAmount), um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, 'ContNumber'=um2.pbtxntriggerid, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, 'Prem_ID'= ppi.Prem_ID,um2.Fundno from Umvtrptb um2 inner join InvHst ih2 on um2.InvHstId = ih2.InvHstId inner join PlanBasedTxnTrigger pbt2 ON um2.PBTxnTriggerId = pbt2.PBTxnTriggerId left join ContHst ch2 ON um2.CONTNO = ch2.CONTNO AND um2.PLANNO = ch2.PLANNO left join (Select distinct 'Prem_ID'=case when el2.eltype = 1 and xsp2.planconversionflag = 1 and pl2.onriskdt=el2.elstrtdt then '05' when el2.eltype = 1 then '10' else '20' end, PPItemProductCode, pl2.planno, el2.Elno from PRODUCTPARAMETERITEM inner join plan_ pl2 on pl2.prodcd=PPItemProductCode inner join element el2 on pl2.PLANNO = el2.PLANNO inner join xSanPlan xsp2 on el2.planno = xsp2.planno where PPItemName = 'ChargeStructure' AND PPItemValue = '2') as ppi on um2.prodcd=ppi.PPItemProductCode and ppi.planno=um2.planno and ppi.Elno=um2.Elno left join CommissionItem ci ON um2.PBTxnTriggerId = ci.PBTxnTriggerId and um2.ElNo = ci.ElNo and ci.commcalcbasis = 1 --Only include movetyp in (39,41) Where um2.movetyp in (39,41) Group by um2.planno, um2.Movetyp, ih2.CONTDT, ch2.CONTTYP, um2.PBTxnTriggerId, ih2.Seriesno, pbt2.PBTxnTriggerTargetDate, Prem_ID, um2.Fundno) AS ufc ON ufc.Planno=um.Planno AND ufc.Movetyp=um.Movetyp AND ufc.CONTDT=ih.CONTDT AND (ch.CONTTYP is null or ufc.CONTTYP=ch.CONTTYP) AND ((um.Movetyp IN (39,41) and ufc.ContNumber=um.PBTxnTriggerId) or (NOT um.Movetyp IN (39,41) AND ufc.ContNumber=um.CONTNO)) AND ufc.PBTxnTriggerTargetDate=pbt.PBTxnTriggerTargetDate AND (pid.Prem_ID IS NULL or ufc.Prem_ID=pid.Prem_ID)AND ufc.Fundno=um.Fundno WHERE (um.MOVETYP=1 AND ch.RECSTAT IN (2,3,4)) OR (um.MOVETYP=2 AND ch.RECSTAT IN (5,6)) OR (um.MOVETYP IN (5,12,52) AND ih.UNITS > 0) OR (um.MOVETYP IN (3,9,10,18,19,20,23,24,33,38,39,40,41,42,43,44,50,51,53))ORDER BY um.planno, um.movetyp, ih.CONTDT, ch.CONTTYP, ContNo, ih.seriesno, pbt.PBTxnTriggerTargetDate, Prem_ID, um.FUNDNO, um.elno, UniqueID *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-20 : 04:21:57
|
and i thought our programmer's sprocs were already too loooonnnnnnggggg.... --------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-20 : 04:52:19
|
SELECT TOP 100000000 Wow!I expect it takes SQL a good 5 minutes to build a query plan!There are two (distinct) comments, this is one of them:--Only include movetyp in (39,41) Where um2.movetyp in (39,41) Clearly the rest of the code is obvious!Kristen |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 04:58:33
|
| Lol!Actually, I removed most of the comments - possible about 20 lines or so, but they are all brilliant insight's like that one, rather than explained why the @#~$ the uses so many left joins, etc.Fortunately, it only runs on a +- 200 GB OLTP system....... but at least it is part of the evening batch processing.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-10-20 : 05:33:27
|
| This is the loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooongest querry ive ever encountered.IMAGINE HOW LONG WILL THE PROCESS TIME WILL TAKE. Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-10-20 : 06:47:57
|
| Sorry for the foul language, but DAMN! That is one nasty piece of code...let's pray you never have to fix any bugs! What about posting the execution plan also?? Preferably as text --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:27:07
|
lol - fortunately it ain't my baby. I got a jpg of the access path -- looks like a road map for a medium sized country  .Will see about getting the guy who sent it to give an explain...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:41:52
|
hmm - seems to crash on posting... will try and cut it into smaller sections:PART 1StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------ SELECT TOP 100000000 'Planno'=RTRIM(um.planno), 'MoveTyp'= CASE WHEN ch.Conttyp is NOT NULL THEN Case When um.MoveTyp=1 Then Case when ch.Conttyp=1 then 101 when ch.Conttyp=2 then 102 1 1 0 NULL NULL 1 NULL 78632.031 NULL NULL NULL 3815.938 NULL NULL SELECT 0 NULL |--Sort(TOP 100000000, ORDER BY:([Expr1129] ASC, [Expr1130] ASC, [Expr1131] ASC, [ch].[CONTTYP] ASC, [Expr1132] ASC, [ih].[SERIESNO] ASC, [Expr1133] ASC, [Expr1134] ASC, [um].[FUNDNO] ASC, [um].[ELNO] ASC, [ih].[InvHstId] ASC)) 1 2 1 Sort TopN Sort TOP 100000000, ORDER BY:([Expr1129] ASC, [Expr1130] ASC, [Expr1131] ASC, [ch].[CONTTYP] ASC, [Expr1132] ASC, [ih].[SERIESNO] ASC, [Expr1133] ASC, [Expr1134] ASC, [um].[FUNDNO] ASC, [um].[ELNO] ASC, [ih].[InvHstId] ASC) NULL 78632.031 1.1261261E-2 5.8696971 220 3815.938 [um].[ELNO], [um].[FUNDNO], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [pl].[DEFPLANFEE], [ch].[CONTTYP], [ld].[LoanRemBalance], [sw].[SwitchType], [Expr1129], [Expr1130], [Expr1131], [Expr1132], [Expr1133], [Expr1134], [Expr1135], [Expr1136], [Expr1 NULL PLAN_ROW 0 1.0 |--Compute Scalar(DEFINE:([Expr1129]=rtrim(Convert([um].[PLANNO])), [Expr1130]=If ([ch].[CONTTYP]<>NULL) then If ([um].[MOVETYP]=1) then If ([ch].[CONTTYP]=1) then 101 else If ([ch].[CONTTYP]=2) then 102 else Convert([um].[MOVETYP]) else If ([um].[M 1 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1129]=rtrim(Convert([um].[PLANNO])), [Expr1130]=If ([ch].[CONTTYP]<>NULL) then If ([um].[MOVETYP]=1) then If ([ch].[CONTTYP]=1) then 101 else If ([ch].[CONTTYP]=2) then 102 else Convert([um].[MOVETYP]) else If ([um].[MOVETYP]=2) then If ([ch]. [Expr1129]=rtrim(Convert([um].[PLANNO])), [Expr1130]=If ([ch].[CONTTYP]<>NULL) then If ([um].[MOVETYP]=1) then If ([ch].[CONTTYP]=1) then 101 else If ([ch].[CONTTYP]=2) then 102 else Convert([um].[MOVETYP]) else If ([um].[MOVETYP]=2) then If ([ch].[CONTTYP 78632.031 0.0 7.8632031E-3 220 3810.0571 [um].[ELNO], [um].[FUNDNO], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [pl].[DEFPLANFEE], [ch].[CONTTYP], [ld].[LoanRemBalance], [sw].[SwitchType], [Expr1129], [Expr1130], [Expr1131], [Expr1132], [Expr1133], [Expr1134], [Expr1135], [Expr1136], [Expr1 NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([Union1120], [Union1121], [Union1122], [Union1126], [Union1128])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1120]=[um].[PLANNO] AND 1 4 3 Hash Match Right Outer Join HASH:([Union1120], [Union1121], [Union1122], [Union1126], [Union1128])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1120]=[um].[PLANNO] AND [Union1121]=[um].[MOVETYP]) AND [Union1122] NULL 78632.031 0.0 2.2136586 257 3810.0493 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Hash Match(Union) 1 5 4 Hash Match Union NULL [Union1119] = ([Expr1071], [Expr1064]), [Union1120] = ([um2].[PLANNO], [um2].[PLANNO]), [Union1121] = ([um2].[MOVETYP], [um2].[MOVETYP]), [Union1122] = ([ih2].[CONTDT], [ih2].[CONTDT]), [Union1123] = ([ch2].[CONTTYP], [ch2].[CONTTYP]), [Union1124] = ([um2] 30300.035 0.0 0.5658462 57 530.35327 [Union1119], [Union1120], [Union1121], [Union1122], [Union1123], [Union1124], [Union1125], [Union1126], [Union1127], [Union1128] NULL PLAN_ROW 0 1.0 | |--Compute Scalar(DEFINE:([Expr1071]=If ([Expr1365]=0) then NULL else [Expr1366])) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Expr1071]=If ([Expr1365]=0) then NULL else [Expr1366]) [Expr1071]=If ([Expr1365]=0) then NULL else [Expr1366] 4757.8447 0.0 3.5445943E-2 57 41.301765 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1071] NULL PLAN_ROW 0 1.0 | | |--Stream Aggregate(GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [ppi].[prem_id]) DEFINE:([Expr1365]=COUNT_ 1 7 6 Stream Aggregate Aggregate GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [ppi].[prem_id]) [Expr1365]=COUNT_BIG([ci].[CommItemAmount]), [Expr1366]=SUM([ci].[CommItemAmount]) 4757.8447 0.0 3.5445943E-2 57 41.301765 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1365], [Expr1366] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([um2].[ELNO], [um2].[PBTxnTriggerId]) WITH PREFETCH) 1 8 7 Nested Loops Left Outer Join OUTER REFERENCES:([um2].[ELNO], [um2].[PBTxnTriggerId]) WITH PREFETCH NULL 4757.8447 0.0 0.01988779 99 41.266319 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[PBTxnTriggerId] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_ 1 10 8 Sort Sort ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[PBTxnTriggerId] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_id] ASC) NULL 3370.7986 1.1261261E-2 6.1723121E-2 55 30.586061 [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | | |--Hash Match(Left Outer Join, HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[ 1 11 10 Hash Match Left Outer Join HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 3370.7986 0.0 0.52888888 81 30.513077 [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([um2].[CONTNO], [um2].[PLANNO]) WITH PREFETCH) 1 12 11 Nested Loops Left Outer Join OUTER REFERENCES:([um2].[CONTNO], [um2].[PLANNO]) WITH PREFETCH NULL 3370.7986 0.0 1.4089938E-2 339 29.710636 [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 14 12 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 3008.897 0.0 1.2577189E-2 300 20.229145 [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 16 14 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 3008.897 0.0 1.2577189E-2 151 10.736282 [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 18 16 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 3008.897 1.1261261E-2 5.4337908E-2 44 1.5894219 [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39)) 1 19 18 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] 3008.897 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:42:46
|
PART 2 | | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD) 1 20 16 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 9.134284 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 3008.897 | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 21 14 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 9.4802856 [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 3008.897 | | | | |--Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), SEEK:([ch2].[CONTNO]=[um2].[CONTNO] AND [ch2].[PLANNO]=[um2].[PLANNO]) ORDERED FORWA 1 22 12 Index Seek Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), SEEK:([ch2].[CONTNO]=[um2].[CONTNO] AND [ch2].[PLANNO]=[um2].[PLANNO]) ORDERED FORWARD [ch2].[CONTTYP] 1.1202772 3.2034749E-3 7.9740144E-5 47 9.4674006 [ch2].[CONTTYP] NULL PLAN_ROW 0 3008.897 | | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 23 11 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), SEEK:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[CommCalcBasis]=1 AND [ci].[El 1 25 8 Index Seek Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), SEEK:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[CommCalcBasis]=1 AND [ci].[ElNo]=[um2].[ELNO]) ORDERED FORWARD [ci].[CommItemAmount] 1.4114889 3.2034749E-3 8.0066755E-5 51 10.660371 [ci].[CommItemAmount] NULL PLAN_ROW 0 3370.7986 | |--Compute Scalar(DEFINE:([Expr1064]=If ([Expr1367]=0) then NULL else [Expr1368])) 1 41 5 Compute Scalar Compute Scalar DEFINE:([Expr1064]=If ([Expr1367]=0) then NULL else [Expr1368]) [Expr1064]=If ([Expr1367]=0) then NULL else [Expr1368] 25542.189 0.0 1.8615677 57 488.48563 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1064] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([um2].[PLANNO] 1 42 41 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP]) [Expr1367]=COUNT_BIG([ci].[CommItemAmount]), [Expr1368]=SUM([ci].[CommItemAmount]) 25542.189 0.0 1.8615677 57 488.48563 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1367], [Expr1368] NULL PLAN_ROW 0 1.0 | |--Filter(WHERE:(((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) AND [ci].[CommItemAmount]>0.00) OR (([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5)) 1 43 42 Filter Filter WHERE:(((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) AND [ci].[CommItemAmount]>0.00) OR (([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5)) AND [ci].[CommItemAmount]<0.00)) OR ((([um NULL 25542.189 0.0 1.018574 86 486.62405 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[PBTxnTriggerId], [um2].[ELNO])=([ci].[PBTxnTriggerId], [ci].[ElNo]), RESIDUAL:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[ElNo]=[um2].[ELNO])) 1 44 43 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[PBTxnTriggerId], [um2].[ELNO])=([ci].[PBTxnTriggerId], [ci].[ElNo]), RESIDUAL:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[ElNo]=[um2].[ELNO]) NULL 106322.97 18.029146 42.566944 86 485.6055 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP], [ppi].[prem_id], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC, [um2].[ELNO] ASC)) 1 45 44 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC, [um2].[ELNO] ASC) NULL 57615.449 1.1261261E-2 4.1822348 68 335.80075 [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Hash Match(Right Outer Join, HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[pla 1 46 45 Hash Match Right Outer Join HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 57615.449 0.0 2.2873647 95 331.60724 [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 47 46 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 48 46 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 57615.449 14.9927 30.664753 84 329.04633 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 49 48 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 53019.871 1.1261261E-2 3.8194704 66 234.6167 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 50 49 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 53019.871 0.0 0.22162305 164 230.78596 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 52 50 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 53019.871 1.1261261E-2 3.8194704 58 182.41937 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 53 52 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 53019.871 0.0 0.22490828 214 178.58864 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | |--Filter(WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MO 1 55 53 Filter Filter WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MOVETYP]=51) OR [um2].[MOVETYP]=50) OR [um2].[MOVETYP]=44) OR [um2].[MOVETYP]=43) OR [um2 NULL 53805.809 0.0 0.69305003 65 2.1255727 [um2].[InvHstId], [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2])) 1 56 55 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]) [um2].[InvHstId], [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:43:26
|
PART 3 | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 140 53 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 176.23816 [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 53805.809 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORW 1 141 50 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 48.144974 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 53019.871 | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 142 48 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), WHERE:([ci].[CommCalcBasis]=1) ORDERED FORWARD) 1 151 44 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), WHERE:([ci].[CommCalcBasis]=1) ORDERED FORWARD [ci].[CommCalcBasis], [ci].[ElNo], [ci].[PBTxnTriggerId], [ci].[CommItemAmount], [KeyCo2] 1.8938306E+7 57.953876 21.759682 51 79.713554 [ci].[CommCalcBasis], [ci].[ElNo], [ci].[PBTxnTriggerId], [ci].[CommItemAmount], [KeyCo2] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([Union1110], [Union1111], [Union1112], [Union1116], [Union1118])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1110]=[um].[PLANNO 1 290 4 Hash Match Right Outer Join HASH:([Union1110], [Union1111], [Union1112], [Union1116], [Union1118])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1110]=[um].[PLANNO] AND [Union1111]=[um].[MOVETYP]) AND [Union1112] NULL 78632.031 0.0 2.1455894 248 3277.4822 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Hash Match(Union) 1 291 290 Hash Match Union NULL [Union1109] = ([Expr1057], [Expr1050]), [Union1110] = ([um2].[PLANNO], [um2].[PLANNO]), [Union1111] = ([um2].[MOVETYP], [um2].[MOVETYP]), [Union1112] = ([ih2].[CONTDT], [ih2].[CONTDT]), [Union1113] = ([ch2].[CONTTYP], [ch2].[CONTTYP]), [Union1114] = ([um2] 29007.701 0.0 0.56109113 57 469.64545 [Union1109], [Union1110], [Union1111], [Union1112], [Union1113], [Union1114], [Union1115], [Union1116], [Union1117], [Union1118] NULL PLAN_ROW 0 1.0 | |--Compute Scalar(DEFINE:([Expr1057]=If ([Expr1369]=0) then NULL else [Expr1370])) 1 292 291 Compute Scalar Compute Scalar DEFINE:([Expr1057]=If ([Expr1369]=0) then NULL else [Expr1370]) [Expr1057]=If ([Expr1369]=0) then NULL else [Expr1370] 4834.4946 0.0 3.6016982E-2 57 41.302742 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1057] NULL PLAN_ROW 0 1.0 | | |--Stream Aggregate(GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [ppi].[prem_id]) DEFINE:([Expr1369]=C 1 293 292 Stream Aggregate Aggregate GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [ppi].[prem_id]) [Expr1369]=COUNT_BIG([ci].[CommItemAmount]), [Expr1370]=SUM([ci].[CommItemAmount]) 4834.4946 0.0 3.6016982E-2 57 41.302742 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1369], [Expr1370] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([um2].[ELNO], [um2].[PBTxnTriggerId]) WITH PREFETCH) 1 294 293 Nested Loops Left Outer Join OUTER REFERENCES:([um2].[ELNO], [um2].[PBTxnTriggerId]) WITH PREFETCH NULL 4834.4946 0.0 2.0208187E-2 99 41.266727 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[PBTxnTriggerId] ASC, [ih2].[SERIESNO] ASC, [ppi].[ 1 296 294 Sort Sort ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[PBTxnTriggerId] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_id] ASC) NULL 3370.7986 1.1261261E-2 6.1723121E-2 55 30.586061 [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | | |--Hash Match(Left Outer Join, HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[plan 1 297 296 Hash Match Left Outer Join HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 3370.7986 0.0 0.52888888 81 30.513077 [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([um2].[CONTNO], [um2].[PLANNO]) WITH PREFETCH) 1 298 297 Nested Loops Left Outer Join OUTER REFERENCES:([um2].[CONTNO], [um2].[PLANNO]) WITH PREFETCH NULL 3370.7986 0.0 1.4089938E-2 339 29.710636 [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 300 298 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 3008.897 0.0 1.2577189E-2 300 20.229145 [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 302 300 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 3008.897 0.0 1.2577189E-2 151 10.736282 [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 304 302 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 3008.897 1.1261261E-2 5.4337908E-2 44 1.5894219 [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39)) 1 305 304 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] 3008.897 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[CONTNO], [um2].[ProdCd], [um2].[ELNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:44:12
|
PART 4 | | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORW 1 306 302 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 9.134284 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 3008.897 | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 307 300 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 9.4802856 [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 3008.897 | | | | |--Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), SEEK:([ch2].[CONTNO]=[um2].[CONTNO] AND [ch2].[PLANNO]=[um2].[PLANNO]) ORDERED 1 308 298 Index Seek Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), SEEK:([ch2].[CONTNO]=[um2].[CONTNO] AND [ch2].[PLANNO]=[um2].[PLANNO]) ORDERED FORWARD [ch2].[CONTTYP] 1.1202772 3.2034749E-3 7.9740144E-5 47 9.4674006 [ch2].[CONTTYP] NULL PLAN_ROW 0 3008.897 | | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 309 297 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), SEEK:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[CommCalcBasis]=5 AND [ci 1 311 294 Index Seek Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), SEEK:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[CommCalcBasis]=5 AND [ci].[ElNo]=[um2].[ELNO]) ORDERED FORWARD [ci].[CommItemAmount] 1.4342282 3.2034749E-3 8.0091995E-5 51 10.660457 [ci].[CommItemAmount] NULL PLAN_ROW 0 3370.7986 | |--Compute Scalar(DEFINE:([Expr1050]=If ([Expr1371]=0) then NULL else [Expr1372])) 1 327 291 Compute Scalar Compute Scalar DEFINE:([Expr1050]=If ([Expr1371]=0) then NULL else [Expr1372]) [Expr1050]=If ([Expr1371]=0) then NULL else [Expr1372] 24173.207 0.0 1.7627447 57 427.78162 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1050] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([um2].[PL 1 328 327 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP]) [Expr1371]=COUNT_BIG([ci].[CommItemAmount]), [Expr1372]=SUM([ci].[CommItemAmount]) 24173.207 0.0 1.7627447 57 427.78162 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1371], [Expr1372] NULL PLAN_ROW 0 1.0 | |--Filter(WHERE:(((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) AND [ci].[CommItemAmount]>0.00) OR (([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT] 1 329 328 Filter Filter WHERE:(((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) AND [ci].[CommItemAmount]>0.00) OR (([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5)) AND [ci].[CommItemAmount]<0.00)) OR ((([um NULL 24173.207 0.0 0.99840325 86 426.01889 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 | |--Hash Match(Left Outer Join, HASH:([um2].[PBTxnTriggerId], [um2].[ELNO])=([ci].[PBTxnTriggerId], [ci].[ElNo]), RESIDUAL:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[ElNo]=[um2].[ELNO])) 1 330 329 Hash Match Left Outer Join HASH:([um2].[PBTxnTriggerId], [um2].[ELNO])=([ci].[PBTxnTriggerId], [ci].[ElNo]), RESIDUAL:([ci].[PBTxnTriggerId]=[um2].[PBTxnTriggerId] AND [ci].[ElNo]=[um2].[ELNO]) NULL 104217.46 0.0 4.2045708 86 425.02048 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP], [ppi].[prem_id], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 | |--Hash Match(Right Outer Join, HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[pla 1 331 330 Hash Match Right Outer Join HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 57615.449 0.0 2.2873647 95 331.60724 [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 332 331 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 333 331 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 57615.449 14.9927 30.664753 84 329.04633 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 334 333 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 53019.871 1.1261261E-2 3.8194704 66 234.6167 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 335 334 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 53019.871 0.0 0.22162305 164 230.78596 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 337 335 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 53019.871 1.1261261E-2 3.8194704 58 182.41937 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 338 337 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 53019.871 0.0 0.22490828 214 178.58864 [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | |--Filter(WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MO 1 340 338 Filter Filter WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MOVETYP]=51) OR [um2].[MOVETYP]=50) OR [um2].[MOVETYP]=44) OR [um2].[MOVETYP]=43) OR [um2 NULL 53805.809 0.0 0.69305003 65 2.1255727 [um2].[InvHstId], [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2])) 1 341 340 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]) [um2].[InvHstId], [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[ProdCd], [um2].[ELNO], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:45:31
|
part 5 | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 425 338 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 176.23816 [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 53805.809 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORW 1 426 335 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 48.144974 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 53019.871 | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 427 333 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), WHERE:([ci].[CommCalcBasis]=5)) 1 436 330 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), WHERE:([ci].[CommCalcBasis]=5) [ci].[CommCalcBasis], [ci].[ElNo], [ci].[PBTxnTriggerId], [ci].[CommItemAmount] 148157.98 57.953876 21.759682 51 79.713554 [ci].[CommCalcBasis], [ci].[ElNo], [ci].[PBTxnTriggerId], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([Union1100], [Union1101], [Union1102], [Union1106], [Union1108])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1100]=[um].[P 1 569 290 Hash Match Right Outer Join HASH:([Union1100], [Union1101], [Union1102], [Union1106], [Union1108])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1100]=[um].[PLANNO] AND [Union1101]=[um].[MOVETYP]) AND [Union1102] NULL 78632.031 0.0 5.1295695 240 2805.6912 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Hash Match(Union) 1 570 569 Hash Match Union NULL [Union1098] = ([Expr1035], [Expr1042]), [Union1099] = ([Expr1036], [Expr1043]), [Union1100] = ([um2].[PLANNO], [um2].[PLANNO]), [Union1101] = ([um2].[MOVETYP], [um2].[MOVETYP]), [Union1102] = ([ih2].[CONTDT], [ih2].[CONTDT]), [Union1103] = ([ch2].[CONTTYP] 77733.203 0.0 2.4308815 65 646.04004 [Union1098], [Union1099], [Union1100], [Union1101], [Union1102], [Union1103], [Union1104], [Union1105], [Union1106], [Union1107], [Union1108] NULL PLAN_ROW 0 1.0 | |--Stream Aggregate(GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [ppi].[prem_id]) DEFINE:([Expr1035]=SUM([ih2] 1 571 570 Stream Aggregate Aggregate GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [ppi].[prem_id]) [Expr1035]=SUM([ih2].[TARGET]), [Expr1036]=SUM([ih2].[UNITS]) 24502.715 0.0 0.18254521 65 325.15872 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1035], [Expr1036] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[CONTNO] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_id] ASC) 1 572 571 Sort Sort ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[CONTNO] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_id] ASC) NULL 24502.715 1.1261261E-2 1.6399491 65 324.97617 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Hash Match(Left Outer Join, HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[ 1 573 572 Hash Match Left Outer Join HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 24502.715 0.0 1.8323628 97 323.32495 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 574 573 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 24502.715 0.0 0.10242134 173 321.21906 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 576 574 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 24502.715 1.1261261E-2 1.6399491 67 279.01758 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Filter(WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([u 1 577 576 Filter Filter WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5) AND [ NULL 24502.715 0.0 0.51738673 84 277.36639 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 578 577 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 57615.449 14.9927 30.664753 84 276.849 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 579 578 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 53019.871 1.1261261E-2 3.8194704 66 182.41937 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 580 579 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 53019.871 0.0 0.22490828 214 178.58864 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | | |--Filter(WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MO 1 582 580 Filter Filter WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MOVETYP]=51) OR [um2].[MOVETYP]=50) OR [um2].[MOVETYP]=44) OR [um2].[MOVETYP]=43) OR [um2 NULL 53805.809 0.0 0.69305003 65 2.1255727 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2])) 1 583 582 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]) [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 667 580 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 176.23816 [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 53805.809 | | | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 668 578 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD) 1 783 574 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 42.099037 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 24502.715 | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 784 573 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([ 1 793 570 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[M [Expr1042]=SUM([ih2].[TARGET]), [Expr1043]=SUM([ih2].[UNITS]) 53230.484 0.0 4.1462302 65 318.45044 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1042], [Expr1043] NULL PLAN_ROW 0 1.0 | |--Hash Match(Right Outer Join, HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2] 1 794 793 Hash Match Right Outer Join HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 53230.484 0.0 2.2539074 97 314.3042 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 795 794 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 796 794 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 53230.484 13.852128 30.457769 91 311.77673 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 797 796 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 48979.242 1.1261261E-2 3.5026872 74 218.69467 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 798 797 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 48979.242 0.0 0.20473324 172 215.18071 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 800 798 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 48979.242 1.1261261E-2 3.5026872 66 167.63292 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 801 800 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 48979.242 0.0 0.20723413 214 164.11897 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3)) 1 803 801 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3) [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] 49577.543 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:53:33
|
part 6 | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 425 338 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 176.23816 [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 53805.809 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORW 1 426 335 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 48.144974 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 53019.871 | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 427 333 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), WHERE:([ci].[CommCalcBasis]=5)) 1 436 330 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CommissionItem].[ix5_pbtxnid_elno_commcalcbasis_commitemamount] AS [ci]), WHERE:([ci].[CommCalcBasis]=5) [ci].[CommCalcBasis], [ci].[ElNo], [ci].[PBTxnTriggerId], [ci].[CommItemAmount] 148157.98 57.953876 21.759682 51 79.713554 [ci].[CommCalcBasis], [ci].[ElNo], [ci].[PBTxnTriggerId], [ci].[CommItemAmount] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([Union1100], [Union1101], [Union1102], [Union1106], [Union1108])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1100]=[um].[P 1 569 290 Hash Match Right Outer Join HASH:([Union1100], [Union1101], [Union1102], [Union1106], [Union1108])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate], [um].[FUNDNO]), RESIDUAL:(((((((([Union1100]=[um].[PLANNO] AND [Union1101]=[um].[MOVETYP]) AND [Union1102] NULL 78632.031 0.0 5.1295695 240 2805.6912 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Hash Match(Union) 1 570 569 Hash Match Union NULL [Union1098] = ([Expr1035], [Expr1042]), [Union1099] = ([Expr1036], [Expr1043]), [Union1100] = ([um2].[PLANNO], [um2].[PLANNO]), [Union1101] = ([um2].[MOVETYP], [um2].[MOVETYP]), [Union1102] = ([ih2].[CONTDT], [ih2].[CONTDT]), [Union1103] = ([ch2].[CONTTYP] 77733.203 0.0 2.4308815 65 646.04004 [Union1098], [Union1099], [Union1100], [Union1101], [Union1102], [Union1103], [Union1104], [Union1105], [Union1106], [Union1107], [Union1108] NULL PLAN_ROW 0 1.0 | |--Stream Aggregate(GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [ppi].[prem_id]) DEFINE:([Expr1035]=SUM([ih2] 1 571 570 Stream Aggregate Aggregate GROUP BY:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [pbt2].[PBTxnTriggerTargetDate], [um2].[FUNDNO], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [ppi].[prem_id]) [Expr1035]=SUM([ih2].[TARGET]), [Expr1036]=SUM([ih2].[UNITS]) 24502.715 0.0 0.18254521 65 325.15872 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1035], [Expr1036] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[CONTNO] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_id] ASC) 1 572 571 Sort Sort ORDER BY:([um2].[PLANNO] ASC, [um2].[MOVETYP] ASC, [ih2].[CONTDT] ASC, [pbt2].[PBTxnTriggerTargetDate] ASC, [um2].[FUNDNO] ASC, [ch2].[CONTTYP] ASC, [um2].[CONTNO] ASC, [ih2].[SERIESNO] ASC, [ppi].[prem_id] ASC) NULL 24502.715 1.1261261E-2 1.6399491 65 324.97617 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Hash Match(Left Outer Join, HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[ 1 573 572 Hash Match Left Outer Join HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 24502.715 0.0 1.8323628 97 323.32495 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 574 573 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 24502.715 0.0 0.10242134 173 321.21906 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 576 574 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 24502.715 1.1261261E-2 1.6399491 67 279.01758 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Filter(WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([u 1 577 576 Filter Filter WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5) AND [ NULL 24502.715 0.0 0.51738673 84 277.36639 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 578 577 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 57615.449 14.9927 30.664753 84 276.849 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 579 578 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 53019.871 1.1261261E-2 3.8194704 66 182.41937 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 580 579 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 53019.871 0.0 0.22490828 214 178.58864 [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | | | |--Filter(WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MO 1 582 580 Filter Filter WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MOVETYP]=51) OR [um2].[MOVETYP]=50) OR [um2].[MOVETYP]=44) OR [um2].[MOVETYP]=43) OR [um2 NULL 53805.809 0.0 0.69305003 65 2.1255727 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2])) 1 583 582 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]) [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 667 580 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 176.23816 [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 53805.809 | | | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 668 578 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD) 1 783 574 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 42.099037 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 24502.715 | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 784 573 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([ 1 793 570 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id], [um2].[FUNDNO]), RESIDUAL:(((((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[M [Expr1042]=SUM([ih2].[TARGET]), [Expr1043]=SUM([ih2].[UNITS]) 53230.484 0.0 4.1462302 65 318.45044 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1042], [Expr1043] NULL PLAN_ROW 0 1.0 | |--Hash Match(Right Outer Join, HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2] 1 794 793 Hash Match Right Outer Join HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 53230.484 0.0 2.2539074 97 314.3042 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 795 794 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 796 794 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 53230.484 13.852128 30.457769 91 311.77673 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 797 796 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 48979.242 1.1261261E-2 3.5026872 74 218.69467 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 798 797 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 48979.242 0.0 0.20473324 172 215.18071 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 800 798 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 48979.242 1.1261261E-2 3.5026872 66 167.63292 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 801 800 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 48979.242 0.0 0.20723413 214 164.11897 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 1.0 | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3)) 1 803 801 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3) [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] 49577.543 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [um2].[FUNDNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 07:56:45
|
part 7 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 804 801 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 162.3568 [ih2].[TARGET], [ih2].[UNITS], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 49577.543 | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD) 1 805 798 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 47.343056 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 48979.242 | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 806 796 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([Union1091], [Union1092], [Union1093], [Union1097])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate]), RESIDUAL:(((((([Union1091]=[um].[PLANNO] AND [Union1092]=[u 1 823 569 Hash Match Right Outer Join HASH:([Union1091], [Union1092], [Union1093], [Union1097])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate]), RESIDUAL:(((((([Union1091]=[um].[PLANNO] AND [Union1092]=[um].[MOVETYP]) AND [Union1093]=[ih].[CONTDT]) AND ([ch].[CON NULL 78632.031 0.0 3.9289677 218 2154.5215 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Hash Match(Union) 1 824 823 Hash Match Union NULL [Union1090] = ([Expr1022], [Expr1029]), [Union1091] = ([um2].[PLANNO], [um2].[PLANNO]), [Union1092] = ([um2].[MOVETYP], [um2].[MOVETYP]), [Union1093] = ([ih2].[CONTDT], [ih2].[CONTDT]), [Union1094] = ([ch2].[CONTTYP], [ch2].[CONTTYP]), [Union1095] = ([um2] 69439.148 0.0 1.7827113 50 605.54382 [Union1090], [Union1091], [Union1092], [Union1093], [Union1094], [Union1095], [Union1096], [Union1097] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate]), RESIDUAL:(((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2] 1 825 824 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate]), RESIDUAL:(((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP]) AND [ih2].[CONTDT]=[ih2].[CONTDT]) [Expr1022]=SUM([ih2].[TARGET]) 21808.502 0.0 1.3745816 50 309.4082 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [Expr1022] NULL PLAN_ROW 0 1.0 | | |--Filter(WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([um2].[MOVETYP]=5 1 826 825 Filter Filter WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5) AND [ NULL 21808.502 0.0 0.46273807 206 308.03363 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 827 826 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 51529.852 13.410172 30.37764 206 307.57089 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 828 827 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 47444.574 0.0 0.20052131 188 215.01091 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 830 828 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 47971.605 1.1261261E-2 3.4240258 36 57.725864 [um2].[InvHstId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 831 830 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 47971.605 0.0 0.20052131 138 54.290577 [um2].[InvHstId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 833 831 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 47971.605 1.1261261E-2 3.4240258 32 5.9138579 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | |--Hash Match(Inner Join, HASH:([FUND].[FUNDNO])=([um2].[FUNDNO])) 1 834 833 Hash Match Inner Join HASH:([FUND].[FUNDNO])=([um2].[FUNDNO]) NULL 47971.605 0.0 0.31005281 36 2.4785709 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | |--Stream Aggregate(GROUP BY:([FUND].[FUNDNO])) 1 835 834 Stream Aggregate Aggregate GROUP BY:([FUND].[FUNDNO]) NULL 43.81076 0.0 5.8188476E-4 9 4.2942449E-2 [FUND].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD) 1 836 835 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] 135.05882 4.2022943E-2 2.5889999E-4 121 4.2281844E-2 [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] NULL PLAN_ROW 0 1.0 | | | | | |--Filter(WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MO 1 838 834 Filter Filter WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MOVETYP]=51) OR [um2].[MOVETYP]=50) OR [um2].[MOVETYP]=44) OR [um2].[MOVETYP]=43) OR [um2 NULL 53805.809 0.0 0.69305003 65 2.1255727 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2])) 1 839 838 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]) [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORW 1 924 831 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 48.176197 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 47971.605 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 925 828 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 157.08452 [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 47971.605 | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 926 827 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO]), RESIDUAL:((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP] 1 1045 824 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO]), RESIDUAL:((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP]) AND [ih2].[CONTDT]=[ih2].[CONTDT]) AND [ch2].[CONTTYP]=[ch2] [Expr1029]=SUM([ih2].[TARGET]), [pbt2].[PBTxnTriggerTargetDate]=ANY([pbt2].[PBTxnTriggerTargetDate]) 47630.641 0.0 2.8715739 50 294.35287 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [Expr1029] NULL PLAN_ROW 0 1.0 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 1046 1045 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 47630.641 12.395739 30.193527 209 291.48129 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 1047 1046 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 43842.074 0.0 0.18476358 192 200.11986 [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 1049 1047 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 44201.813 1.1261261E-2 3.1310048 40 55.226757 [um2].[InvHstId], [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 1050 1049 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 44201.813 0.0 0.18476358 138 52.084492 [um2].[InvHstId], [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 1052 1050 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 44201.813 1.1261261E-2 3.1310048 32 5.0272756 [um2].[InvHstId], [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] NULL PLAN_ROW 0 1.0 | | | | |--Hash Match(Inner Join, HASH:([FUND].[FUNDNO])=([um2].[FUNDNO])) 1 1053 1052 Hash Match Inner Join HASH:([FUND].[FUNDNO])=([um2].[FUNDNO]) NULL 44201.813 0.0 0.28711614 36 1.8850093 [um2].[InvHstId], [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] NULL PLAN_ROW 0 1.0 | | | | |--Stream Aggregate(GROUP BY:([FUND].[FUNDNO])) 1 1054 1053 Stream Aggregate Aggregate GROUP BY:([FUND].[FUNDNO]) NULL 43.81076 0.0 5.8188476E-4 9 4.2942449E-2 [FUND].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD) 1 1055 1054 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] 135.05882 4.2022943E-2 2.5889999E-4 121 4.2281844E-2 [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 10:06:08
|
part 8 | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3)) 1 1057 1053 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3) [um2].[InvHstId], [um2].[FUNDNO], [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] 49577.543 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[FUNDNO], [um2].[CONTNO], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] NULL PLAN_ROW 0 1.0 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD) 1 1059 1050 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 46.872452 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 44201.813 | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 1060 1047 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 144.70834 [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 44201.813 | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 1061 1046 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([Union1082], [Union1083], [Union1084], [Union1088])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate]), RESIDUAL:((((((([Union1082]=[um].[PLANNO] AND [Union10 1 1077 823 Hash Match Right Outer Join HASH:([Union1082], [Union1083], [Union1084], [Union1088])=([um].[PLANNO], [um].[MOVETYP], [ih].[CONTDT], [pbt].[PBTxnTriggerTargetDate]), RESIDUAL:((((((([Union1082]=[um].[PLANNO] AND [Union1083]=[um].[MOVETYP]) AND [Union1084]=[ih].[CONTDT]) AND ([ch].[CO NULL 78632.031 0.0 4.1154613 214 1545.0486 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Hash Match(Union) 1 1078 1077 Hash Match Union NULL [Union1081] = ([Expr1007], [Expr1015]), [Union1082] = ([um2].[PLANNO], [um2].[PLANNO]), [Union1083] = ([um2].[MOVETYP], [um2].[MOVETYP]), [Union1084] = ([ih2].[CONTDT], [ih2].[CONTDT]), [Union1085] = ([ch2].[CONTTYP], [ch2].[CONTTYP]), [Union1086] = ([um2] 69439.148 0.0 1.8914423 55 610.4201 [Union1081], [Union1082], [Union1083], [Union1084], [Union1085], [Union1086], [Union1087], [Union1088], [Union1089] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id]), RESIDUAL:((((((([um2].[PLANNO]=[ 1 1079 1078 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[CONTNO], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id]), RESIDUAL:((((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP]) AND [ih2].[CONTD [Expr1007]=SUM([ih2].[TARGET]) 21808.502 0.0 1.4833125 55 311.34402 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1007] NULL PLAN_ROW 0 1.0 | | |--Hash Match(Left Outer Join, HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[plan 1 1080 1079 Hash Match Left Outer Join HASH:([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO])=([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 21808.502 0.0 1.5535381 87 309.86072 [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | | |--Filter(WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([um2].[ 1 1081 1080 Filter Filter WHERE:((((((((((((((((((([um2].[MOVETYP]=1 AND (([ch2].[RECSTAT]=4 OR [ch2].[RECSTAT]=3) OR [ch2].[RECSTAT]=2)) OR ([um2].[MOVETYP]=2 AND ([ch2].[RECSTAT]=6 OR [ch2].[RECSTAT]=5))) OR ((([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5) AND [ NULL 21808.502 0.0 0.46273807 216 308.03363 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 1082 1081 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 51529.852 13.410172 30.37764 216 307.57089 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 1083 1082 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 47444.574 0.0 0.20052131 198 215.01091 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 1085 1083 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 47971.605 1.1261261E-2 3.4240258 46 57.725864 [um2].[InvHstId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 1086 1085 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 47971.605 0.0 0.20052131 149 54.290577 [um2].[InvHstId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 1088 1086 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 47971.605 1.1261261E-2 3.4240258 42 5.9138579 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | | |--Hash Match(Inner Join, HASH:([FUND].[FUNDNO])=([um2].[FUNDNO])) 1 1089 1088 Hash Match Inner Join HASH:([FUND].[FUNDNO])=([um2].[FUNDNO]) NULL 47971.605 0.0 0.31005281 47 2.4785709 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | | |--Stream Aggregate(GROUP BY:([FUND].[FUNDNO])) 1 1090 1089 Stream Aggregate Aggregate GROUP BY:([FUND].[FUNDNO]) NULL 43.81076 0.0 5.8188476E-4 9 4.2942449E-2 [FUND].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD) 1 1091 1090 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] 135.05882 4.2022943E-2 2.5889999E-4 121 4.2281844E-2 [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] NULL PLAN_ROW 0 1.0 | | | | | | |--Filter(WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR 1 1093 1089 Filter Filter WHERE:(((((((((((((((((([um2].[MOVETYP]=1 OR [um2].[MOVETYP]=2) OR (([um2].[MOVETYP]=52 OR [um2].[MOVETYP]=12) OR [um2].[MOVETYP]=5)) OR [um2].[MOVETYP]=53) OR [um2].[MOVETYP]=51) OR [um2].[MOVETYP]=50) OR [um2].[MOVETYP]=44) OR [um2].[MOVETYP]=43) OR [um2 NULL 53805.809 0.0 0.69305003 65 2.1255727 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2])) 1 1094 1093 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]) [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[PBTxnTriggerId], [um2].[FUNDNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[CONTNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) OR 1 1179 1086 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 48.176197 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 47971.605 | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 1180 1083 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 157.08452 [ih2].[UNITS], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 47971.605 | | | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 1181 1082 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[RECSTAT], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 1296 1080 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Hash Match(Aggregate, HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id]), RESIDUAL:((((((([um2].[P 1 1302 1078 Hash Match Aggregate HASH:([um2].[PLANNO], [um2].[MOVETYP], [ih2].[CONTDT], [ch2].[CONTTYP], [um2].[PBTxnTriggerId], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ppi].[prem_id]), RESIDUAL:((((((([um2].[PLANNO]=[um2].[PLANNO] AND [um2].[MOVETYP]=[um2].[MOVETYP]) AND [ih2 [Expr1015]=SUM([ih2].[TARGET]) 47630.641 0.0 3.2185972 55 297.18463 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id], [Expr1015] NULL PLAN_ROW 0 1.0 | |--Hash Match(Right Outer Join, HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[pla 1 1303 1302 Hash Match Right Outer Join HASH:([ppi].[ppitemproductcode], [ppi].[planno], [ppi].[elno])=([um2].[ProdCd], [um2].[PLANNO], [um2].[ELNO]), RESIDUAL:(([um2].[ProdCd]=[ppi].[ppitemproductcode] AND [ppi].[planno]=[um2].[PLANNO]) AND [ppi].[elno]=[um2].[ELNO]) NULL 47630.641 0.0 2.2111807 87 293.96603 [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi])) 1 1304 1303 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [ppi]) [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [ppi].[elno], [ppi].[planno], [ppi].[ppitemproductcode], [ppi].[prem_id] NULL PLAN_ROW 0 1.0 | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO])) 1 1305 1303 Merge Join Left Outer Join MANY-TO-MANY MERGE:([um2].[CONTNO], [um2].[PLANNO])=([ch2].[CONTNO], [ch2].[PLANNO]), RESIDUAL:([um2].[CONTNO]=[ch2].[CONTNO] AND [um2].[PLANNO]=[ch2].[PLANNO]) NULL 47630.641 12.395739 30.193527 219 291.48129 [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH) 1 1306 1305 Nested Loops Inner Join OUTER REFERENCES:([um2].[InvHstId]) WITH PREFETCH NULL 43842.074 0.0 0.18476358 202 200.11986 [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC)) 1 1308 1306 Sort Sort ORDER BY:([um2].[CONTNO] ASC, [um2].[PLANNO] ASC) NULL 44201.813 1.1261261E-2 3.1310048 50 55.226757 [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH) 1 1309 1308 Nested Loops Inner Join OUTER REFERENCES:([um2].[PBTxnTriggerId]) WITH PREFETCH NULL 44201.813 0.0 0.18476358 149 52.084492 [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId], [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um2].[PBTxnTriggerId] ASC)) 1 1311 1309 Sort Sort ORDER BY:([um2].[PBTxnTriggerId] ASC) NULL 44201.813 1.1261261E-2 3.1310048 42 5.0272756 [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] NULL PLAN_ROW 0 1.0 | | | | |--Hash Match(Inner Join, HASH:([FUND].[FUNDNO])=([um2].[FUNDNO])) 1 1312 1311 Hash Match Inner Join HASH:([FUND].[FUNDNO])=([um2].[FUNDNO]) NULL 44201.813 0.0 0.28711614 47 1.8850093 [um2].[InvHstId], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] NULL PLAN_ROW 0 1.0 | | | | |--Stream Aggregate(GROUP BY:([FUND].[FUNDNO])) 1 1313 1312 Stream Aggregate Aggregate GROUP BY:([FUND].[FUNDNO]) NULL 43.81076 0.0 5.8188476E-4 9 4.2942449E-2 [FUND].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD) 1 1314 1313 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUND].[PK_FUND]), WHERE:([FUND].[UWPIND]<>3) ORDERED FORWARD [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] 135.05882 4.2022943E-2 2.5889999E-4 121 4.2281844E-2 [FUND].[UWPIND], [FUND].[FUNDNO], [KeyCo18] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-20 : 10:09:58
|
| you lost me on part two.... man.... why would anyone want to write something like this???Go with the flow & have fun! Else fight the flow |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-20 : 10:13:28
|
part 9 (last - as last) | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3)) 1 1316 1312 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um2]), WHERE:(([um2].[MOVETYP]=41 OR [um2].[MOVETYP]=39) OR [um2].[MOVETYP]=3) [um2].[InvHstId], [um2].[FUNDNO], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] 49577.543 1.3183192 0.1142035 65 1.4325228 [um2].[InvHstId], [um2].[FUNDNO], [um2].[CONTNO], [um2].[ELNO], [um2].[ProdCd], [um2].[PLANNO], [um2].[MOVETYP], [um2].[PBTxnTriggerId] NULL PLAN_ROW 0 1.0 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED 1 1318 1309 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt2]), SEEK:([pbt2].[PBTxnTriggerId]=[um2].[PBTxnTriggerId]) ORDERED FORWARD [pbt2].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 46.872452 [pbt2].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 44201.813 | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD) 1 1319 1306 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih2]), SEEK:([ih2].[InvHstId]=[um2].[InvHstId]) ORDERED FORWARD [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] 1.0 3.2034749E-3 7.9603E-5 158 144.70834 [ih2].[TARGET], [ih2].[CONTDT], [ih2].[SERIESNO] NULL PLAN_ROW 0 44201.813 | |--Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD) 1 1320 1305 Index Scan Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch2]), ORDERED FORWARD [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[CONTTYP] 1.33041E+7 34.137577 14.634588 47 48.772167 [ch2].[PLANNO], [ch2].[CONTNO], [ch2].[CONTTYP] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([PID].[ppitemproductcode], [PID].[planno], [PID].[elno])=([um].[ProdCd], [um].[PLANNO], [um].[ELNO]), RESIDUAL:(([um].[ProdCd]=[PID].[ppitemproductcode] AND [PID].[planno]=[um].[PLA 1 1334 1077 Hash Match Right Outer Join HASH:([PID].[ppitemproductcode], [PID].[planno], [PID].[elno])=([um].[ProdCd], [um].[PLANNO], [um].[ELNO]), RESIDUAL:(([um].[ProdCd]=[PID].[ppitemproductcode] AND [PID].[planno]=[um].[PLANNO]) AND [PID].[elno]=[um].[ELNO]) NULL 78632.031 0.0 2.4477212 193 930.513 [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DEFPLANFEE], [ch] NULL PLAN_ROW 0 1.0 |--Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [PID])) 1 1335 1334 Table Scan Table Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] AS [PID]) [PID].[elno], [PID].[planno], [PID].[ppitemproductcode], [PID].[prem_id] 52825.0 0.21535628 5.8185998E-2 41 0.27354228 [PID].[elno], [PID].[planno], [PID].[ppitemproductcode], [PID].[prem_id] NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([sw].[PBTxnTriggerId])=([pbt].[PBTxnInstructionId])) 1 1336 1334 Hash Match Right Outer Join HASH:([sw].[PBTxnTriggerId])=([pbt].[PBTxnInstructionId]) NULL 78632.031 0.0 0.56425053 175 927.79169 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnTriggerTargetDate], [pl].[DE NULL PLAN_ROW 0 1.0 |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[Switch].[PK_Switch] AS [sw])) 1 1337 1336 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[Switch].[PK_Switch] AS [sw]) [sw].[PBTxnTriggerId], [sw].[SwitchType] 10792.0 8.6937398E-2 5.9748502E-3 67 0.1858245 [sw].[PBTxnTriggerId], [sw].[SwitchType] NULL PLAN_ROW 0 1.0 |--Hash Match(Left Outer Join, HASH:([um].[PLANNO], [um].[FUNDNO])=([fd].[PLANNO], [fd].[FUNDNO]), RESIDUAL:([um].[PLANNO]=[fd].[PLANNO] AND [um].[FUNDNO]=[fd].[FUNDNO])) 1 1338 1336 Hash Match Left Outer Join HASH:([um].[PLANNO], [um].[FUNDNO])=([fd].[PLANNO], [fd].[FUNDNO]), RESIDUAL:([um].[PLANNO]=[fd].[PLANNO] AND [um].[FUNDNO]=[fd].[FUNDNO]) NULL 78632.031 0.0 14.30425 184 927.04163 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId], [pbt].[PBTxn NULL PLAN_ROW 0 1.0 |--Hash Match(Right Outer Join, HASH:([ld].[PlanNo])=([um].[PLANNO]), RESIDUAL:([ld].[PlanNo]=[um].[PLANNO])) 1 1339 1338 Hash Match Right Outer Join HASH:([ld].[PlanNo])=([um].[PLANNO]), RESIDUAL:([ld].[PlanNo]=[um].[PLANNO]) NULL 76401.469 0.0 0.65637547 182 907.38458 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId], [pbt].[PBTxn NULL PLAN_ROW 0 1.0 | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[xSanLoanDet].[PK_xSanLoanDet] AS [ld]), WHERE:([ld].[LoanStatus]=1)) 1 1340 1339 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[xSanLoanDet].[PK_xSanLoanDet] AS [ld]), WHERE:([ld].[LoanStatus]=1) [ld].[LoanStatus], [ld].[PlanNo], [ld].[LoanRemBalance] 2044.7 6.8689615E-2 2.8659001E-3 100 0.07155551 [ld].[LoanStatus], [ld].[PlanNo], [ld].[LoanRemBalance] NULL PLAN_ROW 0 1.0 | |--Filter(WHERE:(((((((((((((((((((((([um].[MOVETYP]=1 AND (([ch].[RECSTAT]=4 OR [ch].[RECSTAT]=3) OR [ch].[RECSTAT]=2)) OR ([um].[MOVETYP]=2 AND ([ch].[RECSTAT]=6 OR [ch].[RECSTAT]=5))) OR ((([um].[ 1 1341 1339 Filter Filter WHERE:(((((((((((((((((((((([um].[MOVETYP]=1 AND (([ch].[RECSTAT]=4 OR [ch].[RECSTAT]=3) OR [ch].[RECSTAT]=2)) OR ([um].[MOVETYP]=2 AND ([ch].[RECSTAT]=6 OR [ch].[RECSTAT]=5))) OR ((([um].[MOVETYP]=52 OR [um].[MOVETYP]=12) OR [um].[MOVETYP]=5) AND [ih].[UN NULL 76401.469 0.0 1.0774825 442 906.6554 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId], [pbt].[PBTxn NULL PLAN_ROW 0 1.0 | |--Bookmark Lookup(BOOKMARK:([Bmk1076]), OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST] AS [ch])) 1 1342 1341 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1076]), OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST] AS [ch]) [ch].[RECSTAT], [ch].[CONTTYP], [ch].[ContHstCollectionFee], [ch].[ContHstContnDisc], [ch].[ContHstIntAmt], [ch].[ContHstAllocationFee], [ch].[CONTNO], [ch].[DATERCD], [ch].[BENAMT], [ch].[AdvisorFee], [ch].[EMPEEAMT] 109056.93 312.45688 0.11996263 442 905.57794 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId] NULL PLAN_ROW 0 1.0 | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([um].[PLANNO], [um].[CONTNO]) WITH PREFETCH) 1 1344 1342 Nested Loops Left Outer Join OUTER REFERENCES:([um].[PLANNO], [um].[CONTNO]) WITH PREFETCH NULL 109056.93 0.0 0.45585796 272 593.0011 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId] NULL PLAN_ROW 0 1.0 | |--Nested Loops(Inner Join, OUTER REFERENCES:([um].[PLANNO]) WITH PREFETCH) 1 1346 1344 Nested Loops Inner Join OUTER REFERENCES:([um].[PLANNO]) WITH PREFETCH NULL 100380.55 0.0 0.41966063 233 437.06625 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY:([um].[CONTNO] ASC, [um].[PLANNO] ASC)) 1 1348 1346 Sort Sort ORDER BY:([um].[CONTNO] ASC, [um].[PLANNO] ASC) NULL 100397.28 1.1261261E-2 7.6568494 91 411.57278 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um].[PBTxnTriggerId]) WITH PREFETCH) 1 1349 1348 Nested Loops Inner Join OUTER REFERENCES:([um].[PBTxnTriggerId]) WITH PREFETCH NULL 100397.28 0.0 0.41966063 185 403.90469 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT], [pbt].[PBTxnInstructionId] NULL PLAN_ROW 0 1.0 | | | |--Sort(ORDER BY:([um].[PBTxnTriggerId] ASC)) 1 1351 1349 Sort Sort ORDER BY:([um].[PBTxnTriggerId] ASC) NULL 100397.28 1.1261261E-2 7.6568494 79 349.32019 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([um].[InvHstId]) WITH PREFETCH) 1 1352 1351 Nested Loops Inner Join OUTER REFERENCES:([um].[InvHstId]) WITH PREFETCH NULL 100397.28 0.0 0.43214241 214 341.6521 [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO], [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT] NULL PLAN_ROW 0 1.0 | | | | |--Filter(WHERE:((((((((((((((((((((([um].[MOVETYP]=1 OR [um].[MOVETYP]=2) OR (([um].[MOVETYP]=52 OR [um].[MOVETYP]=12) OR [um].[MOVETYP]=5)) OR [um].[MOVETYP] 1 1354 1352 Filter Filter WHERE:((((((((((((((((((((([um].[MOVETYP]=1 OR [um].[MOVETYP]=2) OR (([um].[MOVETYP]=52 OR [um].[MOVETYP]=12) OR [um].[MOVETYP]=5)) OR [um].[MOVETYP]=53) OR [um].[MOVETYP]=51) OR [um].[MOVETYP]=50) OR [um].[MOVETYP]=44) OR [um].[MOVETYP]=43) OR [um].[MOVET NULL 103383.35 0.0 0.78642499 65 2.2189476 [um].[InvHstId], [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um]), ORDERED FORWARD) 1 1355 1354 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[UMVTRPTB].[PK_UMVTRPTB] AS [um]), ORDERED FORWARD [um].[InvHstId], [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO] 103750.0 1.3183192 0.1142035 65 1.4325228 [um].[InvHstId], [um].[ProdCd], [um].[PLANNO], [um].[CONTNO], [um].[PBTxnTriggerId], [um].[ELNO], [um].[MOVETYP], [um].[FUNDNO] NULL PLAN_ROW 0 1.0 | | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih]), SEEK:([ih].[InvHstId]=[um].[InvHstId]) ORDERED FORWARD) 1 1451 1352 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[INVHST].[PK_INVHST] AS [ih]), SEEK:([ih].[InvHstId]=[um].[InvHstId]) ORDERED FORWARD [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT] 1.0 3.2034749E-3 7.9603E-5 158 339.00101 [ih].[UNITS], [ih].[CONTDT], [ih].[SERIESNO], [ih].[FUNDNO], [ih].[InvHstId], [ih].[ServerCreatedDate], [ih].[INVDT] NULL PLAN_ROW 0 103383.35 | | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt]), SEEK:([pbt].[PBTxnTriggerId]=[um].[PBTxnTrigger 1 1452 1349 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PlanBasedTxnTrigger].[PK_PlanBasedTxnTrigger] AS [pbt]), SEEK:([pbt].[PBTxnTriggerId]=[um].[PBTxnTriggerId]) ORDERED FORWARD [pbt].[PBTxnInstructionId], [pbt].[PBTxnTriggerTargetDate] 1.0 3.2034749E-3 7.9603E-5 113 54.164825 [pbt].[PBTxnInstructionId], [pbt].[PBTxnTriggerTargetDate] NULL PLAN_ROW 0 100397.28 | | |--Clustered Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PLAN_].[PK_PLAN_] AS [pl]), SEEK:([pl].[PLANNO]=[um].[PLANNO]) ORDERED FORWARD) 1 1453 1346 Clustered Index Seek Clustered Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[PLAN_].[PK_PLAN_] AS [pl]), SEEK:([pl].[PLANNO]=[um].[PLANNO]) ORDERED FORWARD [pl].[DEFPLANFEE] 1.0 3.2034749E-3 7.9603E-5 148 25.073812 [pl].[DEFPLANFEE] NULL PLAN_ROW 0 100397.28 | |--Index Seek(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch]), SEEK:([ch].[CONTNO]=[um].[CONTNO] AND [ch].[PLANNO]=[um].[PLANNO] 1 1454 1344 Index Seek Index Seek OBJECT:([ProdCopy_20040906_Lamda].[dbo].[CONTHST].[ix02_conthst_contno_planno_recstat_conttyp] AS [ch]), SEEK:([ch].[CONTNO]=[um].[CONTNO] AND [ch].[PLANNO]=[um].[PLANNO]) ORDERED FORWARD [Bmk1076] 1.0864348 3.2034749E-3 7.9702681E-5 47 155.47897 [Bmk1076] NULL PLAN_ROW 0 100380.55 |--Clustered Index Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUNDIST].[PK_FUNDIST] AS [fd])) 1 1575 1338 Clustered Index Scan Clustered Index Scan OBJECT:([ProdCopy_20040906_Lamda].[dbo].[FUNDIST].[PK_FUNDIST] AS [fd]) [fd].[FUNDNO], [fd].[PLANNO] 757196.0 4.5198007 0.8329941 74 5.3527946 [fd].[FUNDNO], [fd].[PLANNO] NULL PLAN_ROW 0 1.0 *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-20 : 10:54:31
|
| it's funny, you never know, all of this just might be perfectly logical and required and optimized as good as possible (w/o using temp tables and all that) .... it's hard to evaluate !!!I can't tell if lots of it is repeating or not, but it would obviously greatly benefit from using Views to break things up. Maybe that would be my approach -- take each subquery, one at a time, and create a view of it and figure out how that works. take it apart, piece by piece, and then put it back together. maybe lots of the SQL is redundant and can be put into a few views.also -- that might help you to add some temp tables or something to replace certain clauses, which may greatly help performance. In cases like this, in which this much processing is required, I would expect that building a few temp tables in the beginning might greatly improve performance.just some thoughts -- good luck !- Jeff |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-10-21 : 00:55:12
|
| Well Regan - I hope this ex-coleague - doesn't find out how you are slating his code all over the internet!If I was you I'd charge him at least 2 cases of beer - for optimizing the query.I tend to agree with Jeff here - that views and temp tables might be a good place to start, but I'm sure your approach - is already doing something like that.Oh BTW - that was a pretty neat way of rapidly increasing your post count.Duane. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-21 : 01:50:41
|
| Well, I think there must be some sort of VIEW in there 'coz I can't find the table MIS_PPI_PL_EL_XSP which is giving rise to a few table scans:Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP] Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Table Scan(OBJECT:([ProdCopy_20040906_Lamda].[dbo].[MIS_PPI_PL_EL_XSP]Kristen |
|
|
|
|
|
|
Scariest SQL you've seen? Just for FYI