SQL Server MERGE Slow

Agent96

I am importing data into SQL Server using the MERGE function, and it is extremely slow. I am sure this is because I am not doing things the best way, but I am not sure what the best way is.

To explain, I am using Java, I am issuing a batchUpdate with packets of 5k MERGE statements (I have tried various batch sizes). The merge makes use of parameters, with the values set on the batchUpdate for example:

MERGE [MY_DATABASE_TABLE] AS TARGET
        USING (SELECT ?, ?, ?, ?, ?, ?, ?, ?)  AS SOURCE

The ? parameters are replaced with the actual values when I run the batchUpdate. My MERGE statement does have a number of following conditional checks when matched (if the source is null and the target is not null, for example)

To give an indication of performance, for 38k rows with a straight INSERT statement, the time is 8 seconds, with a MERGE it is 3 minutes. This time seems to increase exponentially as the number of rows increases (90k rows=23mins). The same functionality in MYSql is 10 seconds (MySQL uses on duplicate key update). As SQL Server then locks my database table for the duration of the MERGE (23mins!) and my 90k rows is a relatively small test file, this performance is extremely undesirable.

This must be something I have not optimised. My 'MERGE On' columns in my Target database table are all Indexed (UNIQUE KEY), so it is not this. I can not index my source as my source is not a database table. This makes me wonder if I should first import all the data into a temporary table, and then merge from that?

My Question is: Should the MERGE as I have it be expected to be quicker, or is MERGE only fast if used with temporary tables which are Indexed?


Note: I am using the sqljdbc4 v 4.0.x driver

Note: batchUpdate is provided by org.springframework.jdbc.core.JdbcTemplate

gofr1

Instead of MySQL - SQL Server's MERGE can not ignore duplicate keys. And even if IGNORE_DUP_KEY is set to ON for any unique index on the target table, MERGE will ignore this setting.

Yes MERGE will be quicker with indexed temporary table.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

SQL Server MERGE Slow

分類Dev

AWS RDS SQL Server failover really slow

分類Dev

Merge data into one column - sql server 2012

分類Dev

Merge multiple rows into one row using SQL Server?

分類Dev

SSMS: Merge two Excel sheets into a single SQL Server table

分類Dev

SQL Server- Merge rows into one column and take maximum count

分類Dev

SQL Server - Merge multiple query results into one result set

分類Dev

tar slow on Ubuntu Server

分類Dev

Extremely Slow Query which contains multiple Sub-Queries SQL Server 2008 R2

分類Dev

db2 から SQL Server への MERGE INTO 変換

分類Dev

PROC SQL MERGE MISMATCH

分類Dev

ubuntu server on virtual box, slow performance troubleshoot

分類Dev

SQL Server AND AND OR AND AND

分類Dev

SQL multi table query working slow

分類Dev

How to merge different rows in SQL

分類Dev

Oracle merge sql no rows inserted

分類Dev

SQL merge result of two columns

分類Dev

What does the `Rebase and merge` merge strategy do on Bitbucket Server?

分類Dev

SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE

分類Dev

Should I upload css + maps to the server? And does it slow down the website?

分類Dev

SQL Server:MERGEステートメントを使用して2つのテーブルを更新する

分類Dev

SQL Server:MERGE後の非クラスター化インデックス(挿入/更新)

分類Dev

MERGE を使用して MS SQL Server Upsert クエリを作成しようとしています

分類Dev

SQL merge not matched by target vs not matched by source

分類Dev

How to create Merge SQL when with duplicated data

分類Dev

Detect and merge date range successive overlaps in SQL

分類Dev

How to merge sql query with union into one query

分類Dev

SQL MERGE Statement with More Than 2 Tables

分類Dev

Why my sql query is so slow in one database?

Related 関連記事

  1. 1

    SQL Server MERGE Slow

  2. 2

    AWS RDS SQL Server failover really slow

  3. 3

    Merge data into one column - sql server 2012

  4. 4

    Merge multiple rows into one row using SQL Server?

  5. 5

    SSMS: Merge two Excel sheets into a single SQL Server table

  6. 6

    SQL Server- Merge rows into one column and take maximum count

  7. 7

    SQL Server - Merge multiple query results into one result set

  8. 8

    tar slow on Ubuntu Server

  9. 9

    Extremely Slow Query which contains multiple Sub-Queries SQL Server 2008 R2

  10. 10

    db2 から SQL Server への MERGE INTO 変換

  11. 11

    PROC SQL MERGE MISMATCH

  12. 12

    ubuntu server on virtual box, slow performance troubleshoot

  13. 13

    SQL Server AND AND OR AND AND

  14. 14

    SQL multi table query working slow

  15. 15

    How to merge different rows in SQL

  16. 16

    Oracle merge sql no rows inserted

  17. 17

    SQL merge result of two columns

  18. 18

    What does the `Rebase and merge` merge strategy do on Bitbucket Server?

  19. 19

    SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE

  20. 20

    Should I upload css + maps to the server? And does it slow down the website?

  21. 21

    SQL Server:MERGEステートメントを使用して2つのテーブルを更新する

  22. 22

    SQL Server:MERGE後の非クラスター化インデックス(挿入/更新)

  23. 23

    MERGE を使用して MS SQL Server Upsert クエリを作成しようとしています

  24. 24

    SQL merge not matched by target vs not matched by source

  25. 25

    How to create Merge SQL when with duplicated data

  26. 26

    Detect and merge date range successive overlaps in SQL

  27. 27

    How to merge sql query with union into one query

  28. 28

    SQL MERGE Statement with More Than 2 Tables

  29. 29

    Why my sql query is so slow in one database?

ホットタグ

アーカイブ