I am moving bulk data to archive tables.

Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns.

I want too increase the performance for the selection by using a temporary non clustered index and drop that index after select operation.

I am also setting data base logging to bulk logging while performing the bulk operation.

Will that really help, or it is even possible o not.

Any other suggestions are also welcome.

  • 2:15 for 4500 records? Or is that 4.5million? Is it just a simple SELECT FROM <TABLE> or are there joins? It seems very slow regardless of the indexing if it's just simply selecting 4,500 records from one table – Mark Sinkinson Dec 9 '14 at 8:53
  • @MarkSinkinson its 4.5million – Vinay Pratap Singh Dec 9 '14 at 9:35
  • The benefit would depend on the query, but you can easily check how much time the index creation adds to see if it's worth it initially. – LowlyDBA - John M Dec 9 '14 at 14:10
  • Please add the schema of the source table and the query you use to move data. – Thomas Kejser Dec 9 '14 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.