Optimizations

I currently have a nightly process that truncates a set of tables prior to
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of th
e
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless th
e
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail
.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:

> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew