Anatomy of an Incremental Load

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Leonard » Anatomy of an Incremental Load

102 posts, Page 10 of 11

«««7 8 9 10 11 »»

Anatomy of an Incremental Load

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Leonard

Posted Tuesday, June 08, 2010 12:14 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947

Hi Cyril,

There are use cases where Truncate-and-Load will perform better than an Incremental Load.

Keep in mind that SQL Server - and SSIS - performance is impacted by lots of outside factors, starting with network topology and extending to the size and shape of the data itself.

The science of Lookups is a field all by itself, and I barely touched on it in this article. In other writing I talk about Change Detection (SQL Server 2008 Change Data Capture is one mechanism) which offers some relief to some incremental loads. Note this will not solve everything all the time - and if I didn't make that clear earlier allow me to do so now: there are some use cases for which Incremental Loading is *not* the answer. You may very well have hit upon one.

As an engineer, I always recommend testing to see which will perform best. It sounds as if you have performed these tests and found your best solution.

:{> Andy

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard

Post #934188

cyril.capistrano

Posted Tuesday, June 08, 2010 12:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 26, 2012 1:41 PM
Points: 5, Visits: 33

That was the first post I've ever done and I thank you for your useful response. I might just post some more!

Thanks! Smile

Post #934195

Andy Leonard

Posted Tuesday, June 08, 2010 12:58 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947

Hi Cyril,

I encourage you to not only post in the forums - you should also consider writing about your experiences! Steve Jones is always on the lookout for new material and authors.

:{> Andy

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard

Post #934215

cyril.capistrano

Posted Tuesday, June 08, 2010 1:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 26, 2012 1:41 PM
Points: 5, Visits: 33

Solid!

Post #934217

epriddy

Posted Thursday, June 24, 2010 11:24 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, October 22, 2012 4:46 PM
Points: 77, Visits: 110

Love your tag :)

Post #942608

mhassanshahbaz

Posted Monday, December 13, 2010 11:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 13, 2010 11:10 AM
Points: 6, Visits: 21

hi andy!

above examples are working when primary key column data type is int,but not in case of var char, as i have course in course_dim table,what to do with this case??

2)when it found & loaded the new row dwh,next time it again loading that same one along new rows,why?

3)i want to send "new" & changed rows to destination,what to do in this case,describe it too plz

hope to hear from you soon :)

Post #1033984

FairFunk

Posted Thursday, January 13, 2011 4:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 41, Visits: 1,092

Hi Andy,

Great post, it really helped me out of a bind!

Keep up the good work, as its really hard to find good SSIS tutorial examples.

Cheers
Chris

Post #1047169

rockstar283

Posted Saturday, June 04, 2011 12:13 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:30 PM
Points: 84, Visits: 385

Guys..what is your opinion about indexes..as per my knowledge indexes are heavily used in OLAP..but I generally disable the indexes before loading new data and again enable them back after loading the data..do you think its a good strategy?

Post #1119866

Andy Leonard

Posted Sunday, June 05, 2011 3:00 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947

rockstar283 (6/4/2011)

Indexes can slow inserts. If they cause enough pain, I drop them before the load and then re-apply afterwards.

:{>

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard

Post #1119987

kaalabhairavaa

Posted Monday, March 19, 2012 12:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 11, 2013 10:09 AM
Points: 1, Visits: 12

@Andy

That was simply Amazing explanation about Incremental Load using SSIS.

Thanks and Appreciated for all your Help.

Bheeshma K

Post #1269153

« Prev Topic | Next Topic »

102 posts, Page 10 of 11

«««7 8 9 10 11 »»

Permissions