java - @BatchSize a smart or stupid use? -

first i'll explain how understood , use @batchsize : @batchsize made in order load relations of objects in batch, making less sql request database. specially usefull on lazy @onetomany relations.

however it's useful on lazy @onetoone relation , @manytoone : if load list of entities database , ask load lazyed @*toone entity, load entities batch if use test load relation of 1st entity of list.

note if want tests : show if entities not loaded : instance if have list of user manager , list users, when access manager, no request triggered since it's loaded.

the drawback see on method if load list of item database use part of it. post-filtering operation.

so let's main point.

let's assume make never post-filtering-like operations if it's makes me native sql queries or use dto objects multiselect criteria query , on.

1. am right consider can @batchsize every lazyed relations after having think using eager loading / join , choose lazy relation ?
2. do have interest search adequate value @batchsize or can think "the bigger better" ? mean "is there limit of number in "in" sql operator can make request enough slower not worth anymore ? use postgres if have answers others sgbd i'm interested too.
3. optional question : seems using @batchsize on class isn't producing lot of results. still have annotate every lazy relationships, did miss or useless ?

edit : point of 3 i'm getting different behaviour.

let i'm loading list of entities of class "a" has lazy onetomany relationship b. want print creationdate of b. i'm doing classic 2 loop.

i annotated b batchsize :

• @onetomany not annotated batchsize : each set of b loaded on each iteration independently without batching. annotation on b class seems totally ignored. if set value "two" , have 6 entries in 1 set, have 1 query set.
  • @onetomany annotated : have specific query of batches loaded. if fix batch size 2 , have total of 10 b accros 5 requests : whatever number of have. if set 100 : have 1 query b objects.

ps : i'm not considering related query b might fire load b fields fetch select/subselect.

edit 2 : found post why not use @batchsize on every lazy loaded relationship? althought googled , search on beforeposting question, guess didn't use right words...

however i'm adding different might lead different answer : when i'm wondering using batchsize on every relations, it's after choosing if want eager loading, join / select fetch or if want lazy loading.

1. yes, @batchsize meant used lazy associations.
2. hibernate execute multiple statements in sitations anyway, if count of uninitialized proxies/collections less specified batch size. see this answer more details. also, more lighter queries compared less bigger ones may positively contribute overall throughput of system.
3. @batchsize on class level means specified batch size entity applied @*toone lazy associations entity. see example person entity in documentation.

the linked question/answers provided more concerned need optimization , lazy loading in general. apply here of course, not related batch loading only, 1 of possible approaches.

another important thing relates eager loading mentioned in linked answers , suggests if property used may better performance using eager loading. in general not true collections , in many situations to-one associations either.

for example, suppose have following entity bs , cs always used when a used.

public class {   @onetomany   private collection<b> bs;    @onetomany   private collection<c> cs; } 

eagerly loading bs , cs suffers n+1 selects problem if don't join them in single query. if join them in single query, example like:

select   left join fetch a.bs   left join fetch a.cs 

then create full cartesian product between bs , cs , returning count(a.bs) x count(a.cs) rows in result set for each a read 1 one , assembled entities of a , collections of bs , cs.

batch fetching optimal in situation, because first read as, bs , cs, resulting in more queries less total amount of data transferred database. also, separate queries simpler big 1 joins , easier database execute , optimize.