Indexes on Bulk Insert data

[Chris]

Any help would be appreciated.

I am running a script that does the following in succession.

1-Drop existing database and create new database
2-Defines tables, stored procedures and functions in the database
3-Imports data using bulk insert
4-Analyzes data using stored procedures

I would like to improve the performance of the analysis in step 4 by
creating indexes in step 2.

Question 1-Are indexes updated when data is bulk inserted? I know they are
when using normal insert, update, or delete T-SQL but I am not sure about
bulk insert of data.

Question 2-Do I need to update the index statistics in any way or would they
be ready to use in step 4.

Thanks,
CJ

[Ray Higdon]

I would define step 4 as create indexes, that will have your stats up to
date and save you from any performance issues during the load or having
to reindex or update the stats

I would do the analysis in step five (depending what type of analysis)

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Chris (ch***@hrn.org) writes:

1-Drop existing database and create new database
2-Defines tables, stored procedures and functions in the database
3-Imports data using bulk insert
4-Analyzes data using stored procedures

I would like to improve the performance of the analysis in step 4 by
creating indexes in step 2.

Question 1-Are indexes updated when data is bulk inserted? I know they are
when using normal insert, update, or delete T-SQL but I am not sure about
bulk insert of data.
Yes, they are. However, you may prefer to wait with creating indexes until
you have loaded the data for best performance. You may also opt to create
clustered indexes before bulk-loading and add non-clustered indexes after.
This is particularly appealing if the order in the data files corre-
sponds to the clustered indexes.
Question 2-Do I need to update the index statistics in any way or would
they be ready to use in step 4.

If you create indexes after bulk-loading, SQL Server will create statistics
for you when creating the indexes.

If you create indexex before bulk-loading, the statistics will not be
correct after the load. Thus, it can be a good idea run UPDATE STATISTICS
in this situation. However, if you don't, SQL Server will auto-update
statistics, unless you have turned off this feature.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp