加速SSIS包(插入和更新)
这里引用了Twitter上的#sqlhelp (解决了--见文章末尾的解决方案)。
我正在尝试加速一个SSIS包，该包插入2 900万行新数据，然后用另外2列更新这些行。到目前为止，包循环遍历包含文件的文件夹，将平面文件插入数据库，然后执行更新并存档文件。添加(感谢@billinkc)：SSIS顺序是Foreach循环、数据流、执行SQL任务、文件任务。
不需要很长时间的内容：循环，文件移动和截断表(工作台)。什么需要很长时间：插入数据，运行下面的语句：
UPDATE dbo.Stage
SET Number = REPLACE(Number,',','')
## Heading ##
-- Creates temp table for State and Date
CREATE TABLE #Ref (Path VARCHAR(255))
INSERT INTO #Ref VALUES(?)
-- Variables for insert
DECLARE @state AS VARCHAR(2)
DECLARE @date AS VARCHAR(12)
SET @state = (SELECT SUBSTRING(RIGHT([Path], CHARINDEX('\', REVERSE([Path]))-1),12,2) FROM #Ref)
SET @date = (SELECT SUBSTRING(RIGHT([Path], CHARINDEX('\', REVERSE([Path]))-1),1,10) FROM #Ref)
SELECT @state
SELECT @date
-- Inserts the values into main table
INSERT INTO dbo.MainTable (Phone,State,Date)
SELECT d.Number, @state, @date
FROM Stage d
-- Clears the Reference and Stage table
DROP TABLE #Ref
TRUNCATE TABLE Stage请注意，我曾在insert和Max insert提交大小上处理过每批增加行的操作，但这两种操作都没有影响包的速度。
解决并添加：
对于那些对数字感兴趣的人: OP包的时间是11.75分钟；使用William的技术(见下面这个)，它下降到9.5分钟。当然，有2900万行，在一个速度较慢的服务器上，这是可以预期的，但希望这能向您展示实际数据的有效性。关键是尽可能多地保持数据流任务上发生的进程，因为更新数据(在数据流之后)占用了明显的一部分时间。
希望这能帮助其他有类似问题的人。
更新二：我添加了一个IF语句，它将它从9分钟减少到4分钟。执行SQL任务的最后代码：
-- Creates temp table for State and Date
CREATE TABLE #Ref (Path VARCHAR(255))
INSERT INTO #Ref VALUES(?)
DECLARE @state AS VARCHAR(2)
DECLARE @date AS VARCHAR(12)
DECLARE @validdate datetime
SET @state = (SELECT SUBSTRING(RIGHT([Path], CHARINDEX('\', REVERSE([Path]))-1),12,2) FROM #Ref)
SET @date = (SELECT SUBSTRING(RIGHT([Path], CHARINDEX('\', REVERSE([Path]))-1),1,10) FROM #Ref)
SET @validdate = DATEADD(DD,-30,getdate())
IF @date < @validdate
BEGIN
TRUNCATE TABLE dbo.Stage
TRUNCATE TABLE #Ref
END
ELSE
BEGIN
-- Inserts new values
INSERT INTO dbo.MainTable (Number,State,Date)
SELECT d.Number, @state, @date
FROM Stage d
-- Clears the Reference and Stage table after the insert
DROP TABLE #Ref
TRUNCATE TABLE Stage
END
回答 1
Stack Overflow用户
发布于 2012-11-19 14:00:43
据我所知，您正在读取平面文件中的29,000,000行，并将它们写入一个暂存表中，然后运行一个sql脚本，该脚本在暂存表中更新(读/写)相同的29,000,000行，然后将这29,000,000条记录(从暂存到写入)移动到最后一个表。
您不能从平面文件中读取、使用SSIS转换来清理数据并添加两个额外的列，然后直接写入最后一个表。您将只在每组不同的数据上工作一次，而不是处理进程所做的三次(如果计算为不同的读写次数则为六次)？
我将更改您的数据流，以便在处理所需项时进行转换，并将其直接写入我的最后一个表。
编辑
从问题中的SQL中，您似乎正在通过从PHONE字段中移除逗号来转换数据，然后从当前处理的文件路径的特定部分检索状态和日期，然后将这三个数据点存储到NAT表中。这些事情可以通过数据流中的派生列转换来完成。
对于State和Date列，设置两个名为State和Date的新变量。使用变量定义中的表达式将它们设置为正确的值(就像在SQL中所做的那样)。当Path变量更新(在您的循环中，我假设)。状态变量和日期变量也将更新。
在派生列转换中，将State变量拖到表达式字段中，并创建一个名为State的新列。
重复约会。
对于PHONE列，在派生列transforamtion中创建如下表达式：
替换(电话、“”)
将派生列字段设置为替换“Phone”
对于输出，创建NAT表的目标，并将数据流中的Phone、State和Date列链接到NAT表中的相应列。
如果输入中有其他列，则可以选择不从源引入这些列，因为从原始数据看，您只对电话列进行操作。
/edit
https://stackoverflow.com/questions/13462681
复制
腾讯云开发者
