How to improve performance using SCD transformation

Question

How to improve performance using SCD transformation

NIRANJANA1977

SSC Eights!

Points: 854
More actions
February 26, 2009 at 3:02 pm

#155342

Dear All
I have designed type II using SCD transformation where I am dealing with 8 columns and about 100000 rows . But it is getting stuck in the middle of the process.
Can anybody have solution for this ????

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you explain a bit more what you're trying to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

The slowly changing dimension wizard component is ok for about 10,000 rows in a dimension. After that, the performance is so poor that you will probably have issues with it. Most of the problem is that the lookup it is doing tends to not be able to cache data of that size.

I would recommend you change to a different set of components. You can do the same thing with a merge join component and a conditional split - use the merge join with an outer join to find the records that already exist and then use the conditional split to send records to an insert or an update.

In addition to this option, you could get the TableDifference component:

http://www.sqlbi.com/Projects/TableDifference/tabid/74/language/en-US/Default.aspx

This component is basically the merge join and conditional split in a single component. It has a couple of additional features (non-case sensitive join).

At 100,000 rows, these two options will perform reasonably. If you get much beyond that, you will find the best option is to stage your records in a table and use a T-SQL join to determine which records to insert and update.

lijnenjoost Right there with Babe Points: 767 More actions · Answer 3

Here are some more alternatives to improve the performance of a Slowly Changing Dimension:

http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

lijnenjoost (1/7/2011)
Here are some more alternatives to improve the performance of a Slowly Changing Dimension:
http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html

You forget to mention the TSQL Merge statement (if SQL Server 2008) in your blog post.

p.s. you do realize this thread (and the others you posted into) is almost 2 years old?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

lijnenjoost Right there with Babe Points: 767 More actions · Answer 5

Koen (da-zero) (1/7/2011)
lijnenjoost (1/7/2011)
Here are some more alternatives to improve the performance of a Slowly Changing Dimension:
http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html
You forget to mention the TSQL Merge statement (if SQL Server 2008) in your blog post.
p.s. you do realize this thread (and the others you posted into) is almost 2 years old?

Thanks Koen,

I added the TSQL Merge option. http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html#C

Ps thread age doesn't realy matter, as long it's usefull for the next one that is reading the post.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

lijnenjoost (1/7/2011)
Ps thread age doesn't realy matter, as long it's usefull for the next one that is reading the post.

I absolutely agree with THAT! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)