We currently have the following SQL Schema:
Projects
Budgets
BudgetTabs
BudgetSections
BudgetTasks
BudgetTaskDetails
BudgetUnits
The data held in these tables is used to specify how much a project will cost to complete.
The problem I foresee is that whenever we need to get the total value of a project we have a query with a minimum 6 joins (there maybe more if including some other project information). As our db is a multi-tenant the these tables are likely to contain hundred of thousands of rows.
We’ve been wondering whether this information would be better stored in a document db such as MongoDB, which would also enable us to move some of the calculations out of SQL and into our code.
2
Six joins doesn’t sound like a lot to me, and I work with multi-tenant databases all day long. My advice is to join on your tenant ID at each step, and build your primary keys with tenant ID in them. That should get you 90% of the way there. If you still need more performance, look at the execution plans and determine if you need to reorder some joins or add indexes.
Rather than think up hypothetical performance problems, measure the real ones you have. SQL Server is more than happy to process billions of rows… a few hundred thousand won’t give it pause so long as you give SQL Server some help along the way.
1