During my recent run, I was getting approximately 1.9 to 3.8 pages per second.
The slowest SQL call of the last run was 482.16ms and the query was:
INSERT INTO #DisplayOrderTmp ([ProductId])
SELECT p.Id
FROM
Product p with (NOLOCK)
INNER JOIN Product_Category_Mapping pcm with (NOLOCK)
ON p.Id = pcm.ProductId
WHERE
p.Deleted = 0
AND pcm.CategoryId IN (36,70,8,71,72,74,73,26,27,28,30,29,85,86,87,88,89,84,92,91,90,17,55,62,63,64,56,57,58,59,60,61,65,38,15,39,41,42,43,40,23,24,25,81,44,82,83,66,67,68,94,54,47,48,49,45,69,37,50,52,53,51,46)
AND pcm.IsFeaturedProduct = 0
AND p.VisibleIndividually = 1
AND p.Published = 1
AND p.Deleted = 0
AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, '1/1/1900') and ISNULL(p.AvailableEndDateTimeUtc, '1/1/2999'))
AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = 42))
ORDER BY pcm.DisplayOrder ASC, p.[Name] AS
I also created a demo site database and then made compared our database to the demo and it has the same indexes and everything else that the demo site has, the only thing I can tell is the specification filtering is taking the time because if I remove that crawl the site, it hits 20-50 pages/second.
I also sent a request regarding the 3 months support and whether that might be helpful to assist us in getting this working performance wise.
If we can’t get it at a decent speed we’ll have to investigate moving off nopCommerce, however, we have been happy with nopCommerce so far. Also I investigated, 50% of people purchasing use the product filtering so I don’t think our customers would like it without it.
Thanks for your assistance,
Carmon