I have a database with three "levels" of objects:
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
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
B rather than directly to each
So, I have joining tables
DC, and a joining view
DC2 that includes
DC as well as the inherited
DC relationships from joins of
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
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:
DC2an 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.