SSIS 2008 SCD Performance Issues | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SSIS 2008 SCD Performance Issues

Hi
We are having 20 dimension tables and each table will be having around 20 million records.
These tables would be loaded on a daily frequency with 5 files, each of 3 million records.
We are currently using SCD transformation for TYPE2 load of data.( to maintain history in the dimension table.)
But SCD is taking a long time to insert the data and below are the statistics that I recorded when I executed the package with sample files:
Run1: File1(0.5 million records) —2 minutes (Dimension Table is empty)
Run2: File2(0.5 million records) —13 minutes (Table has 589,000 records)
Run3: File3(0.5 million records) —26 minutes (Table has 1,140,000 records)
Run4: File4(0.5 million records) —37 minutes (Table has 1,680,000 records)
Run5: File5(1 million records) —51 minutes (Table has 2,780,000 records) Package elapsed time : 2 hr 9 min
1. How do i improve the performance of the SCD? If not, is there any way of loading a table parallely from file so that i can achive performance?
2. In informatica, we have a partitioning feature to load the data parallely which greatly improves performance. Is there any equivalent feature or workaround in SSIS?
Any help would be greatly appreciated. Thanks,
Bhaskar

Welcome to the forums.
IF you have good indexing on the columns that are involved in SCD it will fetch better performance, also better to limit the number of rows on the extract. Further I recommend http://www.codeplex.com/kimballscd and FYI http://www.sql-server-performance.com/articles/biz/slowly_changing_dimension_type1_p1.aspxon 2005.

]]>

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 |