Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Can I drop indexes during large (60 million row) SQL 2005 Insert!

I am doing an insert from one server to another...the source table is 60 million records, and it is taking forever....I need to drop the indexes (there are 10 of them) on the destination table, but am only about 19 million records through the insert.  Is this possible - i.e. can I open a separate session in SQL Management Studio and drop the indexes?
0
p_love
Asked:
p_love
  • 4
  • 2
  • 2
  • +3
2 Solutions
 
SQL_SERVER_DBACommented:
You should be fine
0
 
p_loveAuthor Commented:
Thanks...

will it help?!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello p_love,
>can I open a separate session in SQL Management Studio and drop the indexes?

I am really not sure abt this, but i recommend you stop the copying opn , drop the index and then start copying.



Aneesh R
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
p_loveAuthor Commented:
Hi,

Having waited 8 hours...I am reluctant to start again.  I have a finite window to do this in.  I did a test run on Sunday and it took only 4 hours.  I have a job starting in 8 hours which will be fairly dependent on this data being present.
0
 
SQL_SERVER_DBACommented:
I don't think it will help
0
 
Scott PletcherSenior DBACommented:
Is the data coming from an existing table or from a file(s)?

If file(s), you should use BULK INSERT, as that is the fastest method for loading large volumes of data.

Dropping indexes would certainly help prior to the load starting.  Not sure if it will work now.  You could try running a test and if that doesn't cause problems, try it live.

Also, if there any trigger(s) on the table, disabling them, if possible, would also speed the load.
0
 
wittysloganCommented:
does dts load data faster?
0
 
Scott PletcherSenior DBACommented:
No; BULK INSERT is much faster than DTS.
0
 
wittysloganCommented:
IS the server to server thing the problem?
If it is can't you just backup and restore the database over to the target server and then do the bulk insert on the same server?
0
 
p_loveAuthor Commented:
Hi,

One server is in Sweden and the other is in the US.  I am using OpenQuery.  It took 4 hours on a test run on Sunday, but 9 hours today.  I have cancelled and will investigate further.  I may do a RAW export via SSIS, then do a RAW import.

Thanks

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<I am doing an insert from one server to another...the source table is 60 million records, and it is taking forever....I need to drop the indexes (there are 10 of them) on the destination table, but am only about 19 million records through the insert.  Is this possible>>
Use BULK INSERT...and make sure you have no network bottlenecks...

Hope this helps...
0
 
p_loveAuthor Commented:
Use BULK INSERT...and make sure you have no network bottlenecks...

Hi,

I appreciate this.  The question however wasnt, how could I do this better, it was - can I drop the indexes "during" a large insert.   My only worry with bulk insert is I have to do a bulk export first and didnt fancy it on 60 million records...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<can I drop the indexes "during" a large insert.>>
Yes.  But it is a tedious process to drop  them (you must drop constraints too) and recreate them:  you will have to do lots of scripting for this  Indexes are *not* the primary reason for which your inserts takes forever...Replacing *inserts* by *bulk inserts* is easy to do and you don't have to worry about indexes.

Hope this helps...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now