I have to work with sets with millions of rows in my SQL Server database.
Almost all my commands use the MERGE statement. I merge here and there, this table and that table. I use stored procedures for that as I believe that would provide the best performance.
I found out that the process is too slow for me (merging 2kk rows - seemingly random from 5 to 50 seconds), that is, the performance is not as it would seem the best, and for some reason I couldn't find the reason: I opened the Task Manager and Resource Monitor, initiated some heavy MERGE queries, ensured that no scans or other unpleasant items are present in the actual execution plan. And I saw very low CPU, memory, and disk drive consumption. The reason for this is still not known to me.
Lately I also found out that memory optimized tables coupled with native stored procedures are supposed to provide better performance.
It turns out that Merge isn't available for MO-tables, and MO-tables aren't available for native stored procedures.
The only example of substituting Merge on MSDN uses weird approach, based on while loop and an extra column RowId to "access a row by index". Well, I learned some more info, I repeated the MSDN example, established some hash index with lots of buckets (8kk) on that column and indeed, there was some performance gain.
But again, each iteration in the loop requires at least two index seeks, as well as insert/update/delete when the conditions are met.
So I ask: is there any useful strategies for placing a really performant merge? Is there any way to, I don't know, may be writing a CLR procedure? Would that be better? Any other tips or hints?