which insert is faster? with identity_insert on or identity_insert off

Question

which insert is faster? with identity_insert on or identity_insert off

karthik babu

Hall of Fame

Points: 3335
More actions
September 14, 2014 at 10:40 pm

#397341

I got this basic question while inserting huge number of records from production table to the test environment. Please let me know.
Thanks in advance.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

karthik babu (9/14/2014)
I got this basic question while inserting huge number of records from production table to the test environment. Please let me know.
Thanks in advance.

Quick questions:

1. Is the identity column the clustered index key?

2. Can you force the constantly increasing order of the insert?

3. Do the identity values need to be in sync?

4. What are you using for transferring the data?

5. Could restoring from production backup be an option?

6. Is there a clustered index on the table?

7. Are there other indexes on the table?

The answer is Normally not but it depends.

😎

karthik babu Hall of Fame Points: 3335 More actions · Answer 2

PFB the response...

1. Is the identity column the clustered index key? - Yes

2. Can you force the constantly increasing order of the insert? - No

3. Do the identity values need to be in sync? - Not necessary

4. What are you using for transferring the data? - SSMS Query Analyzer

5. Could restoring from production backup be an option? - Yes but size of DB is so huge, so stick to one particular table

6. Is there a clustered index on the table? - Yes

7. Are there other indexes on the table? - Yes

BTW it took 32 mins to complete 60 million records. 🙂

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Here is my suggestion

Leave the identity to the destination table, don't use the identity_insert. Consider dropping the non clustered indexes and recreate them after the load. In addition, an SSIS package with an OLE DB destination and fastload could help speed this up further.

😎

Yet another question, are the two databases on the same server?

karthik babu Hall of Fame Points: 3335 More actions · Answer 4

I believe this is a good solution and it will work better than with identity_insert on.

Thnx.

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

ScottPletcher SSC Guru Points: 100948 More actions · Answer 5

The big delay is likely in allocating and formatting log space, not in the identity number assignment, which is trivial.

Thus, before you run the big INSERT, pre-allocate sufficient log space to handle logging for the whole statement.

Or, if the db is in simple mode and/or you can backup the log between batches, break the INSERT into multiple batches, with a CHECKPOINT inbetween each, rather than a single big statement.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.