Click here to Skip to main content
14,837,944 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I need to copy a parent tabel into another child table.
where the parent table contains a lot of records i.e about 22224171 records.

I need to decress the copying time.
i have already applied index to 4 columns in the child table.

Could any one help me to solve this performance issue..

Thanks in advance.

Updated 11-Dec-12 4:20am

1 solution

Copy large amounts of data into a table is a balancing act between the data you are inserting and the indexes on that table. Have a look at this question on stack overflow...[^]

"There is overhead in maintaing indexes during the insert and there is overhead in rebuilding the indexes after the insert. The only way to definitively determine which method incurs less overhead is to try them both and benchmark them. "

You'll definitely want to batch up the inserts into smaller chunks, if you're just performing one giant INSERT statement it's all going to be handled by a single transaction which will cause large tlog growth and wont be the fastest.

I have found this pattern works with large amounts of data....(this would be an SSIS package to perform the ETL).

* Drop all indexes on destination table *apart from clustered index*
* Begin bulk insert operation on destination table - (e.g do this in batches of 1000 records).
* Rebuild indexes on destination table

It totally depends on the clustered index as well. I remember doing some work on sales data, and the data was clustered by financial date rather than just an autoincrementing field. In this case, it was important to insert the data into the desintation table in the order of the clustered index (e.g. have your source data sorted before trying to insert it)

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900