添加参数时SSIS的性能下降

Question

我正在使用SSIS中的OLE DB Source从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 Query from Variable，并使用表达式构建查询，这就解决了性能问题。

编辑：下面是使用where参数执行问题代码时在Server中看到的命令：

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参数的引入使我转而研究了存储过程的含义。