Hibernate optimization Crawl (optimization guidelines---n+1 query issues)

Hibernate optimization Crawl (optimization guidelines---n+1 query issues)

By default, hibernate does not load data that you do not request, thus reducing the memory consumption of the persisted context. However, it will also make you face the so-called n+1 query problem. If each association and collection are initialized on demand only, and no other policy is configured. A specific process can also perform dozens of or even hundreds of of queries to get all the data you need. You need the right strategy to avoid executing too many SQL statements.

If you switch from the default policy to a query that is crawling data through the connection, you may experience another problem: the Cartesian product problem. Instead of having to execute too many SQL statements, you can now (often as a side effect) create statements that get too much data.

Find the middle ground between the two extremes: the proper crawl strategy for each process, and the use cases in your application. You need to know what global crawl plans and policies should be set in the mapping metadata, and which crawl policy (through HQL or criteria) applies only to a particular query.

----------

1.n+1 query problem

N+1 query issues can easily be understood by some sample code. Suppose you do not configure any crawl plans or crawl strategies in the mapping metadata: Everything is deferred and loaded on demand. The following sample code attempts to find the highest bid for all the item:

List<item> AllItems = Session.createquery ("from Item"). List (); List<item> AllItems = Session.createcriteria (item.class). List (); map<item,bid> highestbids = new hashmap<item,bid> (); for (Item item:allitems) {Bid highestbid = null; for (Bid:item.getBids ()) {//initialize The collection if (Highestbid = n ull) highestbid = bid; if (Bid.getamount () > Highestbid.getamount ()) highestbid = bid; } highestbids.put (Item,highestbid); }

First, get all item instances, which is no different between hql and criteria queries. This query triggers 1 SQL SELECT, gets all the rows of the item table, and returns n persisted objects. Next, you traverse this result and access each item object.

You are accessing the Bids collection for each item. This collection has so far not been initialized, and the bid object for each item must be loaded with an additional query. This entire code fragment therefore generates a n+1 query.

You always want to avoid n+1 queries.

The first solution might be to change your global mapping metadata for a set, enabling bulk prefetching:

<set name= "Bids" inverse= "true" batch-size= "ten" > <key column= "item_id"/> <one-to-many class= "Bid"/> </set>

Instead of n+1 queries, you now see n/10+1 queries fetching the necessary collections into memory. (Environment: Suppose that not all item objects in a persisted context need to initialize their bid collection.) )

~~~~~

Using a subquery based prefetch, you can reduce the number of selections to just two:

<set name= "Bids" inverse= "true" fetch= "Subselect" > <key column= "item_id"/> <one-to-many class= "Bid"/ > </set>

The first query in the procedure now executes a single SQL SELECT and gets all item instances. Hibernate remembers this statement and applies it again when you hit the first uninitialized collection. All collections are initialized with a second query. (Environment: Suppose that all other item objects in a persisted context need to initialize their bid collection.) )

~~~~~

Finally, you can effectively turn off deferred loading of the bids collection and convert to an instant crawl strategy that results in a single SQL select:

<set name= "Bids" inverse= "true" fetch= "join" > <key column= "item_id"/> <one-to-many class= "Bid"/> </set>

This seems to be an optimization that you should not be doing. The crawl strategy in the map metadata works at a global level. We should not regard fetch= "join" as a common optimization for set mapping. You always rarely need a fully initialized set. In addition to causing a higher memory consumption, each set of external joins also causes a more serious problem with Cartesian product.

In practice, you are most likely to enable a batch or subquery policy for the bids collection in the mapping metadata. If a particular process requires all the bids of each item in memory, you modify the initial HQL or criteria query and apply a dynamic crawl strategy:

List<item> AllItems = Session.createquery ("From Item I-left join Fetch i.bids"). List (); List<item> AllItems = Session.createcriteria (Item.class). Setfetchmode ("bids", Fetchmode.join). List ();

These two queries, through a outer JOIN, generate a single select for all item instances to get bids (as you have already mapped the collection with Fetch= "JOIN").

This may be the first time you've seen how to define a Non-global crawl strategy. The global crawl plan and crawl policy settings that you put in the map metadata are only the global defaults that are always applied. Any optimization process also requires finer-grained rules that apply only to a particular process or use case's crawl strategy and crawl plan.