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

  • 问题

  • 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!!

    2016年10月31日 15:24

全部回复

  • Did you check if there's blocking? 
    2016年10月31日 17:58
  • 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 MSDNFSF@microsoft.com.

    2016年11月1日 8:49
  • 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


    2016年11月3日 3:42
  • 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 MSDNFSF@microsoft.com.

    2016年11月3日 9:37