I have set up a test VPS running Windows Server 2016 and SQL Server 2016.  When I test crawl our site with Screaming Frog after restoring and upgrading the database from 3.7 to 4.2, our site runs about between 2 to 20 pages per second, however, quite a bit of time is spent in SQL calls.


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