INSERT into an indexed table

[djj55]

Hello, SQL 2000 I am trying to insert ~40K records from a temporary table into a large table (10 columns, 41M records) that has several indexes (six indexes including PK). Currently I am using

insert into bigtable (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10) 
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 
from #temp

Is this the best way, it takes about ~20 minutes.

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Wait. It takes 20 minutes to insert 40,000 rows?

Are there any triggers on this table?

sp_helptrigger 'BigTable'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you for your reply.

The sp_helptrigger returns zero rows.

I think the time is a function of our wimpy server. I am told a new computer will be ready but it has been two months now. (Also SQL 2008/Windows 2008)

The server will go to 100 percent disk busy on the data drive which brings things to a crawl at best. Is there a resource management setting I am missing or a server tweak?

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

20 minutes to insert 40,000 rows is just.... horrible.

You could try dropping your indexes (I recommend leaving the PK index). Run your inserts, and then re-create the indexes. This should speed things up a little.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you again for your reply.

I was wondering about that.

I had a major update (new column) that I did on a development server and wanted to move to the production server. Took so long I had to kill the process to free up the production server.
So Friday night when no one was working I dropped the indexes, did the copy, recreated the indexes and it took less than an hour.

Thanks

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!