Geek asks and answers

SQLServer UNION has better performance than UNION ALL?

I know that UNION ALL should have better performance than UNION (see: union and entire connection performance ).

Now I have this huge stored procedure (with lots of queries) where the end result is two SELECT sections with UNION between them. Since both datasets are alien to each other, I can use UNION ALL, which suggests they will be better (no clear operation).

I tested it on several databases and it worked fine. The problem is that one of my clients gave me his database for performance tuning, and when I examined it, I noticed that if I change UNION ALL to UNION, the performance will be a little better (!). These are all the changes I made to the stored procedure.

Can someone explain how this situation can happen?

Thanks
Ziv

Update: the
attached execution plan for both queries (part of diff): enter image description here

+5
source share
2 answers

You referred to another topic that pointed to this article.

, . - Distinct Sort, .

, Merge Join ( ). .

: ?

, :
1) PRC :

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

, " " . .

2) , .

?

+1

, . UNION SELECT DISTINCT . , , UNION ALL, . , , UNION - , . UNION ALL ""...

, .

0

More articles:


All Articles