sql-server - 添加参数时 SSIS 性能下降

Question

我在 SSIS 中使用 OLE DB 源从 SQL Server 2012 数据库中提取数据行:

SELECT item_prod.wo_id, item_prod.oper_id, item_prod.reas_cd, item_prod.lot_no, item_prod.item_id, item_prod.user_id, item_prod.seq_no, item_prod.spare1, item_prod.shift_id, item_prod.ent_id, item_prod.good_prod, item_cons.lot_no as raw_lot_no, item_cons.item_id as rm_item_id, item_cons.qty_cons
FROM item_prod
LEFT OUTER JOIN item_cons on item_cons.wo_id=item_prod.wo_id AND item_cons.oper_id=item_prod.oper_id AND item_cons.seq_no=item_prod.seq_no AND item_prod.lot_no=item_cons.fg_lot_no

这很好用，目前每分钟可以拉出大约 100 万行。由于在不使用缓存时性能要好得多，因此使用左外连接代替查找，并且两个表可能包含超过 4000 万行。

我们需要查询仅提取在上次运行中未提取的行。最后运行的 row_id 被存储在一个变量中并放在上面查询的末尾:

WHERE item_prod.row_id > ?

在第一次运行时，参数将为 -1(解析所有内容)。 通过添加 where 子句 ，性能下降了 5-10 倍(每 5-10 分钟 100 万行)。是什么导致了如此显着的性能下降，有没有办法对其进行优化？

Answer 1

事实证明，SSIS 在执行带有参数的查询时会创建一个存储过程。这是通过查看 SQL Server Profiler 中的执行发现的。

结果，性能受到了影响，我认为是 related to parameter sniffing .

我将源更改为使用 SQL Query from Variable并使用表达式构建我的查询，这修复了性能。

编辑:以下是使用 where 执行问题代码时在 SQL Server Profiler 中看到的命令。参数:

exec [sys].sp_describe_undeclared_parameters N'SELECT item_prod.wo_id, item_prod.oper_id, item_prod.reas_cd, item_prod.lot_no, item_prod.item_id, item_prod.user_id, item_prod.seq_no, item_prod.spare1, item_prod.shift_id, item_prod.ent_id, item_prod.good_prod, item_cons.lot_no as raw_lot_no, item_cons.item_id as rm_item_id, item_cons.qty_cons
FROM item_prod
LEFT OUTER JOIN item_cons on item_cons.wo_id=item_prod.wo_id AND item_cons.oper_id=item_prod.oper_id AND item_cons.seq_no=item_prod.seq_no AND item_prod.lot_no=item_cons.fg_lot_no
WHERE item_prod.row_id > @P1'

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 int',N'SELECT item_prod.wo_id, item_prod.oper_id, item_prod.reas_cd, item_prod.lot_no, item_prod.item_id, item_prod.user_id, item_prod.seq_no, item_prod.spare1, item_prod.shift_id, item_prod.ent_id, item_prod.good_prod, item_cons.lot_no as raw_lot_no, item_cons.item_id as rm_item_id, item_cons.qty_cons
FROM item_prod
LEFT OUTER JOIN item_cons on item_cons.wo_id=item_prod.wo_id AND item_cons.oper_id=item_prod.oper_id AND item_cons.seq_no=item_prod.seq_no AND item_prod.lot_no=item_cons.fg_lot_no
WHERE item_prod.row_id > @P1',1
select @p1

exec [sys].sp_describe_first_result_set N'SELECT item_prod.wo_id, item_prod.oper_id, item_prod.reas_cd, item_prod.lot_no, item_prod.item_id, item_prod.user_id, item_prod.seq_no, item_prod.spare1, item_prod.shift_id, item_prod.ent_id, item_prod.good_prod, item_cons.lot_no as raw_lot_no, item_cons.item_id as rm_item_id, item_cons.qty_cons
FROM item_prod
LEFT OUTER JOIN item_cons on item_cons.wo_id=item_prod.wo_id AND item_cons.oper_id=item_prod.oper_id AND item_cons.seq_no=item_prod.seq_no AND item_prod.lot_no=item_cons.fg_lot_no
WHERE item_prod.row_id > @P1',N'@P1 int',1

由于我不完全确定上面生成的代码的作用，因此我可能遗漏了其他相关命令。最初，我假设 SSIS 变量会被插入到查询中，但是 @P1 参数的引入让我转而查看存储过程的含义。