New Posts New Posts RSS Feed: Excessively large SQL with inheritance model
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Excessively large SQL with inheritance model

 Post Reply Post Reply
Author
paul.gilbert View Drop Down
Newbie
Newbie
Avatar

Joined: 31-Jan-2012
Location: Australia
Posts: 4
Post Options Post Options   Quote paul.gilbert Quote  Post ReplyReply Direct Link To This Post Topic: Excessively large SQL with inheritance model
    Posted: 31-Jan-2012 at 10:46pm
I seem to be having some real trouble with the SQL being generated for my first major project using DevForce in C#. It's mandated that an inheritance hierarchy be used, with a common base entity class that contains a unique entity Id and various change tracking information, as well as a couple of intermediate base classes for differentiating between 'system' tables and standard tables (along with various extra properties).

Now, however, as I start adding tables to my EDMX and wiring up the inheritence, I'm starting to get problems with excessively large SQL being generated, which rapdily becomes an OutOfMemoryException as more tables are added.  It seems to particularly manifest if I do an Include, to include a related table information, or do a standard Where query, as in 'BaseEntities.OfType<ChildClass>.Where(...)'.

What I can see from the SQL being generated, is that DevForce seems to be doing 'LEFT OUTER JOINS' from the base entity class to every since table defined in the model, which is obviously not what I want.  With the 'Where' I'm explicitly telling it that I want objects of the given child class type, so I would have thought the generated SQL would be straight inner joins from the base entity class down through the intermediate class to the child class, and only then do left outer joins to any client classes (if any).

Likewise, for the Include, I had assumed that the fact that the navigation property points to the specific given table, that any generated query wouldn't need to be doing outer joins to other tables elsewhere in the model.

Is anyone aware of something I may be doing wrong in the setup of my model and/or project that could cause this? Or is there a way that DevForce is meant to be used when dealing with an inheritance hierarchy to avoid this?  It's pretty much mandated that the base classes be there, so I'm hoping it's just something simple that I'm doing wrong, and won't have to do anything major to try and work around.

Any suggestions appreciated.

Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391
Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 01-Feb-2012 at 10:44am
Hi Paul,
 
DevForce does not actually generate the SQL for a LINQ query, we delegate that to the Entity Framework.   There are known issues with inheritance in EF, specifically with TPT style inheritance.   Here's one article - http://blogs.msdn.com/b/adonet/archive/2010/08/17/performance-considerations-when-using-tpt-table-per-type-inheritance-in-the-entity-framework.aspx - but if you do a web search on EF inheritance you'll find many more.
 
If you're using TPT inheritance and have control over the database, one option would be to instead use TPH or TPC style.
 
If all you need inheritance for is behavior, you can do that in DevForce with an "injected base type".  Here's more information - http://drc.ideablade.com/xwiki/bin/view/Documentation/model-custom-base-class.
 
 
Back to Top
paul.gilbert View Drop Down
Newbie
Newbie
Avatar

Joined: 31-Jan-2012
Location: Australia
Posts: 4
Post Options Post Options   Quote paul.gilbert Quote  Post ReplyReply Direct Link To This Post Posted: 02-Feb-2012 at 1:38pm
Many thanks. Looks like injected base types will do the trick to give me the hierarchy that's needed, and avoid the issues I was experiencing.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down