Dear Alex, its a decision making time, to go with ClassTable approach or concreteable approach.i read few of your posts and got some queries,pls advise.

  1. i have maximum four level inheritence, ie. Entity -> BusinessEntity -> Workstate -> PersonalPermission.
  2. DO 3.x was using classtable approach, n hence it used to create table for "Workstate" and used to create view for "PersonalPermission-view". 3.Now the class table approach does not create any view, infact creates both "workstate" and "PesonalPermission" as tables...This mean that we have to create views manually so that we can use it for Reporting, for support engineers, so "join" are always an overhead.
  3. if i go with concretetable approach then my Reporting enginer and creating views problem will be solved but then whenever i need to access "Workstate" object, it will make Union of all derived tables , and as u explained that Indexing part in one of ur posts, then it will have negative effect on performance.
  4. plus the base table like "Workstate" will be created but it will be empty table with no rows.
  5. lastly, sometimes i need base object to be returned, instead of exact types.

Please advise, which approach should be better for above requirements. which one is better optimized "joins" or "Union" query. i have to make decision keeping in mind performance, Reporting, and support engineers. so please advise.



asked Nov 03 '10 at 08:46

HannanKhanji's gravatar image


edited Nov 06 '10 at 05:36

Alex%20Yakunin's gravatar image

Alex Yakunin

Dear alex, i am waiting for your valuable advice.

(Nov 07 '10 at 02:25) HannanKhanji HannanKhanji's gravatar image

Hi,i am waiting for your support.

(Nov 08 '10 at 04:40) HannanKhanji HannanKhanji's gravatar image

One Answer:

Sorry for delay,

  • How many objects are expected to be in this hierarchy?
  • Do you really need an opportunity to query or reference base objects? If not, just move hierarchy roots to leafs. Note that #5 does not imply violation of this rule, and #4 makes me feel this is possible, since both BusinesEntity and Workstate are abstract classes.

answered Nov 08 '10 at 04:48

Alex%20Yakunin's gravatar image

Alex Yakunin

I'll add some comments on JOINs vs UNION a bit later - I need to know what kind of hierarchy it is.

(Nov 08 '10 at 04:49) Alex Yakunin Alex%20Yakunin's gravatar image

Preliminary comment: according to you, BusinessEntity and Workstate tables are going to be empty in ConcreteTable case. This indicates ConcreteTable (with UNION) is, likely, a preferable option, since query optimizer will take this fact into account.

On the other hand, I'd consider an option with moving [HierarchyRoot] closer to leafs first.

(Nov 08 '10 at 04:51) Alex Yakunin Alex%20Yakunin's gravatar image

well, 1. as i told you before i have 4 level of hierarchy max and leaf objects can be max 7 to 8. like leave , Permission , officialPermission etc will inherit from Workstate. 2. i may rarely use base types. 3. i will never create "Workstate" or "businessentity" objects directly.

(Nov 08 '10 at 05:17) HannanKhanji HannanKhanji's gravatar image
  1. as i have understood , we cannot have two hierarchy roots, i have already one defined on BusinessEntity, and you want me to define on "WorkState" also,in this case, new keys and ids will be generated for "Workstate" and it will not be part of BusinessEntity, but i have some done much logic on BusinessEntity , i have to repeat similar code for all hierarchy roots.
(Nov 08 '10 at 05:17) HannanKhanji HannanKhanji's gravatar image

Like for eg, in databinding expression i am type casting object to BusinessEntity n it works but now how i will decide whether is should typecast to Businessentity or Workstate,,,basically i dont understand how moving [HierarchyRoot] closer to leafs will help?

(Nov 08 '10 at 05:18) HannanKhanji HannanKhanji's gravatar image

pls advise. thanks

(Nov 08 '10 at 05:18) HannanKhanji HannanKhanji's gravatar image

Moving [HierarchyRoot] closer to leafs splits single hierarchy to multiple ones, and thus reduces the number of JOINs / UNIONs - some of them can be absolutely unnecessary in your case.

You need a single hierarchy root only if you're going to:

  • Use Query.All<BusinessEntity>()
  • Use [Field] BusinessEntity SomeBusinessEntity.
(Nov 09 '10 at 06:39) Alex Yakunin Alex%20Yakunin's gravatar image

If you don't need these features, you should consider splitting the hierarchy into multiple ones. Obviously, casting and inherited logic will work anyway; you'll still be able to compare Entity.Keys and entities, up/down cast their references and so on.

(Nov 09 '10 at 06:43) Alex Yakunin Alex%20Yakunin's gravatar image

Moreover, if keys there will be of the same type (e.g. long), and will share the same key generator (by default that's true), all the keys in these hierarchies will be unique anyway (i.e. as if they'd be in case of single hierarchy).

In particular, this will allow you to implement common persistent interfaces (e.g. IBusinessEntity) in any parts of such hierarchies.

(Nov 09 '10 at 06:43) Alex Yakunin Alex%20Yakunin's gravatar image

as i told you before i have 4 levels of hierarchy max and leaf objects can be max 7 to 8.

My question was about count of instances, but not about depth of inheritance. How many instances are expected there?

(Nov 09 '10 at 06:45) Alex Yakunin Alex%20Yakunin's gravatar image

As you say that keys will be unique irrespective of heirarchies then i might give a try to ConcreteTable approach, i have already started with ClassTable approach, biliving that Union will take more time then Joins with conditions i have, but i wanted to try ConcTable approach... 2. I may have 7 to 8 instances. thanks han

(Nov 09 '10 at 09:54) HannanKhanji HannanKhanji's gravatar image

I may have 7 to 8 instances

If so, it doesn't matter much what kind of inheritance mapping you'll use. SQL Server will handle this fully in-memory.

(Nov 09 '10 at 11:11) Alex Yakunin Alex%20Yakunin's gravatar image

sorry i got you wrong, if i look old customers then employee workstate it may reach 200,000 records. and like this i have two more heirarachies which can reach close to this number.

(Nov 10 '10 at 01:06) HannanKhanji HannanKhanji's gravatar image

200 K records isn't a big number as well: even if record size is 1K, it's just 200 MB, i.e. the whole set will be cached in RAM, and thus joins must be pretty fast.

So my recommendation in this case is: don't fear joins too much in, if you don't expect 100-1000 times more data. I'd try to avoid some them, but only if this don't significantly affect on design. I.e. if I'd really need an ability to query for some base type, I'd keep it as hierarchy root.

(Nov 10 '10 at 06:10) Alex Yakunin Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA