Click here to Skip to main content
13,194,662 members (70,220 online)
Rate this:
Please Sign up or sign in to vote.

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.

Posted 11-Dec-12 4:18am
Updated 11-Dec-12 4:20am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171018.2 | Last Updated 11 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100