help with caching

[marzia]

In my schema there are dozens of 'lookup entities' (city, state, currency, country, invoice_status) and everything is pretty much normalized...
I order to avoid excessive load on db when constructing the object graphs of main entities I thought to use nested selects for those 'lookup entities', together with caching in a hope that cache would avoid a nested select altogether. Am I on the right track or else ?  In general is there some hint (doc, blog, article) to study on principles behind caches in mybatis ? Few tips and some guidance would be much appreciated. Thank you...

[Benoît]

Hi Marzia,

I' haven't used nested selects yet but as I understand I think they
are most coslty than a SQL join. After all, joining is what RDBMS are
good at.

Cheers,
Benoît


On Mar 5, 12:27 pm, marzia <[hidden email]> wrote:
> In my schema there are dozens of 'lookup entities' (city, state, currency,
> country, invoice_status) and everything is pretty much normalized...
> I order to avoid excessive load on db when constructing the object graphs
> of main entities I thought to use nested selects for those 'lookup
> entities', together with caching in a hope that cache would avoid a nested
> select altogether. Am I on the right track or else ?  In general is there
> some hint (doc, blog, article) to study on principles behind caches in
> mybatis ? Few tips and some guidance would be much appreciated. Thank you...

[marzia]

Thank you Benoît, yes joins are 'bread and butter' of dbms but if I
can avoid them it would be better ;-), what do you think ?
In the meantime I am trying to tweak the configuration to obtain what
I described in my previous message, where I have a 'resultMap' with
one association property with nested select which references a simple
select by primary key which is in his separate 'mapper.xml' with the
'cache readOnly=true'. Putting the 'System.out.println' inside the
constructor of my 'association bean' I see that in case of
'association selects' cache is ignored.
Am I doing it wrong or else ? What can I do to have this caching
scenario function properly ?

[marzia]

Can someone of the gurus here at least point me to the right
direction ?
Maybe some use of interceptor ?
How that this feature is not inside the core already ? is there
something wrong with my approach ?
my config: latest snapshot of mybatis with mybatis-guice 3.2
Thanks

[Eduardo Macarron]

Marzia, 2nd level cache does not cache inner selects it caches the
whole result. There is no way to change that behaviour.

May I ask why do you need it?

[marzia]

I have frequent scenario of kind: User 'has a' Role where Role is
table and bean with proper mapper.xml with 'selectPK' and 'selectAll'
statements. When I define 'selectPK' in mapper.xml of User it would be
elegant and simple to have simple 'select ... from user' and when
defining his resultMap make a simple 'association' with 'nested
select' reffering to a 'selectPK' of Role with full namespace path.
Given that Role mapper have a caching enabled 'cache readOnly=true'
the 'association select' can be avoided, and the execution of User
select is very simple and efficient. In my case this is very frequent
scenario, and I think of any crud application... WDYT ?

[marzia]

Eduardo, and others, can someone chime in and at least tell me if
there is something wrong with my approach ?

[Eduardo Macarron]

Yes Marzia, the problem is that MB will not cache inner selects, it
caches top level statements. And I am afraid there is no way to change
that by configuration or with a plugin. :(

2012/3/12 marzia <[hidden email]>:
> Eduardo, and others, can someone chime in and at least tell me if
> there is something wrong with my approach ?

[marzia]

Why is that ? I can understand in case where nested select is written
inline but in case of reference to existing select it can be handled
rather nicely ?
Just imagine a case where there is client with client_status,
client_profile, client_area, instead of 4-way join there is just a
single plain select and other 3 are form caches ;-) Is this a common
case ?
Is it possible to contribute a patch for this ?
P.S. What is MB ?
Thanks, for a patience...

[Eduardo Macarron]

MB is MyBatis, for the acronym.

I see the benefit although honestly you will save that much. If you
get a client using some nested selects the final object graph will be
cached so the next time you get that client you will get the result
directly.

If you also use one of those nested selects alone it will hit the
database, and then will be cached.

You can contribute a patch but I am afraid that will not be easy
because the 2nd level caching is at a high level and knows nothing
about nested selects.

2012/3/12 marzia <[hidden email]>:

> Why is that ? I can understand in case where nested select is written
> inline but in case of reference to existing select it can be handled
> rather nicely ?
> Just imagine a case where there is client with client_status,
> client_profile, client_area, instead of 4-way join there is just a
> single plain select and other 3 are form caches ;-) Is this a common
> case ?
> Is it possible to contribute a patch for this ?
> P.S. What is MB ?
> Thanks, for a patience...

[marzia]

Hi Eduardo,

You got it, but in my case (and in general i think) getting the same
client/invoice/order in same session in a small period of time is
pretty improbable, so caching those entities have not much sense, add
to this that are pretty high on the cardinality side... The point is
that getting those entities (with or without caching) require getting
also those 'foreign key' entities and those are pretty static, warm
cache, and relatively small.
I would like to gain in a simplicity of statements, resultMaps etc...
with this approach, because 'selectByPK' statement is probably already
present in 'client_status' mapper.xml it's just question of referring
to it inside a nested select of client 'selectByPK'mapper.xml.
I don't know how is the execution path, but if the execution of nested
select statement is delegated to some code which in turn handles
'client_status' statements all of this is pretty transparent, because
it's enough to turn on a caching inside of 'client_status'
mapper.xml... am I on the right track ?
I would like to tinker with a code, can you give me some hint's about
how to implement this patch ?

P.S. MB - A few (mili)seconds after I sent a message I got it ;-)

Thanks

[Mike Fotiou]

I totally agree with marzia in the expectation that the use of the cache would have been honored in nested selects and not just direct calls to the statement itself.

Imagine an "asset" entity that contains several normalized lookups of simpler entities like region, consignee, salesperson.  The asset is at the heart of the system and participates in many types of queries.  It would be tedious to always include joins for all of these select statements (even though you could reuse the resultMap defined in the lookup mappers).  In some cases there can be ten or more joins just for lookups, and those lookups can contain their own lookups.  Writing the select for these lookups one time and then being able to retrieve them transparently via the cache would make for cleaner, compartmentalized code throughout the mappers (and obviously improve performance if you go that route).  That is what I thought the promise of reusable mappers and associations/collections were all about.

Caching at the top level for an object graph like this is not so great for data that is modified at lot through it's business life cycle and that also may be touched from systems outside of the MyBatis cache control, like legacy applications.

So what we end up with is the cache being useful for simple use cases like populating combo-boxes and the like, and not really working for us in the way that we would intuitively expect it to work.

In terms of code, it starts at getPropertyMappingValue where getNestedQueryMappingValue() is called in FastResultSetHandler.  The problem is that the SimpleExecutor is passed to the FastResultSetHandler which uses it directly when loading the value of the nested query.  I perhaps naively assumed that the same execution path would be used for any select, whereby another caching executor would be created based on the mapper being used (as if it was being called directly at the top level).  Would it be possible to use the DefaultSqlSession.selectList at this level to return the result of the nested query, which would use the CachingExecutor?

Mike

On Wed, Mar 14, 2012 at 12:44 PM, marzia <[hidden email]> wrote:

Hi Eduardo,

You got it, but in my case (and in general i think) getting the same
client/invoice/order in same session in a small period of time is
pretty improbable, so caching those entities have not much sense, add
to this that are pretty high on the cardinality side... The point is
that getting those entities (with or without caching) require getting
also those 'foreign key' entities and those are pretty static, warm
cache, and relatively small.
I would like to gain in a simplicity of statements, resultMaps etc...
with this approach, because 'selectByPK' statement is probably already
present in 'client_status' mapper.xml it's just question of referring
to it inside a nested select of client 'selectByPK'mapper.xml.
I don't know how is the execution path, but if the execution of nested
select statement is delegated to some code which in turn handles
'client_status' statements all of this is pretty transparent, because
it's enough to turn on a caching inside of 'client_status'
mapper.xml... am I on the right track ?
I would like to tinker with a code, can you give me some hint's about
how to implement this patch ?

P.S. MB - A few (mili)seconds after I sent a message I got it ;-)

Thanks

[Terenyi]

Hello,

We had the same problem, so we created our own myBatis fork. I attached the
diff against 3.0.4. We use that in production for a year now. It was made in a
hurry, so don't expect a nice code. And all the configuration possibilities of
myBatis are not tested, so there can be setups in which the whole thing
misbehaves.

It contains the following modifications:
1. using list types other than ArrayList in collections (subquery results)
2. removed defered loading (lazy loading, it caused problems for us, as I can
remember)
3. removed local cacheing (it's done by the controlled subquery cacheing, like
in iBatis2)
4. automatic on the fly query parameter transformer

Maybe it can give some ideas for the developers of myBatis. It would be great
if those features would be available out of the box.

Be aware: It was hacked in hours without any deep knowledge of myBatis
internals!

Regards,
Balázs

2012.03.16. 05:42:47 Mike Fotiou:

> I totally agree with marzia in the expectation that the use of the cache
> would have been honored in nested selects and not just direct calls to the
> statement itself.
>
> Imagine an "asset" entity that contains several normalized lookups of
> simpler entities like region, consignee, salesperson.  The asset is at the
> heart of the system and participates in many types of queries.  It would be
> tedious to always include joins for all of these select statements (even
> though you could reuse the resultMap defined in the lookup mappers).  In
> some cases there can be ten or more joins just for lookups, and those
> lookups can contain their own lookups.  Writing the select for these
> lookups one time and then being able to retrieve them transparently via the
> cache would make for cleaner, compartmentalized code throughout the mappers
> (and obviously improve performance if you go that route).  That is what I
> thought the promise of reusable mappers and associations/collections were
> all about.
>
> Caching at the top level for an object graph like this is not so great for
> data that is modified at lot through it's business life cycle and that also
> may be touched from systems outside of the MyBatis cache control, like
> legacy applications.
>
> So what we end up with is the cache being useful for simple use cases like
> populating combo-boxes and the like, and not really working for us in the
> way that we would intuitively expect it to work.
>
> In terms of code, it starts at getPropertyMappingValue
> where getNestedQueryMappingValue() is called in FastResultSetHandler.  The
> problem is that the SimpleExecutor is passed to the FastResultSetHandler
> which uses it directly when loading the value of the nested query.  I
> perhaps naively assumed that the same execution path would be used for any
> select, whereby another caching executor would be created based on the
> mapper being used (as if it was being called directly at the top level).
>  Would it be possible to use the DefaultSqlSession.selectList at this level
> to return the result of the nested query, which would use the
> CachingExecutor?
>
> Mike
>
> On Wed, Mar 14, 2012 at 12:44 PM, marzia <[hidden email]> wrote:
> > Hi Eduardo,
> >
> > You got it, but in my case (and in general i think) getting the same
> > client/invoice/order in same session in a small period of time is
> > pretty improbable, so caching those entities have not much sense, add
> > to this that are pretty high on the cardinality side... The point is
> > that getting those entities (with or without caching) require getting
> > also those 'foreign key' entities and those are pretty static, warm
> > cache, and relatively small.
> > I would like to gain in a simplicity of statements, resultMaps etc...
> > with this approach, because 'selectByPK' statement is probably already
> > present in 'client_status' mapper.xml it's just question of referring
> > to it inside a nested select of client 'selectByPK'mapper.xml.
> > I don't know how is the execution path, but if the execution of nested
> > select statement is delegated to some code which in turn handles
> > 'client_status' statements all of this is pretty transparent, because
> > it's enough to turn on a caching inside of 'client_status'
> > mapper.xml... am I on the right track ?
> > I would like to tinker with a code, can you give me some hint's about
> > how to implement this patch ?
> >
> > P.S. MB - A few (mili)seconds after I sent a message I got it ;-)
> >
> > Thanks

[Eduardo Macarron]

Sorry to revive this old thread but while having a look at the fixed issues for 3.2.0 I say Gus implemented the inner select chaching and I recall this discussion.

http://code.google.com/p/mybatis/issues/detail?id=686

It would be great if you could give it a test