SCD 1 slow performance
ScoobySiOct 21 2008 — edited Oct 22 2008I have a SCD Type 1 dimension, the initial load was 5million+ rows. I'm now running a daily update which should insert/update 68 rows, however this is taking over 1hr. The 68 rows are in a staging table so there are no complexities with the source query, it's a straight map to the dimension.
The dimension has 3 hierarchies (5 levels, 3 levels and 4 levels) 2 of the levels are common across 2 of the hierarchies.
What indexing strategies do people follow for dimension tables, at the moment I only have the default PK index on the Dimension_Key? It looks as though a Merge is generated for each level, basically a HASH join between source and target.
Cheers
Si