Need Better Performance On Large Import | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need Better Performance On Large Import

I 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.


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |