Product Sku relations - why hibernate fires so many queries
Posted: Tue Nov 26, 2013 4:14 pm
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
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