Nop_ProductLoadAllPaged - needs to be rewriten/optimized

This stored procedure is extremely inefficient and slow with lots of products. This SP was taking 6 seconds to execute where we had 20,000 products.

In order to overcome this there are a couple of things we did:

1) Use a more modern paging system (WITH Paging AS SELECT rn = (ROW_NUMBER() OVER....)
2) Dynamically build the SQL string to only include the joins/where/orders that are required
3) Add indexes on join columns where missing

I would share our code, but it was for a heavily bespoke system so the tables won't match up.

Andrei - if you want I can send this directly to you if its off use.

Thanks, Will. Please post it here

Note there is some stuff in here which isn't required and this is only used for the front end. The admin is using the old SP.

Just to note this can still be done better by having a dynamic query with only the SQL you need. I have striped out lots of functionality from the original SP in the below and there is also some bespoke stuff which isn't relevant.

ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized]
(
  @PageSize int = null,
  @PageNumber int = null,
  @WarehouseCombinationID int = null,
  @CategoryId int = null,
  @OrderBy int = null,
  @TotalRecords int = null OUTPUT
)
AS
BEGIN

WITH Paging AS (
  SELECT rn = (ROW_NUMBER() OVER (
  ORDER BY 
    CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
    THEN pcm.DisplayOrder END ASC,
    CASE WHEN @OrderBy = 0
    THEN p.[Name] END ASC,
    CASE WHEN @OrderBy = 5
    THEN p.[Name] END ASC,
    CASE WHEN @OrderBy = 10
    THEN wpv.Price END ASC,
    CASE WHEN @OrderBy = 15
    THEN wpv.Price END DESC,
    CASE WHEN @OrderBy = 20
    THEN wpv.Price END DESC,
    CASE WHEN @OrderBy = 25
    THEN wpv.UnitPrice END ASC  
  )), p.*, pcm.DisplayOrder, wpv.Price, wpv.UnitPrice FROM Nop_Product p
  INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm.ProductID
  INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID
  INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID
  WHERE pcm.CategoryID = @CategoryId
  AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0 AND pv.Deleted = 0 and wpv.Deleted = 0)
  AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)  
)
SELECT TOP (@PageSize) * FROM Paging PG
WHERE PG.rn > (@PageNumber * @PageSize) - @PageSize 

SELECT @TotalRecords = COUNT(p.ProductId) FROM Nop_Product p
INNER JOIN Nop_Product_Category_Mapping pcm ON p.ProductID=pcm.ProductID
INNER JOIN Nop_ProductVariant pv ON p.ProductID = pv.ProductID
INNER JOIN Nop_ProductVariant_Warehouse_Mapping wpv ON pv.ProductVariantID = wpv.ProductVariantID
WHERE pcm.CategoryID = @CategoryId
AND (wpv.Published = 1 AND pv.Published = 1 AND p.Published = 1 AND p.Deleted = 0 AND pv.Deleted = 0 and wpv.Deleted = 0)
AND wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)


END

So what was the difference in speed?

atiqi36 wrote:

after caching and optimized sql I saved 8 seconds. Pages load in 1-1.5 seconds now. Was 8-10!

I see that the parameters of the stored procedure are different from the default Nop_ProductLoadAllPaged.
In which case(s) do you use your new one?
Thanks.

zar wrote:

Yes, sorry it can't be used in a standard nop installation as this was done for a heavily customised nop commerce store.

I had a site down all day because of this highly unefficient stored procedure, fortunately Tudor Carenan had rewritten it for Nop 1.8, check his blog post:

http://tcarean.wordpress.com/2010/11/27/rewriting-nop_productloadallpaged-with-cte-to-solve-the-performance-problems-in-nopcommerce/

I upgraded the queryes for Nop 1.9:

-- =============================================
-- Author: www.bitstarsolutions.com - Tudor Carean
-- =============================================
ALTER PROCEDURE [dbo].[Nop_ProductLoadAllPaged]
(
  @CategoryID      int = 0,
  @ManufacturerID    int = 0,
  @ProductTagID    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      money = null,
  @PriceMax      money = null,
  @RelatedToProductID  int = 0,
  @Keywords      nvarchar(MAX) = '',
  @SearchDescriptions bit = 0,
  @ShowHidden      bit = 0,
  @PageIndex      int = 0,
  @PageSize      int = 20, --2147483644,
  @FilteredSpecs    nvarchar(300) = null,  --filter by attributes (comma-separated list). e.g. 14,15,16
  @LanguageID      int = 7, --0,
  @OrderBy      int = 0, --0 position, 5 - Name, 10 - Price
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN

  --init
  DECLARE @SearchKeywords bit
  SET @SearchKeywords = 1
  IF (@Keywords IS NULL OR @Keywords = N'')
    SET @SearchKeywords = 0
  
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'

  SET @PriceMin = isnull(@PriceMin, 0)
  SET @PriceMax = isnull(@PriceMax, 2147483644)

  --DECLARE @PageIndexTable TABLE
  CREATE TABLE #PageIndexTable
  (
    [ProductID] int NOT NULL,
    [total] int
  )

  --filter by attributes
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionID int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionID)
  SELECT CAST(data as int) FROM dbo.[NOP_splitstring_to_table](@FilteredSpecs, ',');

  DECLARE @SpecAttributesCount int
  SELECT @SpecAttributesCount = COUNT(1) FROM #FilteredSpecs

  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int

  SET @RowsToReturn = @PageSize * (@PageIndex + 1)
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1;

  WITH DispOrderCTE (ProductIDCTE, /*RowNumber,*/total) AS
  (
    SELECT DISTINCT
      p.ProductID,
      --ROW_NUMBER() OVER (ORDER BY p.ProductID),
      COUNT(*) OVER(PARTITION BY NULL)
      FROM Nop_Product p with (NOLOCK)
      LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
      LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
      LEFT OUTER JOIN Nop_ProductTag_Product_Mapping ptpm with (NOLOCK) ON p.ProductID=ptpm.ProductID
      LEFT OUTER JOIN Nop_RelatedProduct rp with (NOLOCK) ON p.ProductID=rp.ProductID2
      LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
      LEFT OUTER JOIN Nop_ProductVariantLocalized pvl with (NOLOCK) ON pv.ProductVariantID = pvl.ProductVariantID AND pvl.LanguageID = @LanguageID
      LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = @LanguageID
    WHERE
    (

       (
        @CategoryID IS NULL OR @CategoryID=0
        OR (pcm.CategoryID=@CategoryID AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
      )
    AND (
        @ManufacturerID IS NULL OR @ManufacturerID=0
        OR (pmm.ManufacturerID=@ManufacturerID AND (@FeaturedProducts IS NULL OR pmm.IsFeaturedProduct=@FeaturedProducts))
      )
    AND (
        @ProductTagID IS NULL OR @ProductTagID=0
        OR ptpm.ProductTagID=@ProductTagID
      )
    AND (
        @RelatedToProductID IS NULL OR @RelatedToProductID=0
        OR rp.ProductID1=@RelatedToProductID
      )
    AND  (
        @ShowHidden = 1 OR p.Published = 1
      )
    AND 
      (
        p.Deleted=0
      )
    AND 
      (
        @ShowHidden = 1 OR pv.Published = 1
      )
    AND 
      (
        @ShowHidden = 1 OR pv.Deleted = 0
      )
    AND (
        @PriceMin IS NULL OR @PriceMin=0
        OR pv.Price > @PriceMin  
      )
    AND (
        @PriceMax IS NULL OR @PriceMax=2147483644 -- max value
        OR pv.Price < @PriceMax
      )
    AND  (
        @SearchKeywords = 0 or 
        (
          -- search standard content
          patindex(@Keywords, p.name) > 0
          or patindex(@Keywords, pv.name) > 0
          or patindex(@Keywords, pv.sku) > 0
          or (@SearchDescriptions = 1 and patindex(@Keywords, p.ShortDescription) > 0)
          or (@SearchDescriptions = 1 and patindex(@Keywords, p.FullDescription) > 0)
          or (@SearchDescriptions = 1 and patindex(@Keywords, pv.Description) > 0)          
          -- search language content
          or patindex(@Keywords, pl.name) > 0
          or patindex(@Keywords, pvl.name) > 0
          or (@SearchDescriptions = 1 and patindex(@Keywords, pl.ShortDescription) > 0)
          or (@SearchDescriptions = 1 and patindex(@Keywords, pl.FullDescription) > 0)
          or (@SearchDescriptions = 1 and patindex(@Keywords, pvl.Description) > 0)
        )
      )
    AND
      (
        @ShowHidden = 1
        OR
        (getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999'))
      )
    AND
      (
        --filter by specs
        @SpecAttributesCount = 0
        OR
        (
          NOT EXISTS(
            SELECT 1 
            FROM #FilteredSpecs [fs]
            WHERE [fs].SpecificationAttributeOptionID NOT IN (
              SELECT psam.SpecificationAttributeOptionID
              FROM dbo.Nop_Product_SpecificationAttribute_Mapping psam
              WHERE psam.AllowFiltering = 1 AND psam.ProductID = p.ProductID
              )
            )
          
        )
      )

    )
  )
  INSERT INTO #PageIndexTable (ProductId,total)
  SELECT templist.ProductIDCTE,templist.total from
     (select  fulllist.ProductIDCTE,
        fulllist.total,
        ROW_NUMBER() OVER (ORDER BY fulllist.ProductIDCTE) as RowNumber
    from DispOrderCTE fulllist
    ) templist
    WHERE
      RowNumber > @PageLowerBound AND
      RowNumber < @PageUpperBound
    ORDER BY
    RowNumber

  SELECT
    p.ProductId,
    p.Name,
    p.ShortDescription,
    p.FullDescription,
    p.AdminComment,
    p.TemplateId,
    p.ShowOnHomePage,
    p.MetaKeywords,
    p.MetaDescription,
    p.MetaTitle,
    p.SEName,
    p.AllowCustomerReviews,
    p.AllowCustomerRatings,
    p.RatingSum,
    p.TotalRatingVotes,
    p.Published,
    p.Deleted,
    p.CreatedOn,
    p.UpdatedOn
  FROM
    #PageIndexTable [pi]
    INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID  

  if ((select COUNT(*) from #PageIndexTable)>0)
    SELECT @TotalRecords=max(total) from #PageIndexTable
  else
    select @TotalRecords = 0

  SET ROWCOUNT 0

  DROP TABLE #FilteredSpecs
  DROP TABLE #PageIndexTable

END

Thank you for sharing this query!
However I have two questions :
- unless I am mistaken I can not see the sorting (parameter @OrderBy)
- do you think we can improve performance and results quality by supporting full text search with FREETEXT?
Best regards,

zar wrote:

Mhhh, good point, I was so happy that the site was back online that I didn't noticed that the CTE version of the SP is not using @OrderBy, I'm going to ask the original developer and will analyze the code to see where it needs to be put.

From my experience Full text searching is only valuable when the database has long varchar fields with very meaningful content, but in the case of NopCommerce the product information is in small varchar fields so using Pathindex is the best option.