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 :)
Friday, March 9, 2012
Pls help me tuning SQL Command
Labels:
attention1,
command,
database,
fund,
fundcode,
fundmanagercode,
fundmanagername,
fundname,
hbo,
instrumentno,
microsoft,
mysql,
oracle,
pls,
select,
server,
sql,
tuning,
valuedate,
valuetime
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment