Drop Index, BCP Import and Create Index (Ver 6.5)

Drop Index, BCP Import and Create Index (Ver 6.5)

Post by Mark L. Bree » Wed, 23 Jun 1999 04:00:00



Hi All,

I have a 6.5 database and I import about 120,000 records each week.  At the
moment, I do not delete any records, however I intend to delete all records
over two months old on a regular basis.  The table I import into has three
indexes, one primary key (non-clustered) and two foreign keys (both
non-clustered).

The process I use at the moment, is as follows
1) I do not use the Select Into / Bulk Import Option (performance is fine
without this additional risk)
2) I drop the three indexes on the table
3) I import the file using BCP
4) I recreate the indexes

My question is what do you think of this process, specifically,
1) the practice of having no clustered index, and
2) the drop index (including Primary Key), Import and Create Index routine.

On the initial timines I ran, if I use a Clustered Index it took a long time
to perform the import and also if I did not drop the indexes, it took a long
time to import the data.

I really just want to get your (Expert) opinions on the procedure I am
using.

Thanks once again, I do appriciate your help.

--
Best Regards

Mark L. Breen
Solution Providers Ltd
Ireland

 
 
 

Drop Index, BCP Import and Create Index (Ver 6.5)

Post by Dan Guzma » Wed, 23 Jun 1999 04:00:00


Mark, your index drop/create process will work fine.  However, you may want
to consider creating a view to BCP the rows out which are less than 2 months
old and then BCP these rows back in along with the new data.  This will
allow your weekly single process to take care of the load and delete.

Clustered vs. non-clustered is dependent on how you access your data.  In
general, you consider the clustered index on the columns you will join or
sort on.

Hope this helps.

 
 
 

Drop Index, BCP Import and Create Index (Ver 6.5)

Post by Mark L. Bree » Fri, 25 Jun 1999 04:00:00


Hi Dan,

Thanks for your reply,

On the Drop Index,
I like your idea of exporting the good records and then importing all the
data, (good and new).

Just to clarify, were you suggesting that I either drop the table or
truncate the table after I have BCPed out the good data ?

On the clustered index,

I was reluctant to use a Clustered Index on the primary key because it is a
29 digit text field and I guessed that it would be difficult to sort and
re-sort on this field, however, I could create a clustered index on one of
the foreign keys, there are only two.

Are you indicating that if you can find a clustered index canditate, you
should almost always use it ?

Once again thanks for your help

--
Best Regards

Mark L. Breen
Solution Providers Ltd
Ireland


Quote:> Mark, your index drop/create process will work fine.  However, you may
want
> to consider creating a view to BCP the rows out which are less than 2
months
> old and then BCP these rows back in along with the new data.  This will
> allow your weekly single process to take care of the load and delete.

> Clustered vs. non-clustered is dependent on how you access your data.  In
> general, you consider the clustered index on the columns you will join or
> sort on.

> Hope this helps.

 
 
 

Drop Index, BCP Import and Create Index (Ver 6.5)

Post by Dan Guzma » Fri, 25 Jun 1999 04:00:00


Mark, simply truncate the table after the BCP out and the index drops.  I
should have mentioned this in my reply.

The width of the clustered index column(s) under 6.5 isn't much of an issue
since the leaf node is the data.  The best clustered candidates are on
columns selected by range of values or are unique.  If you frequently join
on a FK, that may be the best choice.  Otherwise, choose the PK.

Note that with 7.0, the width of the clustered index columns will affect the
size non-clustered indexes as well.  Keep this in mind if you plan to
upgrade.

Hope this helps.



> Hi Dan,

> Thanks for your reply,

> On the Drop Index,
> I like your idea of exporting the good records and then importing all the
> data, (good and new).

> Just to clarify, were you suggesting that I either drop the table or
> truncate the table after I have BCPed out the good data ?

> On the clustered index,

> I was reluctant to use a Clustered Index on the primary key because it is
a
> 29 digit text field and I guessed that it would be difficult to sort and
> re-sort on this field, however, I could create a clustered index on one of
> the foreign keys, there are only two.

> Are you indicating that if you can find a clustered index canditate, you
> should almost always use it ?

> Once again thanks for your help

> --
> Best Regards

> Mark L. Breen
> Solution Providers Ltd
> Ireland



> > Mark, your index drop/create process will work fine.  However, you may
> want
> > to consider creating a view to BCP the rows out which are less than 2
> months
> > old and then BCP these rows back in along with the new data.  This will
> > allow your weekly single process to take care of the load and delete.

> > Clustered vs. non-clustered is dependent on how you access your data.
In
> > general, you consider the clustered index on the columns you will join
or
> > sort on.

> > Hope this helps.

 
 
 

Drop Index, BCP Import and Create Index (Ver 6.5)

Post by Mark L. Bree » Sun, 27 Jun 1999 04:00:00


Will Do, Thanks again Dan,

--
Best Regards

Mark L. Breen
Solution Providers Ltd
Ireland


Quote:> Mark, simply truncate the table after the BCP out and the index drops.  I
> should have mentioned this in my reply.

> The width of the clustered index column(s) under 6.5 isn't much of an
issue
> since the leaf node is the data.  The best clustered candidates are on
> columns selected by range of values or are unique.  If you frequently join
> on a FK, that may be the best choice.  Otherwise, choose the PK.

> Note that with 7.0, the width of the clustered index columns will affect
the
> size non-clustered indexes as well.  Keep this in mind if you plan to
> upgrade.

> Hope this helps.