Need help to move 99 lacs records

Question

Post reply

Need help to move 99 lacs records

Abhijit More

SSCertifiable

Points: 6897
More actions
August 6, 2009 at 4:08 am

#140573

Guru, I want to move 99,00,000 records from one table to another table
I tried below scenarios but its impacting the performance.
1.
INSERT INTO table1
SELECT * FROM table2
2.
ROW-BY-ROW processing using cursor.
need your suggestions.
Abhijit - http://abhijitmore.wordpress.com

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

During a maintenance window I would...

1- Set database to simple recovery model

2- Load table as SELECT INTO

3- Set database back to original recovery model

4- Take a backup

Another alternative would be to bcp out / bcp in.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

free_mascot One Orange Chip Points: 27164 More actions · Answer 2

In addition to the Paul; also you can create the table without any constraint and Primary Key will improve the performance. Later you can create the Primary Key or constraints.

In either case "BCP" or "SELECT INTO" puting db into simple recovery will be good followed by full backup.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

Also consider copying the records in smaller batches.

http://sql-server-performance.com/Community/forums/p/11750/11750.aspx

declare @minID intdeclare @maxID int

declare @batchStart int

declare @batchSize int

set @batchSize = 25000

select @minID = min(id), @maxID = max(id)from sourceTable

set @batchStart = @minIDwhile @batchStart <= @maxID

begin

insert into DestinationTable(....)

select ...

from sourceTable s

where s.id between @batchStart and @batchStart + @batchSize - 1

set @batchStart = @batchStart + @batchSize

end

Adding a WAITFOR inside the loop, may improve the server performance.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

SELECT INTO will not work on an existing table. 99 Lacs is 9.9 million rows. No matter how you slice it, that will take some time especiall if SIMPLE recovery is not set, if the clustered index doesn't support the order of inserts (not to mention all the page splits that will occur), if there are a large number of indexes, and/or if there are triggers on the target table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)