badly perfoming query when IS NOT NULL used in the WHERE clause

• solus

  Hall of Fame

  Points: 3495

  More actions

  October 15, 2018 at 3:34 am

  #375219

  I have a query that returns results in under a second (115 rows) if I use

  IS NULL

  in the WHERE clause, but when I change it to
  IS NOT NULL
  its taking 1 minute 16 seconds (129 rows)
  the column im testing for NULL is indexed, I just cant figure out why the 2 WHERe clauses should differs so greatly, can anyone see where Im going wrong here ?

  select
position.counterparty,
position.positiontype,
contract.contract,
Trade.cstcontractstart AS ContractStartDate,
Trade.cstcontractend as ContractEndDate,
Trade.trade AS TradeID,
Trade.tradedate AS TradeDate,
powerquantity.begtime AS StartDateTime,
powerquantity.endtime AS EndDateTime,
CASE    WHEN Cast((powerquantity.endtime - powerquantity.begtime) as int) <= 31 THEN 'M'
        WHEN Cast((powerquantity.endtime - powerquantity.begtime) as int) > 31 AND Cast((powerquantity.endtime - powerquantity.begtime) as int) <= 93 THEN 'Q'
        WHEN Cast((powerquantity.endtime - powerquantity.begtime) as int) > 93 AND Cast((powerquantity.endtime - powerquantity.begtime) as int) <= 183 THEN 'S'
ELSE 'M'
END AS BlockDescription,
Cast((powerquantity.endtime - powerquantity.begtime) as int) AS Days,
Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0 AS HoursInPeriod,
dbo.WorkingDays(powerquantity.begtime, powerquantity.endtime) AS WorkingDays,
dbo.WorkingDays(powerquantity.begtime, powerquantity.endtime) * 24 AS WorkingHours,
SUM(CASE WHEN LEFT(powerquantity.tsperiod,2) = 'WD' THEN 1
            ELSE 0
        END) AS WD_EFA_Periods,
SUM(CASE WHEN LEFT(powerquantity.tsperiod,2) = 'WE' THEN 1
            ELSE 0
        END) AS WE_EFA_Periods,
Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0 - (dbo.WorkingDays(powerquantity.begtime, powerquantity.endtime) * 24) AS WeekendHours,
CASE WHEN fee.feemode = 'FIXED' THEN
    CASE WHEN position.unit = 'MW' THEN
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he1) * fee.pricediff
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he25) * fee.pricediff ELSE (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he2) * fee.pricediff END
            WHEN 2 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he3) * fee.pricediff
            WHEN 3 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he4) * fee.pricediff
            WHEN 4 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he5) * fee.pricediff
            WHEN 5 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he6) * fee.pricediff
            WHEN 6 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he7) * fee.pricediff
            WHEN 7 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he8) * fee.pricediff
            WHEN 8 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he9) * fee.pricediff
            WHEN 9 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he10) * fee.pricediff
            WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he11) * fee.pricediff
            WHEN 11 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he12) * fee.pricediff
            WHEN 12 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he13) * fee.pricediff
            WHEN 13 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he14) * fee.pricediff
            WHEN 14 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he15) * fee.pricediff
            WHEN 15 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he16) * fee.pricediff
            WHEN 16 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he17) * fee.pricediff
            WHEN 17 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he18) * fee.pricediff
            WHEN 18 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he19) * fee.pricediff
            WHEN 19 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he20) * fee.pricediff
            WHEN 20 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he21) * fee.pricediff
            WHEN 21 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he22) * fee.pricediff
            WHEN 22 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he23) * fee.pricediff
            WHEN 23 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he24) * fee.pricediff
    END    
    ELSE
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN MAX(powerquantity.he1) * fee.pricediff
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) * fee.pricediff ELSE MAX(powerquantity.he2) * fee.pricediff END
            WHEN 2 THEN MAX(powerquantity.he3) * fee.pricediff
            WHEN 3 THEN MAX(powerquantity.he4) * fee.pricediff
            WHEN 4 THEN MAX(powerquantity.he5) * fee.pricediff
            WHEN 5 THEN MAX(powerquantity.he6) * fee.pricediff
            WHEN 6 THEN MAX(powerquantity.he7) * fee.pricediff
            WHEN 7 THEN MAX(powerquantity.he8) * fee.pricediff
            WHEN 8 THEN MAX(powerquantity.he9) * fee.pricediff
            WHEN 9 THEN MAX(powerquantity.he10) * fee.pricediff
            WHEN 10 THEN MAX(powerquantity.he11) * fee.pricediff
            WHEN 11 THEN MAX(powerquantity.he12) * fee.pricediff
            WHEN 12 THEN MAX(powerquantity.he13) * fee.pricediff
            WHEN 13 THEN MAX(powerquantity.he14) * fee.pricediff
            WHEN 14 THEN MAX(powerquantity.he15) * fee.pricediff
            WHEN 15 THEN MAX(powerquantity.he16) * fee.pricediff
            WHEN 16 THEN MAX(powerquantity.he17) * fee.pricediff
            WHEN 17 THEN MAX(powerquantity.he18) * fee.pricediff
            WHEN 18 THEN MAX(powerquantity.he19) * fee.pricediff
            WHEN 19 THEN MAX(powerquantity.he20) * fee.pricediff
            WHEN 20 THEN MAX(powerquantity.he21) * fee.pricediff
            WHEN 21 THEN MAX(powerquantity.he22) * fee.pricediff
            WHEN 22 THEN MAX(powerquantity.he23) * fee.pricediff
            WHEN 23 THEN MAX(powerquantity.he24) * fee.pricediff
    END
END
ELSE
    CASE WHEN position.unit = 'MW' THEN
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he1) * fee.priceindex
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he25) * fee.priceindex ELSE (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he2) * fee.priceindex END
            WHEN 2 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he3) * fee.priceindex
            WHEN 3 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he4) * fee.priceindex
            WHEN 4 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he5) * fee.priceindex
            WHEN 5 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he6) * fee.priceindex
            WHEN 6 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he7) * fee.priceindex
            WHEN 7 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he8) * fee.priceindex
            WHEN 8 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he9) * fee.priceindex
            WHEN 9 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he10) * fee.priceindex
            WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he11) * fee.priceindex
            WHEN 11 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he12) * fee.priceindex
            WHEN 12 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he13) * fee.priceindex
            WHEN 13 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he14) * fee.priceindex
            WHEN 14 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he15) * fee.priceindex
            WHEN 15 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he16) * fee.priceindex
            WHEN 16 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he17) * fee.priceindex
            WHEN 17 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he18) * fee.priceindex
            WHEN 18 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he19) * fee.priceindex
            WHEN 19 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he20) * fee.priceindex
            WHEN 20 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he21) * fee.priceindex
            WHEN 21 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he22) * fee.priceindex
            WHEN 22 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he23) * fee.priceindex
            WHEN 23 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he24) * fee.priceindex
    END
    ELSE
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN MAX(powerquantity.he1) * fee.priceindex
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) * fee.priceindex ELSE MAX(powerquantity.he2) * fee.priceindex END
            WHEN 2 THEN MAX(powerquantity.he3) * fee.priceindex
            WHEN 3 THEN MAX(powerquantity.he4) * fee.priceindex
            WHEN 4 THEN MAX(powerquantity.he5) * fee.priceindex
            WHEN 5 THEN MAX(powerquantity.he6) * fee.priceindex
            WHEN 6 THEN MAX(powerquantity.he7) * fee.priceindex
            WHEN 7 THEN MAX(powerquantity.he8) * fee.priceindex
            WHEN 8 THEN MAX(powerquantity.he9) * fee.priceindex
            WHEN 9 THEN MAX(powerquantity.he10) * fee.priceindex
            WHEN 10 THEN MAX(powerquantity.he11) * fee.priceindex
            WHEN 11 THEN MAX(powerquantity.he12) * fee.priceindex
            WHEN 12 THEN MAX(powerquantity.he13) * fee.priceindex
            WHEN 13 THEN MAX(powerquantity.he14) * fee.priceindex
            WHEN 14 THEN MAX(powerquantity.he15) * fee.priceindex
            WHEN 15 THEN MAX(powerquantity.he16) * fee.priceindex
            WHEN 16 THEN MAX(powerquantity.he17) * fee.priceindex
            WHEN 17 THEN MAX(powerquantity.he18) * fee.priceindex
            WHEN 18 THEN MAX(powerquantity.he19) * fee.priceindex
            WHEN 19 THEN MAX(powerquantity.he20) * fee.priceindex
            WHEN 20 THEN MAX(powerquantity.he21) * fee.priceindex
            WHEN 21 THEN MAX(powerquantity.he22) * fee.priceindex
            WHEN 22 THEN MAX(powerquantity.he23) * fee.priceindex
            WHEN 23 THEN MAX(powerquantity.he24) * fee.priceindex
    END
END
END AS BlockCostGBP,
CASE DATEPART(HOUR, powerquantity.begtime)
   WHEN 0 THEN MAX(powerquantity.he1)
        WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) ELSE MAX(powerquantity.he2) END
        WHEN 2 THEN MAX(powerquantity.he3)
        WHEN 3 THEN MAX(powerquantity.he4)
        WHEN 4 THEN MAX(powerquantity.he5)
        WHEN 5 THEN MAX(powerquantity.he6)
        WHEN 6 THEN MAX(powerquantity.he7)
        WHEN 7 THEN MAX(powerquantity.he8)
        WHEN 8 THEN MAX(powerquantity.he9)
        WHEN 9 THEN MAX(powerquantity.he10)
        WHEN 10 THEN MAX(powerquantity.he11)
        WHEN 11 THEN MAX(powerquantity.he12)
        WHEN 12 THEN MAX(powerquantity.he13)
        WHEN 13 THEN MAX(powerquantity.he14)
        WHEN 14 THEN MAX(powerquantity.he15)
        WHEN 15 THEN MAX(powerquantity.he16)
        WHEN 16 THEN MAX(powerquantity.he17)
        WHEN 17 THEN MAX(powerquantity.he18)
        WHEN 18 THEN MAX(powerquantity.he19)
        WHEN 19 THEN MAX(powerquantity.he20)
        WHEN 20 THEN MAX(powerquantity.he21)
        WHEN 21 THEN MAX(powerquantity.he22)
        WHEN 22 THEN MAX(powerquantity.he23)
        WHEN 23 THEN MAX(powerquantity.he24)
END AS Volume,
CASE WHEN position.unit = 'MW' THEN
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he1)
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he25) ELSE (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he2) END
            WHEN 2 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he3)
            WHEN 3 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he4)
            WHEN 4 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he5)
            WHEN 5 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he6)
            WHEN 6 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he7)
            WHEN 7 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he8)
            WHEN 8 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he9)
            WHEN 9 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he10)
            WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he11)
            WHEN 11 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he12)
            WHEN 12 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he13)
            WHEN 13 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he14)
            WHEN 14 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he15)
            WHEN 15 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he16)
            WHEN 16 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he17)
            WHEN 17 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he18)
            WHEN 18 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he19)
            WHEN 19 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he20)
            WHEN 20 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he21)
            WHEN 21 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he22)
            WHEN 22 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he23)
            WHEN 23 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he24)
    END    
    ELSE
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN MAX(powerquantity.he1)
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) ELSE MAX(powerquantity.he2) END
            WHEN 2 THEN MAX(powerquantity.he3)
            WHEN 3 THEN MAX(powerquantity.he4)
            WHEN 4 THEN MAX(powerquantity.he5)
            WHEN 5 THEN MAX(powerquantity.he6)
            WHEN 6 THEN MAX(powerquantity.he7)
            WHEN 7 THEN MAX(powerquantity.he8)
            WHEN 8 THEN MAX(powerquantity.he9)
            WHEN 9 THEN MAX(powerquantity.he10)
            WHEN 10 THEN MAX(powerquantity.he11)
            WHEN 11 THEN MAX(powerquantity.he12)
            WHEN 12 THEN MAX(powerquantity.he13)
            WHEN 13 THEN MAX(powerquantity.he14)
            WHEN 14 THEN MAX(powerquantity.he15)
            WHEN 15 THEN MAX(powerquantity.he16)
            WHEN 16 THEN MAX(powerquantity.he17)
            WHEN 17 THEN MAX(powerquantity.he18)
            WHEN 18 THEN MAX(powerquantity.he19)
            WHEN 19 THEN MAX(powerquantity.he20)
            WHEN 20 THEN MAX(powerquantity.he21)
            WHEN 21 THEN MAX(powerquantity.he22) * fee.pricediff
            WHEN 22 THEN MAX(powerquantity.he23) * fee.pricediff
            WHEN 23 THEN MAX(powerquantity.he24) * fee.pricediff
    END
END AS MWh,
position.unit,
powerquantity.timeunit,
CASE WHEN fee.feemode = 'FIXED' THEN
    fee.pricediff        
ELSE
    fee.priceindex            
END AS GBPMWh,
fee.feemode
from Trade
inner join position on trade.trade = position.trade
inner join powerposition on position.position = powerposition.position
inner join powerquantity on powerposition.position = powerquantity.position and powerposition.posdetail = powerquantity.posdetail
inner join contract on position.contract = contract.contract and contract.contracttype in ('ETSA','SETSA')
inner join fee on position.position = fee.dbvalue and fee.dbcolumn = 'POSITION' and fee.feemethod = 'COMMODITY PRICE'
where contract.contract = '110156'
and powerquantity.posstatus = 1
and position.loadshape is null
group by
powerquantity.begtime,
powerquantity.endtime,
Trade.trade,
Trade.tradedate,
fee.pricediff,
fee.priceindex,
position.unit,
contract.contract,
fee.feemode,
powerquantity.timeunit,
position.counterparty,
position.positiontype,
Trade.cstcontractstart,
Trade.cstcontractend
order by powerquantity.begtime,powerquantity.endtime
go

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 15, 2018 at 6:04 am

  #2009573

  solus - Monday, October 15, 2018 3:34 AM

  I have a query that returns results in under a second (115 rows) if I use

  IS NULL

  in the WHERE clause, but when I change it to
  IS NOT NULL
  its taking 1 minute 16 seconds (129 rows)
  the column im testing for NULL is indexed, I just cant figure out why the 2 WHERe clauses should differs so greatly, can anyone see where Im going wrong here ?

  select
position.counterparty,
position.positiontype,
contract.contract,
Trade.cstcontractstart AS ContractStartDate,
Trade.cstcontractend as ContractEndDate,
Trade.trade AS TradeID,
Trade.tradedate AS TradeDate,
powerquantity.begtime AS StartDateTime,
powerquantity.endtime AS EndDateTime,
CASE    WHEN Cast((powerquantity.endtime - powerquantity.begtime) as int) <= 31 THEN 'M'
        WHEN Cast((powerquantity.endtime - powerquantity.begtime) as int) > 31 AND Cast((powerquantity.endtime - powerquantity.begtime) as int) <= 93 THEN 'Q'
        WHEN Cast((powerquantity.endtime - powerquantity.begtime) as int) > 93 AND Cast((powerquantity.endtime - powerquantity.begtime) as int) <= 183 THEN 'S'
ELSE 'M'
END AS BlockDescription,
Cast((powerquantity.endtime - powerquantity.begtime) as int) AS Days,
Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0 AS HoursInPeriod,
dbo.WorkingDays(powerquantity.begtime, powerquantity.endtime) AS WorkingDays,
dbo.WorkingDays(powerquantity.begtime, powerquantity.endtime) * 24 AS WorkingHours,
SUM(CASE WHEN LEFT(powerquantity.tsperiod,2) = 'WD' THEN 1
            ELSE 0
        END) AS WD_EFA_Periods,
SUM(CASE WHEN LEFT(powerquantity.tsperiod,2) = 'WE' THEN 1
            ELSE 0
        END) AS WE_EFA_Periods,
Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0 - (dbo.WorkingDays(powerquantity.begtime, powerquantity.endtime) * 24) AS WeekendHours,
CASE WHEN fee.feemode = 'FIXED' THEN
    CASE WHEN position.unit = 'MW' THEN
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he1) * fee.pricediff
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he25) * fee.pricediff ELSE (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he2) * fee.pricediff END
            WHEN 2 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he3) * fee.pricediff
            WHEN 3 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he4) * fee.pricediff
            WHEN 4 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he5) * fee.pricediff
            WHEN 5 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he6) * fee.pricediff
            WHEN 6 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he7) * fee.pricediff
            WHEN 7 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he8) * fee.pricediff
            WHEN 8 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he9) * fee.pricediff
            WHEN 9 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he10) * fee.pricediff
            WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he11) * fee.pricediff
            WHEN 11 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he12) * fee.pricediff
            WHEN 12 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he13) * fee.pricediff
            WHEN 13 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he14) * fee.pricediff
            WHEN 14 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he15) * fee.pricediff
            WHEN 15 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he16) * fee.pricediff
            WHEN 16 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he17) * fee.pricediff
            WHEN 17 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he18) * fee.pricediff
            WHEN 18 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he19) * fee.pricediff
            WHEN 19 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he20) * fee.pricediff
            WHEN 20 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he21) * fee.pricediff
            WHEN 21 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he22) * fee.pricediff
            WHEN 22 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he23) * fee.pricediff
            WHEN 23 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he24) * fee.pricediff
    END    
    ELSE
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN MAX(powerquantity.he1) * fee.pricediff
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) * fee.pricediff ELSE MAX(powerquantity.he2) * fee.pricediff END
            WHEN 2 THEN MAX(powerquantity.he3) * fee.pricediff
            WHEN 3 THEN MAX(powerquantity.he4) * fee.pricediff
            WHEN 4 THEN MAX(powerquantity.he5) * fee.pricediff
            WHEN 5 THEN MAX(powerquantity.he6) * fee.pricediff
            WHEN 6 THEN MAX(powerquantity.he7) * fee.pricediff
            WHEN 7 THEN MAX(powerquantity.he8) * fee.pricediff
            WHEN 8 THEN MAX(powerquantity.he9) * fee.pricediff
            WHEN 9 THEN MAX(powerquantity.he10) * fee.pricediff
            WHEN 10 THEN MAX(powerquantity.he11) * fee.pricediff
            WHEN 11 THEN MAX(powerquantity.he12) * fee.pricediff
            WHEN 12 THEN MAX(powerquantity.he13) * fee.pricediff
            WHEN 13 THEN MAX(powerquantity.he14) * fee.pricediff
            WHEN 14 THEN MAX(powerquantity.he15) * fee.pricediff
            WHEN 15 THEN MAX(powerquantity.he16) * fee.pricediff
            WHEN 16 THEN MAX(powerquantity.he17) * fee.pricediff
            WHEN 17 THEN MAX(powerquantity.he18) * fee.pricediff
            WHEN 18 THEN MAX(powerquantity.he19) * fee.pricediff
            WHEN 19 THEN MAX(powerquantity.he20) * fee.pricediff
            WHEN 20 THEN MAX(powerquantity.he21) * fee.pricediff
            WHEN 21 THEN MAX(powerquantity.he22) * fee.pricediff
            WHEN 22 THEN MAX(powerquantity.he23) * fee.pricediff
            WHEN 23 THEN MAX(powerquantity.he24) * fee.pricediff
    END
END
ELSE
    CASE WHEN position.unit = 'MW' THEN
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he1) * fee.priceindex
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he25) * fee.priceindex ELSE (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he2) * fee.priceindex END
            WHEN 2 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he3) * fee.priceindex
            WHEN 3 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he4) * fee.priceindex
            WHEN 4 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he5) * fee.priceindex
            WHEN 5 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he6) * fee.priceindex
            WHEN 6 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he7) * fee.priceindex
            WHEN 7 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he8) * fee.priceindex
            WHEN 8 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he9) * fee.priceindex
            WHEN 9 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he10) * fee.priceindex
            WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he11) * fee.priceindex
            WHEN 11 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he12) * fee.priceindex
            WHEN 12 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he13) * fee.priceindex
            WHEN 13 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he14) * fee.priceindex
            WHEN 14 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he15) * fee.priceindex
            WHEN 15 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he16) * fee.priceindex
            WHEN 16 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he17) * fee.priceindex
            WHEN 17 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he18) * fee.priceindex
            WHEN 18 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he19) * fee.priceindex
            WHEN 19 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he20) * fee.priceindex
            WHEN 20 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he21) * fee.priceindex
            WHEN 21 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he22) * fee.priceindex
            WHEN 22 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he23) * fee.priceindex
            WHEN 23 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he24) * fee.priceindex
    END
    ELSE
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN MAX(powerquantity.he1) * fee.priceindex
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) * fee.priceindex ELSE MAX(powerquantity.he2) * fee.priceindex END
            WHEN 2 THEN MAX(powerquantity.he3) * fee.priceindex
            WHEN 3 THEN MAX(powerquantity.he4) * fee.priceindex
            WHEN 4 THEN MAX(powerquantity.he5) * fee.priceindex
            WHEN 5 THEN MAX(powerquantity.he6) * fee.priceindex
            WHEN 6 THEN MAX(powerquantity.he7) * fee.priceindex
            WHEN 7 THEN MAX(powerquantity.he8) * fee.priceindex
            WHEN 8 THEN MAX(powerquantity.he9) * fee.priceindex
            WHEN 9 THEN MAX(powerquantity.he10) * fee.priceindex
            WHEN 10 THEN MAX(powerquantity.he11) * fee.priceindex
            WHEN 11 THEN MAX(powerquantity.he12) * fee.priceindex
            WHEN 12 THEN MAX(powerquantity.he13) * fee.priceindex
            WHEN 13 THEN MAX(powerquantity.he14) * fee.priceindex
            WHEN 14 THEN MAX(powerquantity.he15) * fee.priceindex
            WHEN 15 THEN MAX(powerquantity.he16) * fee.priceindex
            WHEN 16 THEN MAX(powerquantity.he17) * fee.priceindex
            WHEN 17 THEN MAX(powerquantity.he18) * fee.priceindex
            WHEN 18 THEN MAX(powerquantity.he19) * fee.priceindex
            WHEN 19 THEN MAX(powerquantity.he20) * fee.priceindex
            WHEN 20 THEN MAX(powerquantity.he21) * fee.priceindex
            WHEN 21 THEN MAX(powerquantity.he22) * fee.priceindex
            WHEN 22 THEN MAX(powerquantity.he23) * fee.priceindex
            WHEN 23 THEN MAX(powerquantity.he24) * fee.priceindex
    END
END
END AS BlockCostGBP,
CASE DATEPART(HOUR, powerquantity.begtime)
   WHEN 0 THEN MAX(powerquantity.he1)
        WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) ELSE MAX(powerquantity.he2) END
        WHEN 2 THEN MAX(powerquantity.he3)
        WHEN 3 THEN MAX(powerquantity.he4)
        WHEN 4 THEN MAX(powerquantity.he5)
        WHEN 5 THEN MAX(powerquantity.he6)
        WHEN 6 THEN MAX(powerquantity.he7)
        WHEN 7 THEN MAX(powerquantity.he8)
        WHEN 8 THEN MAX(powerquantity.he9)
        WHEN 9 THEN MAX(powerquantity.he10)
        WHEN 10 THEN MAX(powerquantity.he11)
        WHEN 11 THEN MAX(powerquantity.he12)
        WHEN 12 THEN MAX(powerquantity.he13)
        WHEN 13 THEN MAX(powerquantity.he14)
        WHEN 14 THEN MAX(powerquantity.he15)
        WHEN 15 THEN MAX(powerquantity.he16)
        WHEN 16 THEN MAX(powerquantity.he17)
        WHEN 17 THEN MAX(powerquantity.he18)
        WHEN 18 THEN MAX(powerquantity.he19)
        WHEN 19 THEN MAX(powerquantity.he20)
        WHEN 20 THEN MAX(powerquantity.he21)
        WHEN 21 THEN MAX(powerquantity.he22)
        WHEN 22 THEN MAX(powerquantity.he23)
        WHEN 23 THEN MAX(powerquantity.he24)
END AS Volume,
CASE WHEN position.unit = 'MW' THEN
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he1)
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he25) ELSE (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he2) END
            WHEN 2 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he3)
            WHEN 3 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he4)
            WHEN 4 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he5)
            WHEN 5 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he6)
            WHEN 6 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he7)
            WHEN 7 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he8)
            WHEN 8 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he9)
            WHEN 9 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he10)
            WHEN 10 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he11)
            WHEN 11 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he12)
            WHEN 12 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he13)
            WHEN 13 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he14)
            WHEN 14 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he15)
            WHEN 15 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he16)
            WHEN 16 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he17)
            WHEN 17 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he18)
            WHEN 18 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he19)
            WHEN 19 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he20)
            WHEN 20 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he21)
            WHEN 21 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he22)
            WHEN 22 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he23)
            WHEN 23 THEN (Cast((powerquantity.endtime - powerquantity.begtime) as Float) * 24.0) * MAX(powerquantity.he24)
    END    
    ELSE
        CASE DATEPART(HOUR, powerquantity.begtime)
            WHEN 0 THEN MAX(powerquantity.he1)
            WHEN 1 THEN CASE DATEPART(MONTH, powerquantity.begtime) WHEN 10 THEN MAX(powerquantity.he25) ELSE MAX(powerquantity.he2) END
            WHEN 2 THEN MAX(powerquantity.he3)
            WHEN 3 THEN MAX(powerquantity.he4)
            WHEN 4 THEN MAX(powerquantity.he5)
            WHEN 5 THEN MAX(powerquantity.he6)
            WHEN 6 THEN MAX(powerquantity.he7)
            WHEN 7 THEN MAX(powerquantity.he8)
            WHEN 8 THEN MAX(powerquantity.he9)
            WHEN 9 THEN MAX(powerquantity.he10)
            WHEN 10 THEN MAX(powerquantity.he11)
            WHEN 11 THEN MAX(powerquantity.he12)
            WHEN 12 THEN MAX(powerquantity.he13)
            WHEN 13 THEN MAX(powerquantity.he14)
            WHEN 14 THEN MAX(powerquantity.he15)
            WHEN 15 THEN MAX(powerquantity.he16)
            WHEN 16 THEN MAX(powerquantity.he17)
            WHEN 17 THEN MAX(powerquantity.he18)
            WHEN 18 THEN MAX(powerquantity.he19)
            WHEN 19 THEN MAX(powerquantity.he20)
            WHEN 20 THEN MAX(powerquantity.he21)
            WHEN 21 THEN MAX(powerquantity.he22) * fee.pricediff
            WHEN 22 THEN MAX(powerquantity.he23) * fee.pricediff
            WHEN 23 THEN MAX(powerquantity.he24) * fee.pricediff
    END
END AS MWh,
position.unit,
powerquantity.timeunit,
CASE WHEN fee.feemode = 'FIXED' THEN
    fee.pricediff        
ELSE
    fee.priceindex            
END AS GBPMWh,
fee.feemode
from Trade
inner join position on trade.trade = position.trade
inner join powerposition on position.position = powerposition.position
inner join powerquantity on powerposition.position = powerquantity.position and powerposition.posdetail = powerquantity.posdetail
inner join contract on position.contract = contract.contract and contract.contracttype in ('ETSA','SETSA')
inner join fee on position.position = fee.dbvalue and fee.dbcolumn = 'POSITION' and fee.feemethod = 'COMMODITY PRICE'
where contract.contract = '110156'
and powerquantity.posstatus = 1
and position.loadshape is null
group by
powerquantity.begtime,
powerquantity.endtime,
Trade.trade,
Trade.tradedate,
fee.pricediff,
fee.priceindex,
position.unit,
contract.contract,
fee.feemode,
powerquantity.timeunit,
position.counterparty,
position.positiontype,
Trade.cstcontractstart,
Trade.cstcontractend
order by powerquantity.begtime,powerquantity.endtime
go

  Can you post up the two execution plans (actual not estimate) as .sqlplan attachments please.
  Just as a matter of interest, was this query scripted using a query designer?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• solus

  Hall of Fame

  Points: 3495

  More actions

  October 15, 2018 at 6:12 am

  #2009577

  I wrote the query manually, 2 execution plans attached

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 15, 2018 at 6:43 am

  #2009584

  solus - Monday, October 15, 2018 6:12 AM

  I wrote the query manually, 2 execution plans attached

  Can you post actual rather than estimated plans please?
  Initial findings - optimiser timeout, unable to generate parallel plan, implicit conversion warning.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Brandie Tarvin

  SSC Guru

  Points: 172994

  More actions

  October 15, 2018 at 8:59 am

  #2009625

  ChrisM@Work - Monday, October 15, 2018 6:43 AM

  solus - Monday, October 15, 2018 6:12 AM

  I wrote the query manually, 2 execution plans attached

  Can you post actual rather than estimated plans please?
  Initial findings - optimiser timeout, unable to generate parallel plan, implicit conversion warning.

  FYI, solus, the reason Chris asks for the actual execution plans is that estimated can sometimes be wildly off of what an actual plan is. I've seen it on rare occasions where the actual bears only a passing resemblance to the estimated plan because of stale statistics and other issues.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• solus

  Hall of Fame

  Points: 3495

  More actions

  October 22, 2018 at 8:01 am

  #2010580

  how do i get the actual plan ?

• Brandie Tarvin

  SSC Guru

  Points: 172994

  More actions

  October 22, 2018 at 8:14 am

  #2010582

  solus - Monday, October 22, 2018 8:01 AM

  how do i get the actual plan ?

  In the GUI, go to Query -> Include Actual Execution Plan. Then when the query is completed, right click on the execution plan and save it off to your hard drive. You can then post that plan to this thread.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  October 22, 2018 at 8:36 pm

  #2010718

  Hold the phone a minute, please.  What is the datatype of the position.loadshape column?  If it's a numeric datatype, what is the minimum value that it can be?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• solus

  Hall of Fame

  Points: 3495

  More actions

  October 23, 2018 at 4:13 am

  #2010733

  position.loadshape is varchar(64) and its nullable

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  October 23, 2018 at 6:22 am

  #2010746

  solus - Tuesday, October 23, 2018 4:13 AM

  position.loadshape is varchar(64) and its nullable

  Then instead of using IS NOT NULL, try the following code...

  and position.loadshape > ''

  This will reject NULLs, Empty Strings, and any length string consisting of only blanks/spaces.  Since its not a "negative" lookup like IS NOT NULL, it may perform better and, even if it doesn't will reject empty and blank strings as well NULLs.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)