Need Better Performance On Large Import | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Need Better Performance On Large ImportI have a flat file where I am importing 18.9 million records. I am using the following transformations inside the package:
ScriptTask, Unpivot, Union All Lookups, dataConversion.
I am using the "Out of the box" settings on the dataflow properties. I tried adjusting them by setting the maxRows to 500,000 and updateing the DefaultBufferSize to 100485760 and set the engine threads to 16 since I have 4 quad core processors but all of this made it slower than the default settings. My process is taking 14 hours. Is this good? I sure hope not. What can I do to boost the performance?
Welcome to the forums.
Are you directly importing the file to the base table itself?
If so the indexes on that table will have such a performance degradation, so in this case better to put them in STAGING table then clean up the data as per your requirement then import back to base table.