2

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.

  • Are there any constraints or foreign keys on the columns of the table involved in the self join? Those can help a bit (a similar situation is with recursive CTEs). If not, just appropriate indexing goes a long way. – Erik Darling Feb 10 '17 at 20:56
  • @sp_Blitz the primary key is what is being joined on – Yishai Feb 10 '17 at 21:07
1

There are numerous restrictions to what can be an indexed view. In a data warehouse setting those restrictions will make it impossible for just about every standard star join view to be an indexed view. Which is really a shame, since despite what the comments say, [materialized] indexed views are magic!

As for whether joining to the same table twice is an issue, it is not. In a data warehouse you'll often find yourself joining to time and date dimensions dozens of times.

Also, I was interested to see that you made the primary key on the date dimension the date itself. In my last data warehouse we ended up needing to use an INT column as the primary key, because we needed to save indeterminate dates (like 2016/1/??) where all of the date parts were not known.

Also, when writing your views, you should always check to verify whether a LEFT JOIN will have better performance than an INNER JOIN, even if they produce the same results. Back when our data warehouse was closer to a snowflake schema, the joins were into the hundreds, and many of the views did better with all LEFT JOINs as opposed to INNER JOINs where INNER JOINs were appropriate.

  • For the record, switching the view that prompted this question from inner joins to left joins reduced the execution time over 95%. – Yishai Feb 14 '17 at 22:30
  • Excellent, glad to hear that advice is generalizable. It seemed counter intuitive at the time I noticed it, which is why I thought I would pass it along. I don't remember 95% gains, but for a few of the larger joins it was significant. – Matthew Sontum Feb 15 '17 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.