Extrem slow INSERT without reason

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...

avatar image By Thomas Franz 16 asked Aug 04, 2017 at 11:57 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

I found the culprint by myself.

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.

avatar image By Thomas Franz 16 answered Aug 04, 2017 at 12:22 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Aug 04, 2017 at 11:57 AM

Seen: 64 times

Last Updated: Aug 04, 2017 at 12:25 PM