June 8, 2010 at 12:14 pm
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, Chief Data Engineer, Enterprise Data & Analytics
June 8, 2010 at 12:24 pm
That was the first post I've ever done and I thank you for your useful response. I might just post some more!
Thanks!:-)
June 8, 2010 at 12:58 pm
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, Chief Data Engineer, Enterprise Data & Analytics
June 8, 2010 at 1:09 pm
Solid!:cool:
June 24, 2010 at 11:24 am
Love your tag 🙂
December 13, 2010 at 11:12 am
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 🙂
January 13, 2011 at 4:46 am
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
June 4, 2011 at 12:13 pm
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?
June 5, 2011 at 3:00 pm
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, Chief Data Engineer, Enterprise Data & Analytics
December 26, 2012 at 5:20 pm
This was a great article, very straightforward, helpful, and exactly what I needed. Now I can load my data without just truncating and reloading. Thanks!:w00t:
December 26, 2012 at 7:39 pm
msmithson (12/26/2012)
This was a great article, very straightforward, helpful, and exactly what I needed. Now I can load my data without just truncating and reloading. Thanks!:w00t:
Thanks msmithson - I am glad it helped!
:{>
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
Viewing 12 posts - 91 through 101 (of 101 total)
You must be logged in to reply to this topic. Login to reply