November 11, 2008 at 7:34 am
Hi,
out application (c#) has the challenge to generate huge amount of data, lets say 25 million records in short time frame and this would repeat different times per day. The application uses a .net bulk component (i don't know in detail).
Now there are problems with the transaction log. If the admins would do this, could it be a good idea to let the log grow as large as needed? I'm afraid of a potential rollback. I assume we cannot load the data in one transaction but have to spit it to more small transactions..
Second, could it be an good idea if the application first write it's data to flatfiles and we use ssis to load it to the database? Would this perhaps be faster?
Thanks,
Tobias
November 11, 2008 at 8:04 am
IMO, you should batch the inserts and do a TX Log backup after each batch. Or set the DB to SIMPLE mode.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2008 at 8:38 am
No matter how you load it, if you go with one large transaction, then you have log issues (and potential rollback issues). SSIS is essentially a C# application written to load data. It's optimized, so I'm not sure your app can do it better, but you might not be worse.
I would batch it as Jack suggested. The reasons are to limit the bet you place on one set of work. If there are issues, power loss, etc., you have to redo the whole thing (after waiting for it to undo).
However, you might have requirements this is a transaction. In that case I'd look to do some trick to load in batches and "switch" in the data with a join or a partition or something when it's all loaded. Be sure that you account for failures between batches and have the ability to restart the load in the middle somewhere.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply