sql server - SSIS Performance drop when adding parameters -

i'm using ole db source in ssis pull data rows sql server 2012 database:

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 raw_lot_no, item_cons.item_id rm_item_id, item_cons.qty_cons item_prod left outer join item_cons on item_cons.wo_id=item_prod.wo_id , item_cons.oper_id=item_prod.oper_id , item_cons.seq_no=item_prod.seq_no , item_prod.lot_no=item_cons.fg_lot_no 

this works great, , able pull around 1 million rows per minute currently. left outer join used instead of lookup due better performance when using no cache, , both tables may contain upwards of 40 million rows.

we need query pull rows haven't been pulled in previous run. last run row_id gets stored in variable , put @ end of above query:

where item_prod.row_id > ? 

on first run, parameter -1 (to parse everything). performance drops between 5-10x adding clause (1 million rows per 5-10 minutes). causing such significant performance drop, , there way optimize it?

it turns out, ssis creates stored procedure when executing query parameters. discovered looking @ execution in sql server profiler.

as result, there performance hit, believe related parameter sniffing.

i changed source use sql query variable , built query using expression instead, , fixed performance.

edit: following commands seen in sql server profiler when executing question's code where parameter:

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 raw_lot_no, item_cons.item_id rm_item_id, item_cons.qty_cons item_prod left outer join item_cons on item_cons.wo_id=item_prod.wo_id , item_cons.oper_id=item_prod.oper_id , item_cons.seq_no=item_prod.seq_no , item_prod.lot_no=item_cons.fg_lot_no 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 raw_lot_no, item_cons.item_id rm_item_id, item_cons.qty_cons item_prod left outer join item_cons on item_cons.wo_id=item_prod.wo_id , item_cons.oper_id=item_prod.oper_id , item_cons.seq_no=item_prod.seq_no , item_prod.lot_no=item_cons.fg_lot_no 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 raw_lot_no, item_cons.item_id rm_item_id, item_cons.qty_cons item_prod left outer join item_cons on item_cons.wo_id=item_prod.wo_id , item_cons.oper_id=item_prod.oper_id , item_cons.seq_no=item_prod.seq_no , item_prod.lot_no=item_cons.fg_lot_no item_prod.row_id > @p1',n'@p1 int',1 

since i'm not entirely sure above generated code does, there may other related commands missed. originally, assumed ssis variables inserted query, introduction of @p1 parameter led me @ stored procedure implications instead.