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

  • 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

  • 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

  • I wrote the query manually, 2 execution plans attached

  • 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

  • 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.

  • how do i get the actual plan ?

  • 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.

  • 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)

  • position.loadshape is varchar(64) and its nullable

  • 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)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply