Extrem slow INSERT without reason
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...
The target table had an ID value with DEFAULT (NEXT VALUE FOR pld.s_leistungswertid) ). The sequence was created with CACHE = 20 -> ~5000 updates to the system table which manages the sequences.
After changing the Sequence to CACHE = 200 the INSERT takes only ~11 seconds and with CACHE = 20000 only 1,5 seconds.