Hi,

We have come across some issues when using Bulk Synchonize with large datasets. Our destination table is currently at around 120 million rows and we are syncing about 2.4 million rows each time.

The delete statements that are generated are taking a very long time. We have improved this by indexing our destination table correctly. But given the large number of rows we are dealing with we have also found some success by generating the temp table and indexing that as well. The deletes also seem to be batching in an interesting way. I'm not sure if there is a better way to do this. I'm also aware that we could split our import into smaller chunks as a fix as well.

1. Is it possible to hook into some kind of life-cycle event that occurs after the creation of the temp table so that we can create indexes on it? The only place I have seen that it is possible to get the temp table name is in the batch end events.
2. The generated sql for deletes will delete TOP [BatchLimit] records, but not sure if there is any better way to optimise this as it's running over all records in the staging table and not batched chunks. SQL included below from Profiler.
3. I've also tried using table hints to force specific index usage. This doesn't seem like it generates correct sql for the delete statement. It puts the index hint after the first line "FROM DestinationTable", but I think maybe it should be after the second "FROM DestinationTable" after the output.

exec sp_executesql N'DELETE TOP (50000) FROM DestinationTable OUTPUT ''DELETE'' AS [$action], -1 AS ZZZ_Index, 1 FROM [DistributionPlan] AS DestinationTable WHERE NOT EXISTS (SELECT 1 FROM ( SELECT TOP 100 PERCENT * FROM #ZZZ_DistributionPlan_d8917687c96c409598108d56b851f4f2 WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index ) AS StagingTable WHERE DestinationTable.[BSKey] = StagingTable.[BSKey] AND ( DestinationTable.SyncKey = ''F2606'' )) AND EXISTS (SELECT 1 FROM ( SELECT TOP 100 PERCENT * FROM #ZZZ_DistributionPlan_d8917687c96c409598108d56b851f4f2 WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index ) AS StagingTable WHERE DestinationTable.[SyncKey] = StagingTable.[SyncKey]) ;',N'@IndexStart int,@IndexEnd int',@IndexStart=0,@IndexEnd=2147483647 

Thanks,
Cuinn.