CODEWITHSUNDEEP
sql-servermerge
Agent96
Agent96

Reputation: 475

SQL Server MERGE Slow

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

Upvotes: 3

Views: 3663

Answers (1)

gofr1
gofr1

Reputation: 15987

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.

Upvotes: 1

Related Questions