Page 1 of 1

A LOT of products (80.000+)

Posted: Wed Dec 12, 2012 8:33 pm
by felixnutella

I have removed all the preloaded products and categories from the Demo and loaded my own products. There are around 80.000 products but can Broadleaf handle that amount of products ? I have tried to run it but it crashed with out of memory. Have increased to incrementally up to 2G but it seems that the fetch of all products in the solr search takes a REAAAALLLY LONG time to finish.

I'm using a MSSQL Server but have not been able to find some weird things to be aware of when using Hibernate/JPA with that SQL Dialect...

Any idea as to where I could look/tweak/other?


UPDATE: The SQL fired - I guess that it's done once per product..
select skuimpl0_.SKU_ID as SKU1_37_5_,
skuimpl0_.ACTIVE_END_DATE as ACTIVE2_37_5_,
skuimpl0_.ACTIVE_START_DATE as ACTIVE3_37_5_,
skuimpl0_.AVAILABLE_FLAG as AVAILABLE4_37_5_,
skuimpl0_.DEFAULT_PRODUCT_ID as DEFAULT22_37_5_,
skuimpl0_.DESCRIPTION as DESCRIPT5_37_5_,
skuimpl0_.CONTAINER_SHAPE as CONTAINER6_37_5_,
skuimpl0_.DEPTH as DEPTH37_5_,
skuimpl0_.GIRTH as GIRTH37_5_,
skuimpl0_.HEIGHT as HEIGHT37_5_,
skuimpl0_.CONTAINER_SIZE as CONTAINER11_37_5_,
skuimpl0_.WIDTH as WIDTH37_5_,
skuimpl0_.DISCOUNTABLE_FLAG as DISCOUN13_37_5_,
skuimpl0_.IS_MACHINE_SORTABLE as IS14_37_5_,
skuimpl0_.LONG_DESCRIPTION as LONG15_37_5_,
skuimpl0_.NAME as NAME37_5_,
skuimpl0_.RETAIL_PRICE as RETAIL17_37_5_,
skuimpl0_.SALE_PRICE as SALE18_37_5_,
skuimpl0_.TAXABLE_FLAG as TAXABLE19_37_5_,
skuimpl0_.WEIGHT as WEIGHT37_5_,
skuimpl0_.WEIGHT_UNIT_OF_MEASURE as WEIGHT21_37_5_,
skuimpl0_1_.PRODUCT_ID as PRODUCT1_38_5_,
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_,
case when productimp1_1_.PRODUCT_ID is not null then 1 when productimp1_.PRODUCT_ID is not null then 0 end as clazz_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_.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_,
categoryim3_.CATEGORY_ID as CATEGORY1_23_2_,
categoryim3_.ACTIVE_END_DATE as ACTIVE2_23_2_,
categoryim3_.ACTIVE_START_DATE as ACTIVE3_23_2_,
categoryim3_.ARCHIVED as ARCHIVED23_2_,
categoryim3_.DESCRIPTION as DESCRIPT5_23_2_,
categoryim3_.DISPLAY_TEMPLATE as DISPLAY6_23_2_,
categoryim3_.LONG_DESCRIPTION as LONG7_23_2_,
categoryim3_.NAME as NAME23_2_,
categoryim3_.URL as URL23_2_,
categoryim3_.URL_KEY as URL10_23_2_,
skuimpl4_.SKU_ID as SKU1_37_3_,
skuimpl4_.ACTIVE_END_DATE as ACTIVE2_37_3_,
skuimpl4_.ACTIVE_START_DATE as ACTIVE3_37_3_,
skuimpl4_.AVAILABLE_FLAG as AVAILABLE4_37_3_,
skuimpl4_.DEFAULT_PRODUCT_ID as DEFAULT22_37_3_,
skuimpl4_.DESCRIPTION as DESCRIPT5_37_3_,
skuimpl4_.CONTAINER_SHAPE as CONTAINER6_37_3_,
skuimpl4_.DEPTH as DEPTH37_3_,
skuimpl4_.GIRTH as GIRTH37_3_,
skuimpl4_.HEIGHT as HEIGHT37_3_,
skuimpl4_.CONTAINER_SIZE as CONTAINER11_37_3_,
skuimpl4_.WIDTH as WIDTH37_3_,
skuimpl4_.DISCOUNTABLE_FLAG as DISCOUN13_37_3_,
skuimpl4_.IS_MACHINE_SORTABLE as IS14_37_3_,
skuimpl4_.LONG_DESCRIPTION as LONG15_37_3_,
skuimpl4_.NAME as NAME37_3_,
skuimpl4_.RETAIL_PRICE as RETAIL17_37_3_,
skuimpl4_.SALE_PRICE as SALE18_37_3_,
skuimpl4_.TAXABLE_FLAG as TAXABLE19_37_3_,
skuimpl4_.WEIGHT as WEIGHT37_3_,
skuimpl4_.WEIGHT_UNIT_OF_MEASURE as WEIGHT21_37_3_,
skuimpl4_1_.PRODUCT_ID as PRODUCT1_38_3_,
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_,
case when productimp5_1_.PRODUCT_ID is not null then 1 when productimp5_.PRODUCT_ID is not null then 0 end as clazz_4_
from BLC_SKU skuimpl0_
left outer join BLC_PRODUCT_SKU_XREF skuimpl0_1_ on skuimpl0_.SKU_ID=skuimpl0_1_.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_CATEGORY categoryim3_ on categoryim2_.DEFAULT_PARENT_CATEGORY_ID=categoryim3_.CATEGORY_ID
left outer join BLC_SKU skuimpl4_ on productimp1_.PRODUCT_ID=skuimpl4_.DEFAULT_PRODUCT_ID
left outer join BLC_PRODUCT_SKU_XREF skuimpl4_1_ on skuimpl4_.SKU_ID=skuimpl4_1_.SKU_ID
left outer join BLC_PRODUCT productimp5_ on skuimpl4_1_.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=?

Re: A LOT of products (80.000+)

Posted: Sat Dec 22, 2012 5:17 pm
by felixnutella
No one ?

Re: A LOT of products (80.000+)

Posted: Mon Dec 24, 2012 1:57 pm
by alexhutnik
Have you considered setting up an external solr server to handle the indexing?

Re: A LOT of products (80.000+)

Posted: Wed Dec 26, 2012 7:15 pm
by felixnutella
Nope - not yet. The "problem" is with fetching all of the products. It's not even getting to indexing. My concern is that it will be way to slow in other areas with that amount of products.