Postby fdboles » Mon Mar 25, 2013 10:54 am
Phillip, I will probably need some assistance with this process. I'm new to GitHub and I'm still finding my way around.
With my change in place locally via custom class, I am still having some issues with performance. It turns out to not be related to the product retrieval as I had suspected. The issue appears to be with building the Solr document for indexing. After completing the first 5 pages (500 products) there is a long pause before any additional indexing occurs. After the pause, each document takes 5+ seconds to build. I suspect that this is due to the heap consumption and garbage collection that results from the long running query.
While monitoring the DB server late last night, I noticed a long running query. I have inserted some timing markers in the code and found that this is occurring when adding Explicit Categories to the document (SolrIndexServiceImpl.attachBasicDocumentFields). The query is asking for all product information for all products in a collection of (50) categories. I copied the query and modified to return the row count - 459370. I don't see a reason in code for selecting all products for a collection of categories. This code should be retrieving all products for a specific category. Any suggestions?
Here is the row counter form of the query:
select count(*)
from broadleaf.BLC_CATEGORY_PRODUCT_XREF allproduct0_
inner join broadleaf.BLC_PRODUCT productimp1_ on allproduct0_.PRODUCT_ID=productimp1_.PRODUCT_ID
left outer join broadleaf.BLC_PRODUCT_BUNDLE productimp1_1_ on productimp1_.PRODUCT_ID=productimp1_1_.PRODUCT_ID
left outer join broadleaf.BLC_CATEGORY categoryim2_ on productimp1_.DEFAULT_CATEGORY_ID=categoryim2_.CATEGORY_ID
left outer join broadleaf.BLC_CATEGORY categoryim3_ on categoryim2_.DEFAULT_PARENT_CATEGORY_ID=categoryim3_.CATEGORY_ID
inner join broadleaf.BLC_SKU skuimpl4_ on productimp1_.PRODUCT_ID=skuimpl4_.DEFAULT_PRODUCT_ID
left outer join broadleaf.BLC_PRODUCT_SKU_XREF skuimpl4_1_ on skuimpl4_.SKU_ID=skuimpl4_1_.SKU_ID
left outer join broadleaf.BLC_PRODUCT productimp5_ on skuimpl4_.DEFAULT_PRODUCT_ID=productimp5_.PRODUCT_ID
left outer join broadleaf.BLC_PRODUCT_BUNDLE productimp5_1_ on productimp5_.PRODUCT_ID=productimp5_1_.PRODUCT_ID
left outer join broadleaf.BLC_PRODUCT productimp6_ on skuimpl4_1_.PRODUCT_ID=productimp6_.PRODUCT_ID
left outer join broadleaf.BLC_PRODUCT_BUNDLE productimp6_1_ on productimp6_.PRODUCT_ID=productimp6_1_.PRODUCT_ID
where allproduct0_.CATEGORY_ID in (104895, 105038, 110005, 1, 110050, 110354, 105141, 110145, 105122, 110120,
110114, 110040, 110048, 110358, 110008, 105181, 100001, 105044, 110001, 104839, 111611, 110082, 105182,
110468, 110322, 110061, 104842, 110318, 104900, 110241, 104873, 104831, 110279, 105120, 110088, 105160,
110052, 105153, 110007, 104800, 105043, 2, 110041, 110002, 110320, 110147, 105180, 105040, 105123, 110106)
order by allproduct0_.DISPLAY_ORDER;