Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server performance Tables , +
Hi,
 
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.
 
Regards
Victor
Posted 11-Dec-12 5:18am
Edited 11-Dec-12 5:20am
v2

1 solution

Rate this: bad
good
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...
 
http://stackoverflow.com/questions/6955456/drop-rebuild-indexes-during-bulk-insert[^]
 
"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)
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 11 Dec 2012
Copyright © CodeProject, 1999-2014
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