Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the execution plan, so please help me tuned the performance on the query.

SELECT TOP 20 CustomerPrimaryExtID,
          Max(POSTimeStamp)     AS TransactionDate,
          ExtLocationCode,
          0                     AS RedemptionAmount,
          0                     AS RedemptionCount,
          TerminalNum,
          TH.LogixTransNum,
          POSTransNum           AS TransNum,
          0                     AS DetailRecords,
          CustomerTypeID,
          PresentedCustomerID,
          PresentedCardTypeID,
          HHID,
          Replayed,
          0                     AS TransContext,
          isnull(TransTotal, 0) AS TransTotal
FROM   TransHist AS TH WITH(nolock)
   LEFT OUTER JOIN
(
    SELECT 
        LogixTransNum
    FROM
        TransRedemption with(nolock)
) TR2
ON TH.LogixTransNum = TR2.LogixTransNum
WHERE  ( ( ( CustomerPrimaryExtID IN ( '' )
         AND HHID IS NULL )
        OR HHID = '0000000250000013408'
           AND CustomerTypeID <> 1 )
      OR ( CustomerPrimaryExtID = '0000000250000013408'
           AND CustomerTypeID = 1 ) )
           AND TR2.LogixTransNum IS NULL

GROUP  BY CustomerPrimaryExtID,
      HHID,
      CustomerTypeID,
      PresentedCustomerID,
      PresentedCardTypeID,
      TH.LogixTransNum,
      POSTransNum,
      TerminalNum,
      ExtLocationCode,
      Replayed,
      TransTotal
ORDER  BY TransactionDate DESC

enter image description here

share|improve this question
    
AND TR2.LogixTransNum IS NULL -- this seems suspect. If this is correct, try using NOT EXISTS instead of the LEFT OUTER JOIN + filter construct. –  Jon Seigel Oct 2 '13 at 15:21
    
@JonSeigel are you saying the NULL will cause the optimizer to do a scan instead of a lookup? Just asking out of curiosity. –  Max Vernon Oct 2 '13 at 15:22
    
@Max it will usually have to materialize the join and then filter, unlike a NOT EXISTS. sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join –  Aaron Bertrand Oct 2 '13 at 15:24
    
@Max: Doing a join to an independent subquery will usually (at least in my experience) do the entire subquery first (satisfied by the index scan in this case), and then join that result to the outer query. –  Jon Seigel Oct 2 '13 at 15:24
1  
Thanks guys - I have been in the habit of doing the LEFT JOIN - IS NULL thing quite often. –  Max Vernon Oct 2 '13 at 15:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.