How to optime the Query

SriniK 2017-06-06 17:56:07

Aaron Bertrand 2017-06-08 12:10:59
My first comment would be I would not use a table variable for this many rows. I also don't know where select * from {IRowset 0xC0419FF505000000} is actually reading data from – what is that? OPENROWSET, OPENQUERY? You can see that the 4 seconds spent there (at least a third of overall time) was spent transferring the data from that query result to @staging. The insert that took over 2 seconds also triggered four stats updates – what happens when you run the query again (without auto-stats kicking in)?