inserting huge volume of records in to table-slow

Question

0.00/5 (No votes)

See more:

Hi I need to insert 100000 records into the master table from temperory table.
i have 100000 records in the temporary table.here i am doing validation for each record and i am moving to master table.

problem is...
first 10000 records are inserting very fast(1 min).
next 10000 records are bit slow compare than first 10000(2 min) then 4 min ......
like this it is going on.
i dont know what is happening.
Please anyone help me..

thanks and regards
Arun.

Posted 31-May-12 1:28am

Arun.Mohandas

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

db7uk · Answer 1 · 2012-05-31T01:50:00

There are many reasons that this could happen because of logging (to the transaction log file) etc.

First take a look at this regarding bulk inserts

http://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/[^]

If this does not help then I would consider adding an exclusive lock on the table while inserting records. This would improve the performance a bit as no other writes or reads could be done.

http://msdn.microsoft.com/en-us/library/dd425070(SQL.100,printer).aspx[^]

If you can, and you know a little bit about SSIS, I would really recommend using that for all mass / bulk inserts. SSIS is optimised for things like this.

http://f5debug.net/2011/10/12/free-ebook-download-sql-server-integration-services-ssis-step-by-step-tutorials-version-2-0/[^]

Dylan Morley · Answer 2 · 2012-05-31T02:09:00

It could also be related to indexes on the table. If you have a number of indexes, then as you insert it has to 'reorganise' the indexes for the new data you have inserted. As more data is inserted into the table, the indexes have to reorganise over a larger set of records which is why you see this kind of degenerative behaviour with inserts becoming slower and slower.

In particular, have a look at the clustered index on the master table. Make sure that the data you are inserting is in the order expected by the clustered inex..

I've had behaviour like this before and sometimes it is actually quicker to drop all indexes except the clustered, insert data in the correct order (at the 'end' of the clustered index, so not to cause a clustered rebuild) then recreate the indexes after all data inserted. I've implemented things like this for overnight rebuilds \ ETL.

Can you post your master table structure? In SSMS, right mouse on table -> Script Table As -> Create - then post the results here.

inserting huge volume of records in to table-slow

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0