插入性能不佳的表 Exec SP

我正在開發一個數據倉庫。我們每天晚上刷新的臨時表之一有大約 1000 萬行。我們正在使用自定義建構的 ETL 工具，我無法對其進行太多更改。該工具像這樣載入這個臨時表：

truncate stage_table;
insert into stage_table with (tablockx) (column1, column2, etc...)
exec load_stage_table @batch_id = @batch_input

的內容load_stage_table有一些設置和一個選擇語句。我無法分享確切的程式碼，但這是一個基本範例。

create table load_stage_table
(
    @batch_id varchar(max) = null
)
as 

-- <update batch_id in batch_table>

-- collect data

select
   column1 = table1.column1,
   column2 = table2.column2,
   ...
from table1
join table2
   on table2.id = table1.table2_id
-- many more similar joins

問題是，當我按照我們的 ETL 工具執行儲存過程時，執行時間幾乎是 30 分鐘。但是，如果我將儲存過程修改為在內部插入語句，則只需 1 分鐘。

   create table load_stage_table
(
    @batch_id varchar(max) = null
)
as 

-- <update batch_id in batch_table>

-- collect data
insert into stage_table with (tablockx) (column1, column2, etc...)
select
   column1 = table1.column1,
   column2 = table2.column2,
   ...
from table1
join table2
   on table2.id = table1.table2_id
-- many more similar joins

在兩種方式都執行了幾次並檢查了執行計劃之後，當插入在儲存過程之外時，似乎沒有使用並行性。

從儲存過程之外的返回載入表會阻止並行性嗎？或者這是否表明選擇語句需要一些查詢調整？

INSERT...EXEC 可能會禁止並行性，但我高度懷疑這是這裡的主要問題。問題是它的INSERT...EXEC工作方式與INSERT...SELECT. 將查詢（即SELECT語句或OUTPUT子句）的結果插入到表中時，結果會流式傳輸到表中。如果您正在觀看row_count和used_page_count從，您會發現它們在開始時sys.dm_db_partition_stats立即增加。INSERT...SELECT

但是，當插入EXEC（通常是儲存過程，但可能是動態 SQL）的結果時，子流程/儲存過程需要在將任何內容插入表之前完全完成（是的，我剛剛測試過）。如果您正在觀看row_countand used_page_countfrom sys.dm_db_partition_stats，您會在開始後看起來他們在0（或他們開始的任何地方）停留了很長時間INSERT...EXEC。當儲存過程返回數百萬行時，這不是成功的秘訣，因為這些結果需要在將它們插入表之前儲存在某處。

引用自：https://dba.stackexchange.com/questions/196380