sql - Ridiculously huge impact of comparison operator on query execution duration -

i have following views defined:

dsplit_base - union of 4 queries each of simple join between fact , mapping tables (contains call statistics); consists of 201 columns

calls_check - view derived dsplit_base meant used in data consistency check. here definition:

select a.brand,        a.[call center] ,        c.date,        c.weekday,        count(*) vol,        cast((count(*)-g.vol) real)/g.vol*100 vol_diff ,        sum(abncalls+acdcalls) calls ,        case            when g.calls<>0 cast((sum(abncalls+acdcalls)-g.calls) real)/g.calls*100            else case                     when sum(abncalls+acdcalls)<>0 100                     else 0                 end        end calls_diff dsplit_base join calendar c on a.row_date=c.date join   ( select t.brand,            t.[call center],            c.weekday,            avg(cast(vol bigint)) vol,            avg(cast(calls bigint)) calls         ( select brand,               [call center], row_date, count(*) vol, sum(abncalls+acdcalls) calls dsplit_base group row_date, [call center],               brand ) t    join calendar c on t.row_date=c.date    group c.weekday,             t.[call center],             t.brand) g on c.weekday=g.weekday , a.brand=g.brand , a.[call center]=g.[call center] group c.date,          c.weekday,          g.vol,          g.calls,          a.[call center],          a.brand 

the following query yields around 16000 rows in 1-3 seconds:

    select * calls_check  brand   call center date    weekday     vol vol_diff    calls   calls_diff lmn munich      2008-01-24  thursday    3   -25     470 8.796296 lmn munich      2008-04-26  saturday    3   0       352 51.72414 ... 

now actual problem encountered when tried pull out results limited period of time. adding clause follows query not finish (surely not in ~10 minutes):

    select * calls_check     date >= dateadd(d, -8, sysdatetime()) 

and, maybe weirder, query executes in second!

    select * calls_check     date < dateadd(d, -8, sysdatetime()) 

can tell why comparison operator in clause makes such difference? why < seems efficiently slice result set while > or = makes query unresponsive?

---

some additional info:

the dsplit_base view consists of 4 tables union (with joins). here row counts:

dsplit_de - 2521

dsplit_wns - 7243

dsplit_us - 121451

partners - 377841 (166043)

actual 'partners' table row count 166043 because in view takes rows on condition:

from partners p join splitdim s  on p.[skill name]=s.splitname , (p.date>=s.[start_date] or s.[start_date] null) , (p.date<=s.[end_date] or s.[end_date] null) s.[call center] in ('sitel', 'trx', 'sellbytel')  or (s.[call center]='wns' , p.date<(select min(row_date) dsplit_wns)) or (s.[call center]='munich' , (p.date<'2012-06-29' or p.date between '2012-08-01' , '2012-08-27')) 

i experimented modified view definition , found out that:

having view dsplit_de and/or dsplit_wns both queries work pretty fast (1-2 seconds)

with partners '>=' query took ~30s ; dsplit_us took ~60s

here actual execution plan of latter exec plan

the last 2 table bigger others yet few hundred thousands of records should not take long. causes difference in execution time depenending on '<' or '>' operator used in clause?

as far know < , >= different example what value of x if x <4 less 4 or 3,2,1... meanwhile what value of x if x >=4 4,5,6..... meaning yield different result, , maybe reason behind

     select * calls_check date < dateadd(d, -8, sysdatetime()) 

retrieves minimal data

while

 select * calls_check     date >= dateadd(d, -8, sysdatetime()) 

retrieves lot of data that's why needs lot of time retrieve it

and need careful when comparing dates, should both in datetime not in string cause bugs , not yield expected result

hope helps :)