7 Antworten zu diesem Thema

[altmoola]

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.

 

[altmoola]

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();
}

[Marcus Gesing]

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

[altmoola]

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

[Marcus Gesing]

Probably a lot of product identifiers are passed in your WithProductIds call and that slows down the faceting because Lucene has to filter over every identifier. WithProductIds is typically used for exports or with a very few identifiers, not in conjunction with pages where facets are offered. Filtering over a lot of values has to be solved differently for performance reasons, for example by including the relevant aspect into the index so that it can be a facet itself. There is already a facet availability for this (see search settings > availability). Why don't you use it? I see that your IQueryable is a little different from the one used for search.

[altmoola]

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.

[Marcus Gesing]

I'm going to add the ManageStockByAttributes part to the availability filter because it's missing at the moment but two aspects are wrong in your query. BackorderMode is missing and DisplayStockAvailability must not be used in a query. It's only intended for UI. False means that the product may well be available and can be ordered. The MegaSearch source code is only accessible when using the Enterprise Commerce Edition.

[altmoola]

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.