The first query (History panel) in the attached SQL Sentry Plan Exlorer file contains a unpivoted SELECT on sync.t_sid_abruf with a LEFT JOIN to the target table (pld.t_leistungswerte). Since the target is empty, it would return all ~78k rows from the source (~98k after the pivot). It will return the results in ~0.2 seconds.
The second query is exact the same, except that I added this time an INSERT into the target table (which has two foreign keys to small lookup tables (< 10 rows each)).
When I execute the second query it takes ~2 minutes just to insert the 98k rows and I have absolut no idea, why.
Yes, it is (whyever) using two MERGE JOIN to verify the two FKs and it needs two expensive SORTs, which are spilling to TEMPDB, because the optimizer seems to think that the semi right join to the empty target could reduce the number of rows, but we are talking about < 100k rows (2,5 MB granted memory) and the query runs on my local SSD (SQL Server 2014 Dev) without any other SQL load....
Recompiling, statistic updates, placing the UNPIVOTed dataset in a #TempTable does not help...
By Thomas Franz 16 asked Aug 04, 2017 at 11:57 AM
I found the culprint by myself.
The target table had an ID value with
After changing the Sequence to
By Thomas Franz 16 answered Aug 04, 2017 at 12:22 PM