Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»

Anatomy of an Incremental Load Expand / Collapse
Author
Message
Posted Tuesday, June 8, 2010 12:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
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
Posted Tuesday, June 8, 2010 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 2:54 PM
Points: 5, Visits: 34
That was the first post I've ever done and I thank you for your useful response. I might just post some more!

Thanks!
Post #934195
Posted Tuesday, June 8, 2010 12:58 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
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
Posted Tuesday, June 8, 2010 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 2:54 PM
Points: 5, Visits: 34
Solid!
Post #934217
Posted Thursday, June 24, 2010 11:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 22, 2012 4:46 PM
Points: 77, Visits: 110
Love your tag :)
Post #942608
Posted Monday, December 13, 2010 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, January 13, 2011 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:26 AM
Points: 47, Visits: 1,365
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
Posted Saturday, June 4, 2011 12:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 114, Visits: 523
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
Posted Sunday, June 5, 2011 3:00 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
rockstar283 (6/4/2011)
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?


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
Posted Monday, March 19, 2012 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase «««7891011»»

Permissions Expand / Collapse