As you can see from the Dev Tools profiler, the facets are taking over 9 seconds to complete. How is this even possible? That only renders 9 facet groups with 16 (!!!) options total.
Geschrieben: 11 June 2020 - 15:58
As you can see from the Dev Tools profiler, the facets are taking over 9 seconds to complete. How is this even possible? That only renders 9 facet groups with 16 (!!!) options total.
Geschrieben: 11 June 2020 - 16:05
Here is the code relevant for this view:
Url: https://website.com/products/instock
CatalogController.cs
[RequireHttpsByConfigAttribute(SslRequirement.No)] public ActionResult InStockProducts(CatalogSearchQuery query) { var productIds = _productService.GetProductIdsInStockToCustomer(); // since there's no way to actually give it the products, have to // pass it the IDs query.WithProductIds(productIds.ToArray()); return Category(DatabaseConstants.Category.AllId, query); }
ProductService.cs
public IList<int> GetProductIdsInStockToCustomer() { var query = from p in _productRepository.Table where p.Published && !p.Deleted && p.VisibleIndividually && p.DisplayStockAvailability && ( p.ManageInventoryMethodId == (int)ManageInventoryMethod.ManageStock && p.StockQuantity > 0 || p.ManageInventoryMethodId == (int)ManageInventoryMethod.ManageStockByAttributes && p.ProductVariantAttributeCombinations.Any(c => c.StockQuantity > 0) ) select p.Id; return query.ToList(); }
Geschrieben: 12 June 2020 - 09:25
Here you need to know the raw data. Try to execute the following SQL script in SQL Server Management Studio and post the result here. It counts the records in the individual data tables. Maybe you can already see from this where it is slowing down.
SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName] , SUM(sPTN.Rows) AS [RowCount] FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id WHERE sOBJ.type = 'U' AND sOBJ.is_ms_shipped = 0x0 AND index_id < 2 -- 0:Heap, 1:Clustered GROUP BY sOBJ.schema_id , sOBJ.name ORDER BY [TableName] GO
Marcus Gesing
Smartstore AG
Geschrieben: 12 June 2020 - 16:06
That query runs instantly on my server.
Time statistics Trial 3 Trial 2 Trial 1 Average Client processing time 2 1 1 1.3333 Total execution time 10 10 8 9.3333 Wait time on server replies 8 9 7 8.0000
Here are the row counts:
TableName RowCount [dbo].[__MigrationHistory] 177 [dbo].[AclRecord] 1 [dbo].[ActivityLog] 58607 [dbo].[ActivityLogType] 63 [dbo].[Address] 135 [dbo].[Affiliate] 0 [dbo].[BackInStockSubscription] 0 [dbo].[BlogComment] 0 [dbo].[BlogPost] 3 [dbo].[Campaign] 0 [dbo].[Category] 193 [dbo].[CategoryTemplate] 1 [dbo].[CheckoutAttribute] 1 [dbo].[CheckoutAttributeValue] 2 [dbo].[ContentSlider] 14 [dbo].[ContentSliderSlide] 2 [dbo].[Country] 237 [dbo].[CrossSellProduct] 0 [dbo].[Currency] 12 [dbo].[Customer] 137 [dbo].[Customer_CustomerRole_Mapping] 143 [dbo].[CustomerAddresses] 11 [dbo].[CustomerContent] 0 [dbo].[CustomerRole] 8 [dbo].[DeliveryTime] 8 [dbo].[DependingPrices] 0 [dbo].[Discount] 2 [dbo].[Discount_AppliedToCategories] 0 [dbo].[Discount_AppliedToManufacturers] 0 [dbo].[Discount_AppliedToProducts] 0 [dbo].[DiscountRequirement] 0 [dbo].[DiscountUsageHistory] 0 [dbo].[Download] 0 [dbo].[EmailAccount] 1 [dbo].[ExportDeployment] 0 [dbo].[ExportProfile] 1 [dbo].[ExternalAuthenticationRecord] 0 [dbo].[ForumPostVote] 0 [dbo].[Forums_Forum] 2 [dbo].[Forums_Group] 1 [dbo].[Forums_Post] 0 [dbo].[Forums_PrivateMessage] 0 [dbo].[Forums_Subscription] 0 [dbo].[Forums_Topic] 0 [dbo].[GenericAttribute] 558 [dbo].[GiftCard] 0 [dbo].[GiftCardUsageHistory] 0 [dbo].[GoogleProduct] 1 [dbo].[ImportProfile] 7 [dbo].[IndexBacklog] 74 [dbo].[Language] 2 [dbo].[LocaleStringResource] 16912 [dbo].[LocalizedProperty] 64 [dbo].[Log] 259 [dbo].[MailChimpEventQueueRecord] 3 [dbo].[Manufacturer] 67 [dbo].[ManufacturerTemplate] 1 [dbo].[MeasureDimension] 5 [dbo].[MeasureWeight] 6 [dbo].[MediaStorage] 2461 [dbo].[MegaMenu] 16 [dbo].[MenuItemRecord] 35 [dbo].[MenuRecord] 6 [dbo].[MessageTemplate] 34 [dbo].[News] 4 [dbo].[NewsComment] 0 [dbo].[NewsLetterSubscription] 2 [dbo].[Order] 5 [dbo].[OrderItem] 5 [dbo].[OrderNote] 38 [dbo].[OutputCacheControlTag] 0 [dbo].[OutputCacheItem] 0 [dbo].[OutputCacheRoute] 51 [dbo].[PageStory] 1 [dbo].[PageStoryBlock] 5 [dbo].[PaymentMethod] 2 [dbo].[PermissionRecord] 50 [dbo].[PermissionRecord_Role_Mapping] 62 [dbo].[Picture] 2175 [dbo].[Poll] 2 [dbo].[PollAnswer] 8 [dbo].[PollVotingRecord] 0 [dbo].[Product] 1329 [dbo].[Product_Category_Mapping] 1481 [dbo].[Product_Manufacturer_Mapping] 1358 [dbo].[Product_Picture_Mapping] 2635 [dbo].[Product_ProductAttribute_Mapping] 84 [dbo].[Product_ProductTag_Mapping] 307 [dbo].[Product_SpecificationAttribute_Mapping] 11263 [dbo].[Product_TechnicalSpecificationAttribute_Mapping] 2210 [dbo].[ProductAttribute] 29 [dbo].[ProductAttributeOption] 169 [dbo].[ProductAttributeOptionsSet] 25 [dbo].[ProductBundleItem] 2 [dbo].[ProductBundleItemAttributeFilter] 0 [dbo].[ProductFiles] 205 [dbo].[ProductReview] 0 [dbo].[ProductReviewHelpfulness] 0 [dbo].[ProductTag] 135 [dbo].[ProductTemplate] 1 [dbo].[ProductVariantAttributeCombination] 1037 [dbo].[ProductVariantAttributeValue] 396 [dbo].[QuantityUnit] 16 [dbo].[QueuedEmail] 13 [dbo].[QueuedEmailAttachment] 0 [dbo].[RecurringPayment] 0 [dbo].[RecurringPaymentHistory] 0 [dbo].[RelatedProduct] 132 [dbo].[ReturnRequest] 1 [dbo].[RewardPointsHistory] 0 [dbo].[ScheduleTask] 23 [dbo].[ScheduleTaskHistory] 2125 [dbo].[SearchTerm] 192 [dbo].[Setting] 743 [dbo].[Shipment] 1 [dbo].[ShipmentItem] 1 [dbo].[ShippingByTotal] 0 [dbo].[ShippingByWeight] 0 [dbo].[ShippingMethod] 2 [dbo].[ShoppingCartItem] 16 [dbo].[SpecificationAttribute] 98 [dbo].[SpecificationAttributeOption] 1823 [dbo].[StateProvince] 75 [dbo].[Store] 1 [dbo].[StoreMapping] 0 [dbo].[SyncMapping] 2 [dbo].[TaxCategory] 1 [dbo].[TaxRate] 14 [dbo].[ThemeVariable] 0 [dbo].[TierPrice] 0 [dbo].[Topic] 13 [dbo].[UrlRecord] 1608 [dbo].[WalletHistory] 0
Geschrieben: 12 June 2020 - 21:02
Marcus Gesing
Smartstore AG
Geschrieben: 12 June 2020 - 22:47
I'm using the MegaSearch plugin, the Availability search facet doesn't really make any sense to me, and I don't think it works properly (at least for me). My definition of availability is basically if the green "In Stock" text is shown on a product or for any product variant. The way the filter should work for me is that it should allow filtering to show only In Stock products if they click that facet option - and the query for the in stock products should be the one I wrote. It actually only passed 22 IDs into WithProductIds so I still don't understand the slow down.
I wish I could edit the MegaSearch plugin code for my site as that would make it so much easier and I don't have to do these strange search workarounds. For example I've had to play around with the idea of making a special brand "In Stock" and a scheduled task that associates/disassociates products to that "brand". But really, I either need to override the current Availability facet (would require MegaSearch code access I believe, I would need to change the SmartStore.MegaSearch.Services.CatalogIndexCollector.CollectProductData method I'm pretty sure) or come up with a workaround such as brand association or something to do this.
Geschrieben: 15 June 2020 - 08:59
Marcus Gesing
Smartstore AG
Geschrieben: 18 June 2020 - 16:26
You're right about the query, although I'm structuring the query such that it's more intended for customers, not from the system perspective. Items that are shown as in stock on the website is the main thing I'd want in the query.
As a workaround, can you tell me how I could call "SearchController.Search" with a list of IDs and have it be fast? This would probably be the easiest way for me to show all in stock products and have the facets available.