Tuning the query
From the query plan, the most expense is sort which I limited by removing the group by. How can I optimize further on table insert (#reviewTemp;the cost is 23.2%). I also noticed that the estimated rows are much higher than actual rows (2 million vs 450,000). How can I improve this? Any other area that need be tuned? Thanks
You're inserting over 450,000 rows into a table; this is never going to be "cheap" or "free" because those rows have to be written somewhere. This gets better in SQL Server 2014, where some of the eager write algorithms have been vastly improved. This fix has also been implemented in SQL Server 2012 SP1 CU10.
In the meantime, I'd focus less on the insert, and more on the distinct sort, which is the majority of the cost:
- try using SELECT INTO once instead of SELECT TOP 0 INTO and then INSERT.
- Remove the distinct from the main query, and perform a delete after the fact. If you create the right index on the #temp table (especially if the index can be used later on in the code) this might be much cheaper to do afterward than as a part of the population query.