Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Thailand
    Posts
    42

    Cool Unanswered: 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 you

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    It would be helpful to know what version of SQL server you are using.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I may not have this exactly right but if you are using v7 or 2k try:

    Code:
    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
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    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...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Feb 2003
    Location
    Thailand
    Posts
    42

    Talking

    thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •