SQL Server2012 : the query with unill all can never return the whole result

Question

Hey there,

I have a query like:

Select col1 , col2 from Table1  --return around 800K rows

union all

select col1 , col2 from Table2 -- return around 800k rows

when I split the query to run the two sub-query, they can finish and return the whole result soon. But when I tried to run the query with the UNION ALL, it returns around 800K rows and the get stucked and can NEVER finish.

I would really appreciate if someone could give me a little help.

Best wishes!!

Answer 1

Did you check if there's blocking? 

Answer 2

Hi Xie Zhenmao,

You could first have a look at the execution planto check whether there’re something cost a long time.

Then, as rmiaomentioned, you could run following code to see whether there’re something related to this query. For more things, please have a look at sys.dm_tran_locks.

BEGIN TRAN 
Select col1 , col2 from Table1  --return around 800K rows
union all 
select col1 , col2 from Table2 -- return around 800k rows
select * from sys.dm_tran_locks where request_session_id = @@SPID;

As a workaround, you could try using this code below to implement your need.

--Step 1, Create temporary table
Select col1 , col2 into #tmpResult from Table1  --return around 800K rows
--Step 2, Load  data from Table2 into temporary table
insert into #tmpResult
select col1 , col2 from Table2 -- return around 800k rows
--Step 3, Query result
select * from #tmpResult
--Clean up
drop table #tmpResult

Best Regards,

Albert Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi Albert,

You are right! The execution explain looks good. I think just something block the query that day.

I tried to insert the result into a temp table, it took around 5mins. Then I went back to pull all data out from the VIEW, the query finished in 2min!! I really don't know what happened! 

Thanks

Answer 4

Hi scott_m,

Loading into a temporary table is just a workaround when “union all” cost a long time. If the “union all” cost less time, you could use it again. I guess, there’re maybe some factors affect the performance. You could consider creating index on the related columns to improve it. The following blog you could have a look at it.

https://blogs.msdn.microsoft.com/mssqlisv/2008/09/26/designing-composite-indexes/

Best Regards,

Albert Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].