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.
  • What's your typical query, select * from dc2 where d_id=X? If that sort of query is slow the predicate must not be being pushed into the union. I believe there are differences between how SS2008 and SS2005 handle this but I'm no expert. Posting view DDL, an actual query and plan may help... – Jack says try topanswers.xyz Nov 16 '11 at 12:37

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.

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.