Page 1 of 2

Product Sku relations - why hibernate fires so many queries

Posted: Tue Nov 26, 2013 4:14 pm
by pokemon007
We are using simple Product Sku relations (1 to 1 product sku relations). One weird thing I notice is that Hibernate generates 1 query per product if we query a list of skus as simple as this:
List<Sku> skuList = blSkuDao.readAllSkus();

It generates one following query per product:

Hibernate: select skuimpl0_.SKU_ID as SKU1_37_8_, skuimpl0_.ACTIVE_END_DATE as ACTIVE2_37_8_, skuimpl0_.ACTIVE_START_DATE as ACTIVE3_37_8_,
skuimpl0_.AVAILABLE_FLAG as AVAILABLE4_37_8_, skuimpl0_.DEFAULT_PRODUCT_ID as DEFAULT22_37_8_, skuimpl0_.DESCRIPTION as DESCRIPT5_37_8_,
skuimpl0_.CONTAINER_SHAPE as CONTAINER6_37_8_, skuimpl0_.DEPTH as DEPTH37_8_, skuimpl0_.DIMENSION_UNIT_OF_MEASURE as DIMENSION8_37_8_,
skuimpl0_.GIRTH as GIRTH37_8_, skuimpl0_.HEIGHT as HEIGHT37_8_, skuimpl0_.CONTAINER_SIZE as CONTAINER11_37_8_, skuimpl0_.WIDTH as WIDTH37_8_,
skuimpl0_.DISCOUNTABLE_FLAG as DISCOUN13_37_8_, skuimpl0_.IS_MACHINE_SORTABLE as IS14_37_8_, skuimpl0_.LONG_DESCRIPTION as LONG15_37_8_,
skuimpl0_.NAME as NAME37_8_, skuimpl0_.RETAIL_PRICE as RETAIL17_37_8_, skuimpl0_.SALE_PRICE as SALE18_37_8_,
skuimpl0_.TAXABLE_FLAG as TAXABLE19_37_8_, skuimpl0_.WEIGHT as WEIGHT37_8_, skuimpl0_.WEIGHT_UNIT_OF_MEASURE as WEIGHT21_37_8_,
skuimpl0_2_.PRODUCT_ID as PRODUCT1_38_8_,
productimp1_.PRODUCT_ID as PRODUCT1_31_0_, productimp1_.ARCHIVED as ARCHIVED31_0_,
productimp1_.CAN_SELL_WITHOUT_OPTIONS as CAN3_31_0_, productimp1_.DEFAULT_CATEGORY_ID as DEFAULT10_31_0_,
productimp1_.DISPLAY_TEMPLATE as DISPLAY4_31_0_, productimp1_.IS_FEATURED_PRODUCT as IS5_31_0_,
productimp1_.MANUFACTURE as MANUFACT6_31_0_, productimp1_.MODEL as MODEL31_0_, productimp1_.URL as URL31_0_,
productimp1_.URL_KEY as URL9_31_0_, productimp1_1_.AUTO_BUNDLE as AUTO1_32_0_, productimp1_1_.BUNDLE_PROMOTABLE as BUNDLE2_32_0_,
productimp1_1_.ITEMS_PROMOTABLE as ITEMS3_32_0_, productimp1_1_.PRICING_MODEL as PRICING4_32_0_,
productimp1_1_.BUNDLE_PRIORITY as BUNDLE5_32_0_,
categoryim2_.CATEGORY_ID as CATEGORY1_23_1_, categoryim2_.ACTIVE_END_DATE as ACTIVE2_23_1_, categoryim2_.ACTIVE_START_DATE as ACTIVE3_23_1_, categoryim2_.ARCHIVED as ARCHIVED23_1_, categoryim2_.DEFAULT_PARENT_CATEGORY_ID as DEFAULT11_23_1_,
categoryim2_.DESCRIPTION as DESCRIPT5_23_1_, categoryim2_.DISPLAY_TEMPLATE as DISPLAY6_23_1_, categoryim2_.LONG_DESCRIPTION as LONG7_23_1_, categoryim2_.NAME as NAME23_1_, categoryim2_.URL as URL23_1_, categoryim2_.URL_KEY as URL10_23_1_,
skuimpl3_.SKU_ID as SKU1_37_2_, skuimpl3_.ACTIVE_END_DATE as ACTIVE2_37_2_, skuimpl3_.ACTIVE_START_DATE as ACTIVE3_37_2_,
skuimpl3_.AVAILABLE_FLAG as AVAILABLE4_37_2_, skuimpl3_.DEFAULT_PRODUCT_ID as DEFAULT22_37_2_, skuimpl3_.DESCRIPTION as DESCRIPT5_37_2_,
skuimpl3_.CONTAINER_SHAPE as CONTAINER6_37_2_, skuimpl3_.DEPTH as DEPTH37_2_, skuimpl3_.DIMENSION_UNIT_OF_MEASURE as DIMENSION8_37_2_,
skuimpl3_.GIRTH as GIRTH37_2_, skuimpl3_.HEIGHT as HEIGHT37_2_, skuimpl3_.CONTAINER_SIZE as CONTAINER11_37_2_, skuimpl3_.WIDTH as WIDTH37_2_,
skuimpl3_.DISCOUNTABLE_FLAG as DISCOUN13_37_2_, skuimpl3_.IS_MACHINE_SORTABLE as IS14_37_2_, skuimpl3_.LONG_DESCRIPTION as LONG15_37_2_, skuimpl3_.NAME as NAME37_2_, skuimpl3_.RETAIL_PRICE as RETAIL17_37_2_,
skuimpl3_.SALE_PRICE as SALE18_37_2_, skuimpl3_.TAXABLE_FLAG as TAXABLE19_37_2_, skuimpl3_.WEIGHT as WEIGHT37_2_, skuimpl3_.WEIGHT_UNIT_OF_MEASURE as WEIGHT21_37_2_, skuimpl3_2_.PRODUCT_ID as PRODUCT1_38_2_,
productimp5_.PRODUCT_ID as PRODUCT1_31_4_, productimp5_.ARCHIVED as ARCHIVED31_4_, productimp5_.CAN_SELL_WITHOUT_OPTIONS as CAN3_31_4_,
productimp5_.DEFAULT_CATEGORY_ID as DEFAULT10_31_4_, productimp5_.DISPLAY_TEMPLATE as DISPLAY4_31_4_, productimp5_.IS_FEATURED_PRODUCT as IS5_31_4_,
productimp5_.MANUFACTURE as MANUFACT6_31_4_, productimp5_.MODEL as MODEL31_4_, productimp5_.URL as URL31_4_, productimp5_.URL_KEY as URL9_31_4_, productimp5_1_.AUTO_BUNDLE as AUTO1_32_4_,
productimp5_1_.BUNDLE_PROMOTABLE as BUNDLE2_32_4_, productimp5_1_.ITEMS_PROMOTABLE as ITEMS3_32_4_, productimp5_1_.PRICING_MODEL as PRICING4_32_4_, productimp5_1_.BUNDLE_PRIORITY as BUNDLE5_32_4_
from BLC_SKU skuimpl0_
left outer join BLC_PRODUCT_SKU_XREF skuimpl0_2_ on skuimpl0_.SKU_ID=skuimpl0_2_.SKU_ID
left outer join BLC_PRODUCT productimp1_ on skuimpl0_.DEFAULT_PRODUCT_ID=productimp1_.PRODUCT_ID
left outer join BLC_PRODUCT_BUNDLE productimp1_1_ on productimp1_.PRODUCT_ID=productimp1_1_.PRODUCT_ID
left outer join BLC_CATEGORY categoryim2_ on productimp1_.DEFAULT_CATEGORY_ID=categoryim2_.CATEGORY_ID
left outer join BLC_SKU skuimpl3_ on productimp1_.PRODUCT_ID=skuimpl3_.DEFAULT_PRODUCT_ID
left outer join BLC_PRODUCT_SKU_XREF skuimpl3_2_ on skuimpl3_.SKU_ID=skuimpl3_2_.SKU_ID
left outer join BLC_PRODUCT productimp5_ on skuimpl0_2_.PRODUCT_ID=productimp5_.PRODUCT_ID
left outer join BLC_PRODUCT_BUNDLE productimp5_1_ on productimp5_.PRODUCT_ID=productimp5_1_.PRODUCT_ID
where skuimpl0_.DEFAULT_PRODUCT_ID=?

It should run following predefined single query:

<named-query name="BC_READ_ALL_SKUS">
<query>SELECT sku FROM org.broadleafcommerce.core.catalog.domain.Sku sku</query>
</named-query>

Why it fires one query per product? What did we do wrong?

Thank you!

-Charlie

Re: Product Sku relations - why hibernate fires so many queries

Posted: Mon Dec 02, 2013 1:28 pm
by jefffischer
The hibernate configuration for defaultProduct is an optional OneToOne. This causes Hibernate to generate a query. The mapping has been modified somewhat in upcoming 3.1 to alleviate this.

Re: Product Sku relations - why hibernate fires so many queries

Posted: Wed Dec 04, 2013 5:24 am
by pokemon007
Thank you for the response. I believe this may cause high footprint. Even with very very low traffic, the server needs 2GB ram with two sites built on BLC. I tried to fix it, but it needs quite much change. I'll try to upgrade to the next release if you can get it fixed in next release.

Thanks.

-Charlie

Re: Product Sku relations - why hibernate fires so many queries

Posted: Mon Dec 09, 2013 2:20 pm
by jefffischer
This particular issue should have nothing to do with the heap requirements of Broadleaf Commerce.

Re: Product Sku relations - why hibernate fires so many queries

Posted: Sat Dec 14, 2013 5:56 pm
by pokemon007
Well, in our case, the query gets a list of deals. Each deal is associated with a list of skus. I've tried if no default product, this only needs 1 query. But due to this default product, after that query to get deal, it fires each query per sku in a deal. If it returns 15 deals and each deal have 10 skus (often more than that), it'll need 15x10 = 150 trips to database. Since our project is building inventory of many merchants and the number of merchants is very high, querying db consumes quite much ram. Caching may help avoid trips to database, but it'll at least process 150 queries from cache.

Do you have ETA on the new release that fixes this problem?

Thanks.

-Charlie

Re: Product Sku relations - why hibernate fires so many queries

Posted: Sat Dec 14, 2013 7:53 pm
by phillipuniverse
3.1.0-GA will be released sometime in January. You can go ahead and target the 3.1.0-SNAPSHOT if you like, but we currently do not have migration notes if you are on 3.0.x. Those will be done closer to the GA release.

Re: Product Sku relations - why hibernate fires so many queries

Posted: Mon Dec 23, 2013 5:22 pm
by pokemon007
Got it. I just downloaded 3.1.0-SNAPSHOT. It looks like the product/sku relation has not been updated. Hope it'll be updated soon.

Thank you.

-Charlie

Re: Product Sku relations - why hibernate fires so many queries

Posted: Mon Dec 30, 2013 7:40 pm
by pokemon007
Hi Phillip,

I didn't look into the schema change before I posted last time. After upgraded and manually fixed the schema (into problem with ANT target update_database), I found the many queries are eliminated. This is great! But I ran into another problem at post login when it tries to merge cart:

org.hibernate.exception.GenericJDBCException: Too many tables; MySQL can only use 61 tables in a join
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
com.sun.proxy.$Proxy193.executeQuery(Unknown Source)
org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
org.hibernate.loader.Loader.doQuery(Loader.java:899)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:311)
org.hibernate.loader.Loader.loadCollection(Loader.java:2228)
org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:61)
org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:678)
org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:80)
org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1804)
org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:549)
org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:234)
org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:545)
org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:124)
org.hibernate.collection.internal.PersistentBag.iterator(PersistentBag.java:266)
org.broadleafcommerce.core.order.domain.OrderImpl.updatePrices(OrderImpl.java:629)
org.broadleafcommerce.core.order.dao.OrderDaoImpl.updatePrices(OrderDaoImpl.java:207)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
com.sun.proxy.$Proxy129.updatePrices(Unknown Source)
org.broadleafcommerce.core.web.order.OrderState.setOrder(OrderState.java:59)
org.broadleafcommerce.core.web.order.OrderStateAOP.processOrderRetrieval(OrderStateAOP.java:46)
sun.reflect.GeneratedMethodAccessor142.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
com.sun.proxy.$Proxy129.readCartForCustomer(Unknown Source)
org.broadleafcommerce.core.order.service.OrderServiceImpl.findCartForCustomer(OrderServiceImpl.java:197)
sun.reflect.GeneratedMethodAccessor141.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
com.sun.proxy.$Proxy133.findCartForCustomer(Unknown Source)
org.broadleafcommerce.core.order.service.MergeCartServiceImpl.reconstructCart(MergeCartServiceImpl.java:132)

Tried to get it fixed, but this seems more of the new schema problem. Any idea?

Thank you and Happy New Year!

-Charlie

Re: Product Sku relations - why hibernate fires so many queries

Posted: Sun Jan 05, 2014 5:17 am
by pokemon007
Another question regarding this product/sku relationship. I've been wondering why a product needs a default sku and a sku have default product.

First of all, a sku should only be related to a single product. I can't image how a sku can be related to multiple product. So a sku will have a non-null product.

Secondly, the default sku serves the shared properties among all skus related to a product. I thought such shared properties belong to product. Afterwards, a product will only have a list of skus related to it, no default sku. If we really need such "default sku" for display purpose, we can add a flag to sku object.

I believe with such change, I believe we can avoid the query issue.

Thank you!

-Charlie

Re: Product Sku relations - why hibernate fires so many queries

Posted: Wed Jan 08, 2014 2:08 am
by phillipuniverse
When we did the catalog refactoring (see https://github.com/BroadleafCommerce/Br ... ce/pull/3; Jira isn't there anymore FYI) we went around and around quite a few times and the way we reasoned it out was that a Sku is really a concrete representation of a Product. The defaultSku relationship for a product is for simple store scenarios: you have a bunch of products that you sell in your store like a chair, a bookshelf, a door. 3 products, 1 Sku for each of those products.

However, think about something slightly more complex like T-Shirts. T-Shirts have product options associated with them (like Size and Color) and you might need to manage inventory or specific properties for those representations. However it's all the same Product. We have examples of this via the 'Merchandise' category in the heat clinic: when you select some product option values when you add to the cart, you're really picking a specific Sku to add to your cart from that Product.

So that's the reason behind the default Sku along with the List of additional Skus for a Product and how they can both make sense. Both concepts might not make sense for your particular store; it's all very implementation-specific in how you use them. Since we're trying to make a framework we wanted to be able to handle a large variety of different cases.

In regards to your 61-table join exception, this can arise if you end up extending multiple catalog entities and/or order item entities (I'm not sure what the exact number on this is, I should probably know that). This happens because Broadleaf uses multi-table inheritance by default. The way to alleviate this is to use a load time weaver to change the inheritance strategy to single-table. This puts all of your entity extensions into the root Broadleaf table with a discriminator column. Docs for this are at http://docs.broadleafcommerce.org/core/ ... s-tutorial in the 'Single table inheritance' section.