Insert with Openrowset takes a long time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danny Ni - 23 Jul 2008 07:00 GMT

Hi,

I use the following command to insert data into a table from a text file:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

The table has 4 indexes and it take about 2 hours if the text file has 2
million rows. However if the table has no indexes, inserting the same text
file takes about 5 minutes. Someone suggests me to drop the indexes before
insert, then recreate them after. Is this the right thing to do? The text
files I deal with are all about the same size.

TIA

Reply to this Message

Uri Dimant - 23 Jul 2008 07:29 GMT

Danny
Yes , it is good practice (in general) to drop all indexes before inserting
a huge amount of data
Have you also tried

SELECT * INTO #tmp FROM FROM OPENROWSET(BULK...), how long does it take?

> Hi,
>
[quoted text clipped - 9 lines]
>
> TIA

Reply to this Message

Eric Isaacs - 23 Jul 2008 07:42 GMT

Inserts will always take longer when there are indexes involved, so
the idea of dropping the indexes before the bulk insert is totally
valid. I've used this technique myself many times. As you get more
and more data, recreating the indexes will take more and more time,
however. It might be the case that inserting the data takes 5
minutes, but recreating the indexes takes a lot more time.

Select's executed against the table when the indexes are dropped will
take a long time, so there is another cost to removing those indexes
that you need to be aware of. If the table isn't being used for
reading during those bulk inserts, then do consider dropping those
indexes.

Make sure that all those indexes are needed and used regularly.
Reducing the number of indexes could help in either case, but could
increase the time required to select data from those tables.

-Eric Isaacs

Reply to this Message