T-SQL Query Performance - SQL Server 2008 R2

Question

I have a SQL query which is performing badly. It is taking about 2 minutes to return the result set.

Is there a better way to rewrite the query? I know about CTE, but never used it before.

Please help.

select 
    CustomerPK, 
    LocalID, 
    ExternalID, 
    EarnedDate, 
    QtyEarned, 
    QtyUsed, 
    Value, 
    ServerSerial, 
    LastLocationID,
    SVS.Description as Status, 
    SVS.PhraseID as StatusPhraseID,
    (select SUM(IsNull(QtyEarned,0)) - SUM(IsNull(QtyUsed,0)) 
     from SVHistory SVH2 with (NoLock) 
     where CustomerPK=18653237 and SVH2.LocalID = SVH.LocalID and SVH2.ServerSerial=SVH.ServerSerial
    ) as QtyAvail,
    AdminUserID, 
    ExpireDate, 
    PresentedCustomerID, 
    PresentedCardTypeID, 
    ResolvedCustomerID, 
    HHID, 
    Replayed, 
    ReplayedDate
from 
        SVHistory SVH with (NoLock) 
inner join 
        StoredValueStatus SVS with (NoLock) on SVS.StatusID=SVH.StatusFlag 
where 
    LastLocationID <> -8 
    and CustomerPK = 18653237 
    and SVProgramID = 112 
    and LastUpdate between '2013-05-27 00:00:00' and '2013-06-26 23:59:59' 
    and Deleted = 0 
order by 
    EarnedDate DESC, 
    LocalID, 
    ExternalID, 
    Status;

You should look at the execution plan (and possibly post it here) - it'll tell you where you're losing your time.... — marc_s
– marc_s, Commented Jul 1, 2013 at 19:20
The is just strange. You are reporting individual QtyEarned QtyUsed and AND for each line a sum of aggregate difference. — paparazzo
– paparazzo, Commented Jul 1, 2013 at 19:33

Anon · Accepted Answer · 2013-07-01 19:06:49Z

select 
    CustomerPK, 
    LocalID, 
    ExternalID, 
    EarnedDate, 
    QtyEarned, 
    QtyUsed, 
    Value, 
    ServerSerial, 
    LastLocationID,
    SVS.Description as Status, 
    SVS.PhraseID as StatusPhraseID,
    IsNull(SUM(QtyEarned) OVER(PARTITION BY CustomerPK,LocalID,ServerSerial),0) -
      IsNull(SUM(QtyUsed) OVER(PARTITION BY CustomerPK,LocalID,ServerSerial),0) as QtyAvail,
    AdminUserID, 
    ExpireDate, 
    PresentedCustomerID, 
    PresentedCardTypeID, 
    ResolvedCustomerID, 
    HHID, 
    Replayed, 
    ReplayedDate
from 
SVHistory SVH with (NoLock) 
inner join StoredValueStatus SVS with (NoLock) on SVS.StatusID=SVH.StatusFlag 
where 
    LastLocationID <> -8 
    and CustomerPK= 18653237 
    and SVProgramID=112 
    and LastUpdate between '2013-05-27 00:00:00' and '2013-06-26 23:59:59' 
    and Deleted=0 
order by 
    EarnedDate DESC, 
    LocalID, 
    ExternalID, 
    Status;

paparazzo · Accepted Answer · 2013-07-01 21:58:19Z

0

select 
    CustomerPK, 
    LocalID, 
    ExternalID, 
    EarnedDate, 
    QtyEarned, 
    QtyUsed, 
    Value, 
    ServerSerial, 
    LastLocationID,
    SVS.Description as Status, 
    SVS.PhraseID as StatusPhraseID,
    netT.net,
    AdminUserID, 
    ExpireDate, 
    PresentedCustomerID, 
    PresentedCardTypeID, 
    ResolvedCustomerID, 
    HHID, 
    Replayed, 
    ReplayedDate
from 
        SVHistory SVH with (NoLock) 
inner join 
        StoredValueStatus SVS with (NoLock) on SVS.StatusID=SVH.StatusFlag
inner merge join  
   ( select LocalID, ServerSerial, SUM(IsNull(QtyEarned,0)) - SUM(IsNull(QtyUsed,0)) as net
     from SVHistory 
     where CustomerPK=18653237 
     group by LocalID, ServerSerial
   ) netT
  on netT.LocalID = SVH.LocalID 
 and netT.ServerSerial = SVH.ServerSerial
where 
    LastLocationID <> -8 
    and CustomerPK = 18653237 
    and SVProgramID = 112 
    and LastUpdate between '2013-05-27 00:00:00' and '2013-06-26 23:59:59' 
    and Deleted = 0

edited Jul 1, 2013 at 21:58

answered Jul 1, 2013 at 21:37

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

2 Comments

RBarryYoung Over a year ago

Spelling: "inner merger join"

paparazzo Over a year ago

@RBarryYoung I got inner right. That syntax was clearly by hand and not tested.

Collectives™ on Stack Overflow

T-SQL Query Performance - SQL Server 2008 R2

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related