sql server - Simple SQL query performance puzzles me -

sql server - Simple SQL query performance puzzles me -

a quick note: we're running sql server 2012 in house, problem seems occur in 2008 , 2008 r2, , perhaps older versions well.

i've been investigating performance issue in code of ours, , i've tracked downwards problem next simple query:

select min(document_id) document document_id in (select top 5000 document_id document document_id > 442684)

i've noticed query takes absurdly long time (between 18s , 70s depending on resources of machine running it) homecoming when final value (after greater-than) 442000 or larger. below that, query returns instantly.

i've since tweaked query this:

select min(t.document_id) (select top 5000 document_id document document_id > 442684) t

this returns values of > i've tested with.

i've solved performance issue @ hand, i'm largely happy, i'm still puzzling on why original query performed poorly 442000 , why runs virtually value below (400000, 350000, etc).

can explain this?

edit: fixed 2nd query min instead of max (that typo)

this long comment.

the secret understanding performance in sql server (and other databases) execution plan. need @ execution plan queries understand going on.

the first version of query has bring together operation. in subquery way express join. sql server has several ways implement joins, such hash-matches, merge-sort, nested-loop, , index-lookup operations. optimizer chooses 1 thinks best.

without seeing execution plans, best guess optimizer changes mind best algorithm utilize in. in experience, means switched nested-loop algorithm more reasonable one.

sql sql-server performance