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
Post a Comment