June 10, 2011 at 7:21 am
My normal approach is to split the file with winrar into 100MB chunks and repair volumes. Makes it easy to copy the file and if there is a problem, winrar can fix it 😀
I'm still keen on the bcp on same instance for massive tables because of the batching and lack of contention within the system.
BCP will hammer SQL and disk. Reverse happens on the way in. Using SSIS needs memory (and a lot of it if it has half a chance).
A fellow WinRar user, excellent, love that app!
I don't know about your neck and neck comment though. Everything I have seen has SSIS outperforming bcp in this scenario. SSIS will use more RAM than bcp, but SSIS will create far less disk activity and we know about that tradeoff. SSIS can buffer the data coming out of the source, push it into the destination and then reuse the RAM from those now committed buffers to fill with more data from the source so it's not like it'll have to load everything into memory before it can start dropping any data onto the destination. As Phil commented SSIS gives us built-in commit size mechanisms that allow us to tune for impact versus performance.
Don't get me wrong, I use bcp all the time for different applications. In this case you might be able to squeeze a bit more out of bcp using the native format (wish we could say, need DDL) but I always have to look up the command line reference anytime I use it and by the time I do that and pick all the right switches and options my SSIS package would already have been off and running committing data onto the destination 😀
yup, purely for passers by and "covering all the bases" I would bang my head against a wall if the response was "well, it takes to long to copy the file".
You're late to this party but it would be par for the course on this thread, my head already hurts. 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 7:40 am
can I add, bcp looks cooler when in full screen. More so if you star at it intently and mutter when strangers walk by :hehe:
I win 😉
You might be right - SSIS is quick and would probably win on a local box.
is there any hint at how wide this 800 mill table is? If it's narrow then SSIS would fit a shed load in at a time...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 10, 2011 at 7:49 am
Crispin Proctor (6/10/2011)
can I add, bcp looks cooler when in full screen. More so if you star at it intently and mutter when strangers walk by :hehe:I win 😉
I concede, bcp wins hands down 😛
is there any hint at how wide this 800 mill table is?
Gee, maybe we should ask the OP for some DDL :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply