October 15, 2018 at 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
October 15, 2018 at 6:04 am
solus - Monday, October 15, 2018 3:34 AMI have a query that returns results in under a second (115 rows) if I useIS 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?
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
October 15, 2018 at 6:12 am
I wrote the query manually, 2 execution plans attached
October 15, 2018 at 6:43 am
solus - Monday, October 15, 2018 6:12 AMI 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.
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
October 15, 2018 at 8:59 am
ChrisM@Work - Monday, October 15, 2018 6:43 AMsolus - Monday, October 15, 2018 6:12 AMI wrote the query manually, 2 execution plans attachedCan 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.
October 22, 2018 at 8:01 am
how do i get the actual plan ?
October 22, 2018 at 8:14 am
solus - Monday, October 22, 2018 8:01 AMhow 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.
October 22, 2018 at 8:36 pm
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
Change is inevitable... Change for the better is not.
October 23, 2018 at 4:13 am
position.loadshape is varchar(64) and its nullable
October 23, 2018 at 6:22 am
solus - Tuesday, October 23, 2018 4:13 AMposition.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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply