June 3, 2021 at 7:57 am
Hi, I am running a running a simple flush and fill package with hadoop source and sql server destination with about 50 million rows to transfer. The maximum length of a row is approx 6000 bytes.
It is taking 1.5 hrs to complete. Can someone please help with what values should I set buffer max size and buffer max rows to get some performance gain?
June 3, 2021 at 10:56 am
No idea, have you tried auto adjust buffer size ? https://www.mssqltips.com/sqlservertip/4221/improving-data-flow-performance-with-ssis-autoadjustbuffersize-property/
June 3, 2021 at 11:18 am
Auto adjust buffer size is from 2016, mine is SSIS 2012.
June 3, 2021 at 3:11 pm
and is destination using fast load? and does destination table have any indexes? and is the destination database in full , bulk or simple recovery mode?
also, and this is something to consider, what are the datatypes being transferred? in some cases where source is not SQL Server (case of Oracle for example) it is better to convert dates (and sometimes numbers also) to strings on the source select and then convert in transit or on destination (if using a staging table) to the desired datatype - this can give SIGNIFICANT performance improvements.
June 3, 2021 at 4:04 pm
Have you identified which part of the process is actually causing the delay? Is it taking 1.5 hours to extract the data from Hadoop - or is it taking 1.5 hours to load the data to SQL Server?
Did you use the defaults on the OLEDB destination - or have you set a reasonable batch/commit size?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2021 at 4:26 pm
destination is fast load
destination table has indexes which we are dropping and recreating.
datatypes are mostly varchar
Its the default setting for OLEDB destination. Any recommendation on commit size?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply