DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com