@MoeyE Do your homework! Execute the resulting query on the source side and check out the execution plan.
(and tell us what the source Db is, its version and just to be sure the Replicate version in play,
and while you are at it... what was the general layout of the pass-through filter? ).
Using a pass-through filter is expected to be faster because a) there should be fewer rows to be transferred over the network and examined by Replicate and b) supposedly the source understands the data and knows the best query to fetch the desired rows.
Apparently this backfired. Without a pass-through the source will typically just do a full table scan, not utilizing any indexes. With pass through it probably has an index it can use to limit the number of rows to be considered. However, using an indexed read typically makes the lowest levels of the DB jump around from left to rights and back to gather the rows. This plays havoc on everything. It may read a block to fetch a row and minutes later have to come back to that block to fetch another row but the block is no longer in the DB cache and the storage system needs to go back as well. It then becomes a number game where the table statistics define what the right plan is.
My personal guess is that if you need 50% or more of the data, then a full table scan is optimal.
What does the DB know about the data distribution and filter values? Does the DB used (as of yet unknown) even have stats on data by value? Maybe the filter query is age < 67. Well on a retired personel list that is likely to be more than 80% of the rows, but on a collage application list it will be less than 5%. For the retirees the full table scan is vastly better. For the college applicants the key range is better. Does the DB have the right information to decide?
Hein
