sql - How come adding "ORDER BY" reduce query processing time dramatically? -


i have sql query below, without last order statement, query runs 1 minute, , statement, query finishes in 2 seconds.

select a.c2, b.c2 (   select c1, c2     c3 = , c2 = somethingelse   inner join   b   on   a.c1 = b.c1 ) order b.c2 

by looking @ execution plan, seems adding order statement, triggers parallelism.

with "order by", queryplan is: degreeofparallelism="4" memorygrant="94176" cachedplansize="184" compiletime="35" compilecpu="35" compilememory="1728"

without "order by", queryplan is: queryplan degreeofparallelism="1" memorygrant="2432" cachedplansize="120" compiletime="57" compilecpu="57" compilememory="2488"

i not figure reason behind scene. normaly assume order increase query processing time.

[edit]

there no index on b.c2, , not primary key or clustered key. a.c1 primary key , clustered index. a.c2 spatial index. a.c3 non-clustered index. b.c1 primary key , clustered index.

there 4 columns , 40 million rows in table a. there 2 columns , 8 million rows in table b.

i'm using microsoft sql server 2008.

thanks!


Comments

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -