Showing posts with label fundname. Show all posts
Showing posts with label fundname. Show all posts

Friday, March 9, 2012

Pls help me tuning SQL Command

SELECT Fund.FundName, Fm.FundManagerName, Fm.Attention1, Hbo.ValueDate, Hbo.ValueTime, Hbo.FundManagerCode, Hbo.FundCode, Hbo.InstrumentNo, Hbo.IssueCode, Hbo.SecurityCode, Hbo.Guarantor, Hbo.GuaranteeType, Hbo.Maturity, Hbo.CouponRate, Hbo.Face_Amt, Hbo.RedemptionYield, Hbo.N_AmortiseCost, Hbo.N_MarketValue, Hbo.N_AccruedInterest, Hbo.H_MarketYield, Hbo.RemainingDays, Hbo.MainGroup103, Adjust.T_AmortiseCost, Adjust.T_AccruedInterest, Adjust.T_MarketValue,
(select SecLongDesc From SecurityType Where SecCode=Hbo.MainGroup103 ) as SecLongDesc,
(Select SUM(HBOadjust.T_AmortiseCost) From HBOAdjust Where (HBOAdjust.FundCode=Hbo.FundCode)
and (HBOAdjust.ValueDate=Hbo.ValueDate)
and (HBOAdjust.ProductCode<>'100')) As aAmo,
(Select SUM(HBOadjust.T_AccruedInterest) From HBOAdjust Where (HBOAdjust.FundCode=Hbo.FundCode)
and (HBOAdjust.ValueDate=Hbo.ValueDate)
and (HBOAdjust.ProductCode<>'100')) as aInt,
(Select SUM(HBOadjust.T_MarketValue) From HBOAdjust Where (HBOAdjust.FundCode=Hbo.FundCode)
and (HBOAdjust.ValueDate=Hbo.ValueDate)
and (HBOAdjust.ProductCode<>'100')) as aMKT
FROM dbo.HBOData Hbo, dbo.Fund Fund, dbo.FundManager Fm, dbo.HBOAdjust Adjust
WHERE (Hbo.FundCode *= Fund.FundCode)
AND (Hbo.FundManagerCode *= Fm.FundManagerCode)
AND (Hbo.FundCode *= Adjust.FundCode)
AND (Hbo.ValueDate *= Adjust.ValueDate)
AND (Hbo.MainGroup103 *= Adjust.ProductCode)
AND ( (Hbo.FundCode = 'V01001')
AND (Hbo.ValueDate = '020606')
AND (Hbo.Report103 = 'Y') )
ORDER BY Hbo.MainGroup103, Hbo.Guarantor, Hbo.SecurityCode
**************************************************
This is command . I wanna tuning 'cos it used hight CPU and IO.
How Can I tune. Thank youIt would be helpful to know what version of SQL server you are using.|||I may not have this exactly right but if you are using v7 or 2k try:

SELECT Fund.FundName, Fm.FundManagerName, Fm.Attention1, Hbo.ValueDate, Hbo.ValueTime, Hbo.FundManagerCode, Hbo.FundCode
, Hbo.InstrumentNo, Hbo.IssueCode, Hbo.SecurityCode, Hbo.Guarantor, Hbo.GuaranteeType, Hbo.Maturity, Hbo.CouponRate
, Hbo.Face_Amt, Hbo.RedemptionYield, Hbo.N_AmortiseCost, Hbo.N_MarketValue, Hbo.N_AccruedInterest, Hbo.H_MarketYield
, Hbo.RemainingDays, Hbo.MainGroup103, Adjust.T_AmortiseCost, Adjust.T_AccruedInterest, Adjust.T_MarketValue
, St.SecLongDesc as SecLongDesc
, tmp.aAmo
, tmp.aInt
, tmp.aMKT
FROM (Select Adjust.FundCode
, Adjust.ValueDate
, Adjust.ProductCode
, SUM(adjust.T_AmortiseCost) As aAmo
, SUM(adjust.T_AccruedInterest) as aInt
, SUM(adjust.T_MarketValue) as aMKT
FROM dbo.HBOData Hbo
join dbo.HBOAdjust Adjust on Hbo.FundCode = Adjust.FundCode
AND Hbo.ValueDate = Adjust.ValueDate
AND Hbo.MainGroup103 = Adjust.ProductCode
WHERE Hbo.FundCode = 'V01001'
AND Hbo.ValueDate = '020606'
AND Hbo.Report103 = 'Y'
and Adjust.ProductCode <> '100'
group by Adjust.FundCode, Adjust.ValueDate, Adjust.ProductCode) as t1
right join dbo.HBOData Hbo on t1.FundCode = hbo.FundCode
and t1.ValueDate = hbo.ValueDate
and t1.ProductCode = hbo.MainGroup103
left join dbo.SecurityType St on Hbo.MainGroup103 = St.SecCode
left join dbo.Fund Fund on Hbo.FundCode = Fund.FundCode
left join dbo.FundManager Fm on Hbo.FundManagerCode = Fm.FundManagerCode
left join dbo.HBOAdjust Adjust on Hbo.FundCode = Adjust.FundCode
AND Hbo.ValueDate = Adjust.ValueDate
AND Hbo.MainGroup103 = Adjust.ProductCode
WHERE Hbo.FundCode = 'V01001'
AND Hbo.ValueDate = '020606'
AND Hbo.Report103 = 'Y'
ORDER BY Hbo.MainGroup103, Hbo.Guarantor, Hbo.SecurityCode|||Paste your code into a new view and run it. The sql-server (at least the 2000-version!?!) will then rearrange and optimize your sql-statement for you...|||thank you :)