Some new features

[Pavel Tavoda]

Hello,

I would like to discuss some features which we are using in our projects. I would like to know if it makes sense to include some of this features (best practices) also to sculptor.

1) fetch lazy/eager

I have advice which generate all relations as 'lazy' (if not explicitly set as eager in model). Than in repository operation (findByCodition, findById, findAll) I set all level 1 relations as EAGER. For us it's very useful because normally/often you need exactly your object plus one level of associations. This way we avoid HUGE graph selections or subselects. For big business objects with many associations is very significant. Of course it is better to do manual tuning but anyway setting lazy/eager on relation is not enough. Normally you have to tune select for each specific class, this 'level 1 approach' we found very useful. Average speed up is 5 - 10 times.

2) Column projection

We have prepared special access object something like findByConditionWithProjection which return List<Object[]>. It is used for selecting specific columns from database. For list views above HUGE objects (100s of attributes) or big object graphs (inheritance, many associations, ...) we normally create special entity and service and create special DB view which select specific columns. However our business need is that we have to change columns very often. For this we created special findByCondition which allow to dynamically specify columns which we have to select. Average speed up is 10-100 times.

3) findAll with HQL

Now (at least hibernate) findAllAccess is using HQL. This is not good because it's ignore lazy/eager settings on relations from annotations. HQL is fetching only explicitly named relations. For this is better to use normal criteria query (with order clause when needed).

4) lazy/eager with 'fuzzy logic'

This is something I have in mind. Using fetching strategy in point 1 is very easy to turn EAGER ON or OFF (keep all relations lazy). Believe it or not for specific data it's sometimes faster to subselect relations than fetch them in one select. It's true for big objects with many attributes with not so many rows. For example when you have two fat classes A and B and relation from A -> B and B contains only very few rows. Select only from A and subselecting a few rows from B is faster than one big select above A and B together.

For this situation I would like to have some logic in repository which will switch EAGER ON or OFF and analyze speed. I have no implementation right now however I think about:

1) do eager X

2) when it's SOME CONDITION try same query with eager !X

3) set X to strategy which is faster

SOME CONDITION - is our fuzzy logic, it should be some combination of:

- limit for executing query (e.g. slower than 500ms)

- time period (try different strategy every 100 query or once in 5 minutes, ...)

Speed of lazy/eager query really depends on data and it should change over time. Normally till data grove above some limits subselect is faster. After this, level 1 eager fetch is faster.

Sorry for my English, hope you can understand it

Regards

Pavel