java - Why does my query performance drastically drop? -

using following criteriabuilder returns results within 1 second when have map of 1 or 2 entries, when reaches 3 performance of query drops. adding 4th entry drops performance more point i've not seen return result set.

i have tried use sub-queries instead seems come across similar issue 2 exists sub-queries return data within reasonable time 3 or more result in large performance hit.

i have noticed without order query works great 3 entries ( 1 second or less ), add on same 3 entries search takes on 30 seconds.

here builder:

criteriabuilder builder = em.getcriteriabuilder(); criteriaquery<study> q = builder.createquery(study.class); root<study> rootstudy = q.from(study.class);  list<predicate> plist = new arraylist<predicate>( searchmap.size() ); q.select(rootstudy);  join<study, demographics> dem = rootstudy.join( "demographics" ); join<study, metadata> met = rootstudy.join( "metadata" );  // add conditions (map.entry<xmltaghashkey, string> entry : searchmap.entryset() ) {      mapjoin< demographics, xmltaghashkey, field2 > mapjoin = demjoin.joinmap( "fields" );      path<string> attributepath = mapjoin.get( "value" );     predicate p = builder.and( builder.equal( mapjoin.key(), entry.getkey() ),                             builder.like( attributepath, "%" + entry.getvalue() + "%" ));      plist.add( p ); }  q.where( plist.toarray(new predicate[]{}) ); q.orderby( builder.desc(  met.<string>get( xmltagenum.bbrad_status_updated.name() ) ) );  typedquery<study> typedquery = em.createquery( q ); typedquery.setmaxresults( 100 ); return getresultslist(typedquery); 

here sql created above builder when have 3 map entries:

select t1.id,         t1.study_id,         t1.demographics_id,         t1.metadata_id    demographics_has_fields t8,         field t7,         demographics_has_fields t6,         field t5,         demographics_has_fields t4,         field t3,         demographics t2,         study t1,         metadata t0   ( ( ( ( ( t6.xmltag = ? )                 , t5.value ? )               , ( ( t4.xmltag = ? )                     , t3.value ? ) )             , ( ( t8.xmltag = ? )                   , t7.value ? ) )           , ( ( ( ( ( t2.id = t1.demographics_id )                       , ( ( t6.demographics_id = t2.id )                             , ( t5.id = t6.field_id ) ) )                     , ( ( t4.demographics_id = t2.id )                           , ( t3.id = t4.field_id ) ) )                   , ( ( t8.demographics_id = t2.id )                         , ( t7.id = t8.field_id ) ) )                 , ( t0.id = t1.metadata_id ) ) )  order  t0.bbradstatusupdated desc  

here example of table structure:

what trying achieve select study value of field contains 'james' , xml tag '1' ( 1 name, 2 gender ). reason use map because might want search on additional fields, example gender. think way work exists sub-queries or joins i've done above.

unfortunately data structure pretty bad inherited previous database design, i'm kind of stuck it.

if understand correctly doing, adding joins query every map entry. means, multiply amount of data query has process huge number each map element. bound yield bad scalability.

to verify if problem, following:

run (sql) statements 2 , 3 map entries against database , measure performance. make sure fetch entries not first.

if guess right, see more or less same performance drop, not jpa/eclipse-link problem.

the solution might switch different datamodel, possibly star schema. affect lot of thing in application , know little that.