How can I efficiently represent inheritance down a tree?

I have a database with three "levels" of objects: A, B, and C. Table A has around 100000 rows, B has 500000, and C has 2 million.

In a fit of stupidity, I designed the database to use joining tables AB and BC, even though these are 1:m and could have been represented as an ParentA attribute in B and a ParentB attribute in C. I did this because I thought at the time that these might be m:m.

Now, I also have a table D that has a m:m relationship with C. To simplify data management for users, it makes sense to allow them to relate D to A or B rather than directly to each C.

So, I have joining tables DA, DB, and DC, and a joining view DC2 that includes DC as well as the inherited DC relationships from joins of DA-AB-BC and DB-BC. I use a UNION ALL for this, and there's some business logic to prevent users from assigning the same D record at two levels.

The problem is, the performance of DC2 kinda sucks. All of the other tables have appropriate covering indexes for these joins, and the covering indexes are clustered. DC2 does not include tables A, B, C, or D, just the joining tables.

There are also tables E, F, G, and H that are similar to D in how they relate to A, B, and C.

What are some strategies I can use to improve performance of these inherited relationships?

Thought of already:

• Making DC2 an indexed view is not an option since UNION is not permitted in an indexed view in MSSQL.

• I could make DC2 a table and manage it with triggers, but this would be a pain, and I still have EC2, FC2, etc. to deal with. I can't make it where

• Changing AB and BC into attributes of B and C might actually slow things down, since the joining tables are lighter and the main tables aren't joined to DC2 often.

I'd look at your indexed view the other way around:

Have table O for your objects, storing A, B and C, and a level field. Then create indexed views for A, B and C based on queries off O. Perhaps use a hierarchyid field to know the full tree for each record.

There's plenty more you could do, but this could be a useful start.

StackExchange Database Administrators Q#7947, answer score: 2