[SSIS]當資料來源透過cursor或程式邏輯產生的,要如何取出資料集？ 使用[執行SQL工作]

1.當資料來源透過cursor或程式邏輯產生的,最後資料放在table variable,可以透過[執行SQL工作編輯器]把資料集取回

2.此結果集要指定給一個ssis變數,此變數型態為object

3.接著使用foreach將資料一筆一筆取出

4.指定foreach取出的資料,一個欄位要指定一個變數承接

5.這邊例子是透過這兩個變數組成insert語法

union與union all差異

select part,sum(qty) qty
from
(
   select part,sum(qty) qty from A
   union
   select part,sum(qty) qty from B
)
上面的語法union會把A+B撈出的資料做distinct
這樣會導致最外層sum的時候資料有短缺

同樣的SQL直接跑(4秒)跟包成proceudure跑(3分)時間差距很大

同樣的SQL直接跑(4秒)跟包成proceudure跑(3分)時間差距很大

後來查到奇怪的解決方式

把sp_commit_case的傳入的參數給local變數,然後Procedure裡原本用到傳入參數的地方都改為local變數

這裡"Parameter Sniffing"有解釋原因

如果Procudure內直接使用傳入參數當where條件的話，則SQL只會存在第一次執行時所創建的執行計劃

意思就是說只有第一次才有優化,第二次後就….

或許create procedure使用with recompile及清空procedure cache都可達成相同目的?

以上分享給大家知道

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure. I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g. Slow way: CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20)) AS BEGIN     SELECT *     FROM orders     WHERE customerid = @CustID END Fast way: CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20)) AS BEGIN     DECLARE @LocCustID varchar(20)     SET @LocCustID = @CustID       SELECT *     FROM orders     WHERE customerid = @LocCustID END Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.