Is there a need to rebuild indexes after bulk insert operation 
Author Message
 Is there a need to rebuild indexes after bulk insert operation

Swami,

Bulk Insert updates indexes.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hi All,
> We load data in to our DataMart every monday. We use DTS
> (Bulk Insert) to load data.
> The question is if we need to rebuild indexes after this
> load or does bulk insert update the indexes existing on
> these tables while inserting data?
> Thanks



Sun, 16 May 2004 07:25:20 GMT
 Is there a need to rebuild indexes after bulk insert operation

Yeah, but be careful: If you have more than one connection trying to bulk
insert into a particular table at the same time, I think you can hose your
indices. BOL has a pretty good writeup on safely doing bulk inserts.


Quote:
> Swami,

> Bulk Insert updates indexes.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Hi All,
> > We load data in to our DataMart every monday. We use DTS
> > (Bulk Insert) to load data.
> > The question is if we need to rebuild indexes after this
> > load or does bulk insert update the indexes existing on
> > these tables while inserting data?
> > Thanks



Sun, 16 May 2004 08:31:57 GMT
 Is there a need to rebuild indexes after bulk insert operation
Christian,

Can you cite a specific reference to support your statement? I'm not aware
of any problems with multiple Bulk Inserts operating simultaneously, but
that's not to say that you're not correct ... I just have never heard of
such a problem.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Yeah, but be careful: If you have more than one connection trying to bulk
> insert into a particular table at the same time, I think you can hose your
> indices. BOL has a pretty good writeup on safely doing bulk inserts.



> > Swami,

> > Bulk Insert updates indexes.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Hi All,
> > > We load data in to our DataMart every monday. We use DTS
> > > (Bulk Insert) to load data.
> > > The question is if we need to rebuild indexes after this
> > > load or does bulk insert update the indexes existing on
> > > these tables while inserting data?
> > > Thanks



Sun, 16 May 2004 10:20:01 GMT
 Is there a need to rebuild indexes after bulk insert operation
Sure - in BOL (in SQL Server 2000), look up "parallel data load" in the
index search tab. I think that gives all the detail I was trying to convey.

Hope this helps,
Christian


Quote:
> Christian,

> Can you cite a specific reference to support your statement? I'm not aware
> of any problems with multiple Bulk Inserts operating simultaneously, but
> that's not to say that you're not correct ... I just have never heard of
> such a problem.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Yeah, but be careful: If you have more than one connection trying to
bulk
> > insert into a particular table at the same time, I think you can hose
your
> > indices. BOL has a pretty good writeup on safely doing bulk inserts.



> > > Swami,

> > > Bulk Insert updates indexes.

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > Hi All,
> > > > We load data in to our DataMart every monday. We use DTS
> > > > (Bulk Insert) to load data.
> > > > The question is if we need to rebuild indexes after this
> > > > load or does bulk insert update the indexes existing on
> > > > these tables while inserting data?
> > > > Thanks



Sun, 16 May 2004 11:22:58 GMT
 Is there a need to rebuild indexes after bulk insert operation
Christian,

Thanks for the reference, but I don't see anything in there about Bulk
Insert corrupting indexes. The section, unless I'm missing something, is
just indicating how to optimize multiple Bulk Inserts.

Perhaps you could quote the specific passage that leads you believe that
simultaneous execution of Bulk Inserts can corrupt indexes?

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Sure - in BOL (in SQL Server 2000), look up "parallel data load" in the
> index search tab. I think that gives all the detail I was trying to
convey.

> Hope this helps,
> Christian



> > Christian,

> > Can you cite a specific reference to support your statement? I'm not
aware
> > of any problems with multiple Bulk Inserts operating simultaneously, but
> > that's not to say that you're not correct ... I just have never heard of
> > such a problem.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Yeah, but be careful: If you have more than one connection trying to
> bulk
> > > insert into a particular table at the same time, I think you can hose
> your
> > > indices. BOL has a pretty good writeup on safely doing bulk inserts.



> > > > Swami,

> > > > Bulk Insert updates indexes.

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.


message

> > > > > Hi All,
> > > > > We load data in to our DataMart every monday. We use DTS
> > > > > (Bulk Insert) to load data.
> > > > > The question is if we need to rebuild indexes after this
> > > > > load or does bulk insert update the indexes existing on
> > > > > these tables while inserting data?
> > > > > Thanks



Sun, 16 May 2004 11:47:05 GMT
 Is there a need to rebuild indexes after bulk insert operation
    No! Why? This is a powerful way of bulk loading data by say splitting
the file into different streams & doing parallel loads. This is a very
critical feature for data warehouses & I have used this with no problems
whatsoever.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Mon, 17 May 2004 13:41:07 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Tutility: after rebuild no index operations possible?

2. Roll back multiple BULK INSERT operations

3. having a prob with bulk insert operation

4. Tempdb can't handle large bulk insert operations

5. Error when bulk insert follows another large bulk insert

6. Difference in ranking results from a full index rebuild verses an incremental rebuild

7. rebuild or not rebuild index file, when ?, how?

8. Need Script in order to rebuild all indexes

9. BULK INSERT requires index recreation ?

10. Bulk Insert and index statistics

11. Need 2 rebuild indexes??


 
Powered by phpBB® Forum Software