Many joins with class table inheritance

I am using class table inheritance and I am noticing left outer joins in my queries to every possible inherited table.

I know that joining to everything would be needed when querying against the base type because there is no way to determin what kind of object until after the query but I am specifying the type I want.

e.g. County, Region and City both inherit from Location. When I query for a City with a certain ID, why are Region and Country being joined to?

This is a limitation of the current implementation.  We plan to implement the optimisation you described at some point, but we have not yet been able to schedule this in.

The application I am working on won't be put to serious use for another month. Do you think you'll have time to look at this in that time?

I wouldn't be too optimistic I'm afraid.  What's the impact?  If you have metrics that show it would make a big difference, we can bump it up the priority list, but I can't make any promises.

I don't know yet. There will probably be about 6 entities inheriting from a single table and doing 5 unnessessary joins to tables with 1000s of rows for each database query can't be good for performance.

Hmm, I did some quick tests and it looks like it could make a noticeable difference.  I'll bump this up the priority list, but I'm not making any promises.

I've committed a candidate partial optimisation which avoids the joins, but still emits some redundant SQL.  Further improvements will be harder and we will need to see correspondingly more compelling metrics.  If you could give the optimisation some testing asap then that would be really good.

I made an oreille de cochon out of this.  Wait for the next build.

Joins are gone - awesome.

I can see what you mean about the reduntant SQL. I'm not selecting huge amounts of rows at once, everything has paging, so I don't think reduntant columns should be a probably.

*problem