SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making my select faster

    This is all new to me, (sql) and now I am trying to read up on how to make Selects more efficent and faster.

    This is one of my main statements that takes most of the time (68%)

    Code:
    SELECT     CONVERT(varchar(4),HDR.MarketingClockNumber), HDR.PlantNumber, HDR.QuoteNumber, HDR.EffectiveStartDate, HDR.CustomerNumber, 
                          	HDR.CustomerName, (ITM.NetUnitPrice + SUM(ISNULL(ITX.Adder, 0))) * ITM.Quantity * ITM.DiscountMultiplier AS QuoteNetValue, 
    		HDR.YourInquiry, ITM.LineItem  
    FROM         DataWarehouse.dbo.QuoteHDR HDR  LEFT OUTER JOIN DataWarehouse.dbo.QuoteITM ITM ON HDR.PlantNumber = ITM.PlantNumber AND HDR.QuoteNumber = ITM.QuoteNumber LEFT OUTER JOIN
                         		DataWarehouse.dbo.QuoteITX ITX ON HDR.PlantNumber = ITX.PlantNumber AND HDR.QuoteNumber = ITX.QuoteNumber AND ITM.LineItem = ITX.ItemNumber
    	WHERE     (HDR.PlantNumber = '12' OR
                          HDR.PlantNumber = '18' OR
                          HDR.PlantNumber = '19' OR
                          HDR.PlantNumber = '22' OR
                          HDR.PlantNumber = '23') AND    (ITX.PlantNumber = '12' OR
                          ITX.PlantNumber = '18' OR
                          ITX.PlantNumber = '19' OR
                          ITX.PlantNumber = '22' OR
                          ITX.PlantNumber = '23') AND   (ITX.PlantNumber = '12' OR
                          ITM.PlantNumber = '18' OR
                          ITM.PlantNumber = '19' OR
                          ITM.PlantNumber = '22' OR
                          ITM.PlantNumber = '23') AND (HDR.EffectiveStartDate BETWEEN CONVERT(DATETIME, '2004-10-25 00:00:00', 102) 
    		AND CONVERT(DATETIME, '2005-6-30 00:00:00', 102)) 
    		AND (HDR.QuoteNumber NOT IN
    	                          (SELECT QuoteNumber  FROM QuoteFeedback.dbo.QuoteTracking   ) )  GROUP BY HDR.MarketingClockNumber, HDR.PlantNumber, HDR.QuoteNumber, HDR.EffectiveStartDate, HDR.CustomerNumber, 
    		                      HDR.CustomerName, HDR.YourInquiry, ITM.DiscountMultiplier, ITM.Quantity, ITM.LineItem, ITM.NetUnitPrice
    For indexes, i have clustered, Plant Number, QuoteNumber, and LineItem. Here is the Execution Plan.

    QuoteHDR has 0.2 Million
    QuoteITM has 0.5 Million
    QuoteITX has 1.5 Million

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'm not gonna even try to optimize that

    however, i do want to point out a coupla things

    if you have FROM t1 LEFT OUTER JOIN t2 ON t1.foo=t2.bar and then in the where clause you specify WHERE t2.qux='fap', then you in effect turn it into an INNER join

    this is because whenever you have a t1 row with no matching t2 row, the t1 row is returned in the result set with nulls in all the t2 columns

    but if you then test one of the t2 columns to be equal to something, the entire result row drops out (because null is not equal to anything)

    therefore, try changing the LEFT OUTER joins to INNER and see if that helps

    also, you don't need to explicitly CONVERT those strings to datetime

    finally, instead of this --
    Code:
     AND (HDR.QuoteNumber NOT IN
    	      (SELECT QuoteNumber  
    	         FROM QuoteFeedback.dbo.QuoteTracking  ) )
    try this --
    Code:
     AND NOT EXISTS
    	      (SELECT 1  
    	         FROM QuoteFeedback.dbo.QuoteTracking
    	        where QuoteNumber
    	            = HDR.QuoteNumber )
    P.S> this shouldn't be in the MySQL forum, perhaps a moderator can move it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What forum should of I put it in, for future reference? Sorry about that. Thanks for your comments anyways though.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it sure looks like microsoft sql server to me, so it should go into the Databases forum (the MySQL subforum of the Databases forum is the only specialized subforum we have)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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