Incremental Load V.S. Bulk Load

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

New to SSIS - 29 Jul 2008 17:57 GMT

Hi,
has anyone done/compared the speed between incremental Load and Bulk
Load?
I have a table over 20 million records and it has been using bulk load
(crate table, load and rename) everynight.
I am thinking of doing an incremental load. Since incremental load has
to do the comparison between source and destination to know which one
to load/deleted, I am thinking that incremental Load would be slower.
However, has anyone tried this?
any suggestion?

Thank you

Reply to this Message

Todd C - 29 Jul 2008 18:58 GMT

Hello Newbie:
I think that is going to depend on how you determine *WHAT* constitutes and
incremental load. How would you determine what has changed?

Is this a transactional system where records CANNOT change but ONLY be
added? If so, can you extract from the source ONLY those records that were
added since the last load? With that info you have just reduced your workload.

If records do change, can you identify them? Is there a Datetime stamp field
in the table?

20 Million rows is not too extreme, but could certainly be trimmed down a bit.

Signature

Todd C

> Hi,
> has anyone done/compared the speed between incremental Load and Bulk
[quoted text clipped - 8 lines]
>
> Thank you

Reply to this Message

Marco Russo - 30 Jul 2008 09:59 GMT

> Hi,
> has anyone done/compared the speed between incremental Load and Bulk
[quoted text clipped - 8 lines]
>
> Thank you

It really depends.
In general, if you don't have something (like a timestamp) on the
source data which allows you to get only the changed data, the
comparison might be slower than importing everything.
However, sometime you have to get all the data and identify changed
records for other reasons (i.e. SCD Type II) and in this case you
cannot avoid to do that if you don't have a timestamp. In similar
scenarios, take a look at the TableDifference component (http://
www.sqlbi.eu/tabledifference.aspx) that allows to implement this kind
of comparison into SSIS with high performance.

Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo

Reply to this Message

Dhivya - 29 Sep 2008 13:41 GMT

Hi
Can anyone tell which is the better way to do a incremental data load to a
table with a clustered index only?

Will partition help in incremental data load?

Thanks,
Dhivya

> > Hi,
> > has anyone done/compared the speed between incremental Load and Bulk
[quoted text clipped - 23 lines]
> http://www.sqlbi.eu
> http://sqlblog.com/blogs/marco_russo

Reply to this Message