mongodb - Advice on Query execution with Hibernate -

mongodb - Advice on Query execution with Hibernate -

my application takes extremely long time (over 1.5 minutes) queries execute & load entity records database , queries timeout after 2-3+ minutes. after much investigation found due hibernate 1+n issue rather problem application/database or way designed.

please see below similar database construction mine & illustration of query use

my question in 2 parts,

am querying right , if not else can solve this? (my queries similar java ee tutorial, hibernate doc etc)

will migrating nosql/schemaless(mongodb) resolve of info need can embedded 1 document means have execute 1 query (at few) gather info want rather 20-30 queries hibernate run @ moment.

database / entity structure

basically one order has many order_items, each order_item links item, , item has 1 or more item_specifications. item_specifications set via dynamic table, , each item_specifications link specification_question_answer links related specification_question (compare above diagram improve explanation). along these, there 10+ tables store more info order/item/system details etc. on top of this, every table link user table record added/last-updated record. in essence dynamic db construction lots & lots of joins create work in normalized fashion.

my queries compiled using criteriabuilder , uses construction similar next (with 'where' conditions + paginated of course):

class="lang-java prettyprint-override">criteriabuilder cb = em.getcriteriabuilder(); criteriaquery<order> ordercriteriaquery = cb.createquery(order.class); root<order> orderroot = ordercriteriaquery.from(order.class); typedquery<order> ordertypedquery = em.createquery(ordercriteriaquery); list<order> orderlist = ordertypedquery.getresultlist();

when there around 50 orders, query take over 1 1.5 minutes execute , timeout sometimes. cannot imagine happen if there thousands of orders.

this happens due hibernate 1+n in navigate through relationships , load info each order entity before returning compiled list of orders.

however if run query few fields i.e.- similar select o.id, o.order_date, o.shipping_address order o execute within couple of milliseconds. used methodology optimize application, querying entity objects in selective fashion , compiling order objects piece-by-piece somehow feels wrong!

so querying entities right? java ee tutorial says query should select o order o load list of orders (list<order>) , using similar method.

if right way do, how can improve speed 2+minutes not acceptable?

will migrating nosql & schema-less resolve issue?

thank in advance.

hibernate mongodb java-ee java-ee-7 jpa-2.1