2 Incorporated comments
source | link

I have a simple dimension, for example a date dimension, which is static and unchanging over time.

  In the fact table, I have several dates.

Is it different from a performance perspective to join to 1one date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on.?

So ifIf I have a fact table that looks like this.:

FactId   FactNumber  FactDate  FactOtherDate
1        10          1/1/2016  12/31/2015

And...and I want to produce a query like this:

Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month
From Fact  
Inner Join DateDim On DateDim.Date = FactDate
Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate
Group By DateDim.Month, DateDim2.Month

Would...would it be better to make a second actual table and join to that, rather than joining to the same table twice?

I ask because Indexed Viewsindexed views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently. I'm not looking to use an indexed view, just wondering if that restriction provides any insight into this question.

I have a simple dimension, for example a date dimension, which is static and unchanging over time.

  In the fact table, I have several dates.

Is it different from a performance perspective to join to 1 date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on.

So if I have a fact table that looks like this.

FactId   FactNumber  FactDate  FactOtherDate
1        10          1/1/2016  12/31/2015

And I want to produce a query like this:

Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month
From Fact Inner Join DateDim On DateDim.Date = FactDate
Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate
Group By DateDim.Month, DateDim2.Month

Would it be better to make a second actual table and join to that, rather than joining to the same table twice?

I ask because Indexed Views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently.

I have a simple dimension, for example a date dimension, which is static and unchanging over time. In the fact table, I have several dates.

Is it different from a performance perspective to join to one date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on?

If I have a fact table that looks like this:

FactId   FactNumber  FactDate  FactOtherDate
1        10          1/1/2016  12/31/2015

...and I want to produce a query like this:

Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month
From Fact  
Inner Join DateDim On DateDim.Date = FactDate
Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate
Group By DateDim.Month, DateDim2.Month

...would it be better to make a second actual table and join to that, rather than joining to the same table twice?

I ask because indexed views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently. I'm not looking to use an indexed view, just wondering if that restriction provides any insight into this question.

    Tweeted twitter.com/StackDBAs/status/830984788274184192
1
source | link

Does joining to the same table twice have a predictable performance impact

I have a simple dimension, for example a date dimension, which is static and unchanging over time.

In the fact table, I have several dates.

Is it different from a performance perspective to join to 1 date dimension twice vs. having a second copy of that date dimension and joining to it for the second date and so on.

So if I have a fact table that looks like this.

FactId   FactNumber  FactDate  FactOtherDate
1        10          1/1/2016  12/31/2015

And I want to produce a query like this:

Select Sum(FactNumber), DateDim.Month, OtherMonth = DateDim2.Month
From Fact Inner Join DateDim On DateDim.Date = FactDate
Inner Join DateDim DateDim2 ON DateDim2.Date = FactOtherDate
Group By DateDim.Month, DateDim2.Month

Would it be better to make a second actual table and join to that, rather than joining to the same table twice?

I ask because Indexed Views won't allow such a query as a view, and that made me think that SQL Server handles it differently and possibly less efficiently.