1

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.

  • I'm starting to form an opinion that No-SQL databases are usually a better option for any data structure. I may elaborate on my thinking by writing an answer for you but too tired right now and I'll be unclear. – rmayer06 Nov 27 '14 at 4:13
  • 1
    Only hundreds of thousands of properly indexed rows with foreign keys? pshaw. That's nothing. ... and why would you want to take the calculations of aggregate data out of the database (which is good at it)? – user40980 Jan 5 '15 at 18:32
6

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.

  • This right here. I work with several multi-tenant databases every day and we have a ton of on demand reporting that does data aggregation for anywhere from a few thousand to a few million rows. SQL runs circles around it fine as long as the queries are well designed and the tables properly indexed. – Locke Jan 5 '15 at 18:45

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.