0

I am inserting around 2 million records into a SQL Server 2005 table. The table currently have clustered as well as non-clustered indexes. I want to improve the performance of the insert query in that table . Can anyone have idea about

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ved Prakash
  • 1
  • 1
  • 1
    Also, how are you currently inserting data? Are you using bulk insert? Are you using INSERT statements? Are you inserting one row at a time or multiple rows at once? – Mark Byers Dec 20 '11 at 12:09
  • 1
    @TobyAllen: Do we know for certain that this is homework? –  Dec 20 '11 at 12:31
  • I am using SQL server 2005 database system . And i am not using the bulk insert i am using the insert into table_name statement and currently inserting multiple rows at once.. – Ved Prakash Dec 20 '11 at 12:48
  • Three questions: 1) Where are you getting the 2 million records to be added 2) Can you please post the current table structure? 3) How many records are in the current table? These are relevant if you want specific answers – UnhandledExcepSean Dec 20 '11 at 13:01
  • Currently i am pulling out the data from the same database by using the select queries . Currently there are around 10 million records are present in table. – Ved Prakash Dec 20 '11 at 13:13
  • I am not able to post the Structure of the Table due to size limit of the comment. currently there are around 112 columns present in the table – Ved Prakash Dec 20 '11 at 13:16

5 Answers5

2
  1. Drop all the indexes (including primary if your data for insert are not preordered with the same key)
  2. Insert the data
  3. recreate all the dropped indexes
Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • 4
    If you're talking about SQL Server: I would recommend **not** dropping the clustering index for sure! Otherwise, your table will be converted into a heap before inserting, and then back into a clustered table after the insert - both of which are extremely time- and resource-intensive operations... – marc_s Dec 20 '11 at 12:11
  • yes, agree, but only when there are already a huge amount of data in the table – Oleg Dok Dec 20 '11 at 12:23
  • Dropping or disabling the index and after that again re-creating or re-building the index will consume more time. because that table already contains records and i have tried this thing but it is not benefiting me – Ved Prakash Dec 20 '11 at 12:45
  • Dropping indexes other than the PK can help. If you have a clustered PK and can sort the data via the PK on the insert then you can keep the clustered PK with no or little hit. – paparazzo Dec 20 '11 at 14:56
1

You can try to disable the indexs on the table before inserting and enabling them again after. It can be a huge timesaver if you're inserting large amounts of data into a table.

Check out this article for SQL server on how to do such a thing: http://msdn.microsoft.com/en-us/library/ms177406.aspx

Willem Meints
  • 1,152
  • 14
  • 33
1

If there is no good reason you aren't using bulk-insert, I'd say that your best option is to do this. Ie: Select rows into a format you can then bulk re-insert.

By doing ordinary inserts in this amount, you are putting a huge strain on your transaction logs.

If bulk-insert is not an option, you might win a little bit by splitting up the inserts into chunks - so that you don't go row-by-row, but don't try to insert and update it all in one fell swoop either.

I've experimented a bit with this myself, but haven't had the time to get close to a conclusive answer. (I've started the question Performance for RBAR vs. set-based processing with varying transactional sizes for the same reason.)

Community
  • 1
  • 1
0

You should drop the indexes and then insert data and then recreate indexes.

Alok
  • 266
  • 2
  • 14
-2

You can insert up to 1000 rows in one insert.

values (a,b,c), (d,f,h)

Sort the the data via the primary key on the insert.

Use with (hold lock)

paparazzo
  • 44,497
  • 23
  • 105
  • 176