I've got a table with 40MM records. I've got an incoming data set (~_stage) with about the same # of rows (abbreviated for testing) that will need to insert/update or delete on the ~_master table. I've done this dozens of times, and I've attacked the issue using the tried and true Merge => Condisplit setup shown in "not_sorting.png" attached.
I've already slimmed the ~_master columns down to the bare essentials, and it refuses to sort. I've got 36G of Memory and it's using it all. I'm running in 64 bit mode and I've got multiple, non-system drives, set to be used for the TempSortBufferFolder.
The second screenshot "attempt_num_2.png", shows an alternate attempt at the same deal. In this case, I'm issuing a custom SELECT ... ORDER BY on that same slimmed down column set, but it's still crawling.
40MM is actually an abbreviated set. Production will be 200MM, and could double by the end of 2012.
What's the "right" way to do this SSC? I'm at the keys all day and will respond quickly. THANKS!