Slow Manage Products section in Admin area V1.6, 1,7

I posted a better solution for paging here

HTH

I concur with the elimination of the temp table paging solution used in the ProductLoadAllPaged stored procedure.

Since I didn't want to surgically open the brain of the patient, I opted to just carve out his spleen a bit.

Here is my solution to the actual stored procedure itself.  Notice the Task Manager memory leak caused by this stored procedure when you have a large amount of product variants mapped to a single product:

http://www.fuzionagency.com/post/2010/09/18/NOP-Commerce-Product-Administration-Slow-with-Large-Number-of-Products-and-Variants.aspx

This was implemented on 1.7 version of NOP Commerce and does strip out certain functionality that some may see as mission critical (multi lingual product variants for example), but I have included instructions on how to implement those as well using the same theories described.  

I posted this faster than I preferred to since I was having server crashing issues caused by this darn thing so the PATINDEX search parsing hasn't been completed yet but I will post an update shortly now that I've gotten my SQL box to stop running away with its memory :)

Consider this above post a raw alpha for now but hopefully it will inspire a more permanent solution.

Jared Nielsen
FUZION Agency

I added in the patindex parsing and product search and identified the next query on my chopping block:

nop_PictureLoadAllPaged

This bugger takes a minute and 28 seconds to load so I'm going to do CTEs on this one

More in a bit.

Jared Nielsen
www.FUZION.org

Here is the Common Table Expression fix for the dbo.PictureLoadAllPaged stored procedure.  This one is far simpler and has a nice return on investment (1:28 load time down to :03 seconds)

http://www.fuzionagency.com/post/2010/09/19/NOP-Commerce-PictureLoadAllPaged-by-Jared-Nielsen-FUZION-Agency.aspx

Enjoy!

Jared Nielsen
www.FUZION.org

Thanks FUZION!
I'm using 1.8 and i am waiting for your work on this version!
My Manager Products is so slow, and plz help me fix this problem!
Thank's so much!

Here's Fuzions procedure adapted for V1.8. (It has one more parameter)
Hope this works


/****** Object:  StoredProcedure [dbo].[Nop_ProductLoadAllPaged]    Script Date: 09/26/2010 19:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'

  SET @PriceMin = isnull(@PriceMin, 0)
  SET @PriceMax = isnull(@PriceMax, 2147483644)
  
  --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
  
  CREATE TABLE #DisplayOrderTmp
  (
    [ID] int IDENTITY (1, 1) NOT NULL,
    [ProductID] int NOT NULL--,
    --[Name] nvarchar(400) not null,
    --[Price] money not null,
    --[DisplayOrder1] int,
    --[DisplayOrder2] int,
  )

  INSERT INTO #DisplayOrderTmp ([ProductID]
    --, [Name], [Price], [DisplayOrder1], [DisplayOrder2]
  )
  SELECT DISTINCT p.ProductID
      --, p.Name, (SELECT Min(Price) from nop_ProductVariant where ProductID=p.ProductID), pcm.DisplayOrder, pmm.DisplayOrder
    FROM Nop_Product p with (NOLOCK)
    --Use of subqueries obviates the need for needlessly massive row expanding joins
    --INNER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
    --Localized product variants commented out for speed
    --INNER 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
    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
  WHERE  
  (
    --P.ProductId = 9
    --AND
    (
      -- See if product is published
      @ShowHidden = 1 OR p.Published = 1
    )
    AND
    (
      -- See if at least one product variant is published
      @ShowHidden = 1 OR (SELECT Max(Convert(int,Published)) FROM nop_ProductVariant where ProductID=p.ProductID) = 1
    )
    AND
    (
      p.Deleted=0
    )
    AND
    (
      -- Make sure the product is listed between a certain price range
      (SELECT Min(Price) FROM nop_ProductVariant WHERE ProductID=p.ProductID GROUP BY ProductID)
      BETWEEN IsNull(@PriceMin,0) AND IsNull(@PriceMax,999999999999)
    )
    AND
    (
      @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
    (
      dbo.FUZION_isMatchingKeywords(p.ProductId, @Keywords, @SearchDescriptions) = 1
    )
  /*
    AND
    (
  -- search standard content
      patindex(@Keywords, isnull(p.name, '')) > 0
      or patindex(@Keywords, isnull(pv.name, '')) > 0
      or patindex(@Keywords, isnull(pv.sku , '')) > 0
      or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)
      or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)
      or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)
      -- search language content
      -- commented out localized product variants to save speed
      --or patindex(@Keywords, isnull(pl.name, '')) > 0
      --or patindex(@Keywords, isnull(pvl.name, '')) > 0
      --or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.ShortDescription, '')) > 0)
      --or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.FullDescription, '')) > 0)
      --or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pvl.Description, '')) > 0)
    )
  */
    AND (
        @RelatedToProductID IS NULL OR @RelatedToProductID=0
        OR rp.ProductID1=@RelatedToProductID
      )
    AND
    (
      --Verify that product is set to be available during a certain time period
      @ShowHidden = 1
      OR
      (
        dbo.FUZION_isAvailableToday(p.ProductID) = 1
        --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
          )
        )
      )
    )
  )

  CREATE TABLE #PageIndex
  (
    [IndexID] int IDENTITY (1, 1) NOT NULL,
    [ProductID] int NOT NULL
  )

  INSERT INTO #PageIndex ([ProductID])
  SELECT ProductID
  FROM #DisplayOrderTmp with (NOLOCK)
  GROUP BY ProductID
  ORDER BY min([ID])

  --total records
  SET @TotalRecords = @@rowcount  
  SET ROWCOUNT @RowsToReturn
  
  --return
  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
    #PageIndex [pi]
    INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID
  WHERE
    [pi].IndexID > @PageLowerBound AND
    [pi].IndexID < @PageUpperBound
  ORDER BY
    IndexID
  
  SET ROWCOUNT 0

  DROP TABLE #FilteredSpecs
  DROP TABLE #DisplayOrderTmp
  DROP TABLE #PageIndex
/*
--- Test Harness
exec [dbo].[Nop_ProductLoadAllPaged]
*/
END

b.manolache wrote:

Thanks manolache!
But i don't know where is  dbo.FUZION_isMatchingKeywords ?
can you explain me?

Fuzions original source code has also 2 functions. I pasted them below.


/****** Object:  UserDefinedFunction [dbo].[FUZION_isAvailableToday]    Script Date: 09/19/2010 00:35:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: www.FUZION.org - Jared Nielsen
-- Create date: September 18, 2010
-- Description:  Determines whether at least one Product Variant is available for a certain time period
-- =============================================
CREATE FUNCTION [dbo].[FUZION_isAvailableToday]
(
  @ProductID int
)
RETURNS bit
AS
BEGIN
  DECLARE @isAvailable bit
  SELECT @isAvailable = CASE WHEN getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999') THEN 1 END
  FROM Nop_ProductVariant PV
  WHERE PV.ProductID=@ProductID
  RETURN @isAvailable
END

GO

/****** Object:  UserDefinedFunction [dbo].[FUZION_isMatchingKeywords]    Script Date: 09/19/2010 00:35:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: www.FUZION.org - Jared Nielsen
-- Create date: September 18, 2010
-- Description:  Checks to see if a keyword matches various text fields in a product or product variant
-- =============================================
CREATE FUNCTION [dbo].[FUZION_isMatchingKeywords]
(
  @ProductID int
  , @Keywords nvarchar(max)
  , @SearchDescriptions int
)
RETURNS bit
AS
BEGIN
  DECLARE @isMatching bit
  SELECT @isMatching =
    CASE
      WHEN patindex(@Keywords, isnull(p.name, '')) > 0 THEN 1
      WHEN patindex(@Keywords, isnull(pv.name, '')) > 0 THEN 1
      WHEN patindex(@Keywords, isnull(pv.sku , '')) > 0 THEN 1
      WHEN (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)  THEN 1
      WHEN (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)  THEN 1
      WHEN (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)    THEN 1
      ELSE 0
    END
  FROM
    Nop_Product P
    INNER JOIN Nop_ProductVariant PV
      ON P.ProductId=PV.ProductID
  WHERE P.ProductId=@ProductID
  RETURN @isMatching
/*
  --Test Harness--
  select dbo.FUZION_isMatchingKeywords(9,'%Nitto%',1)
  select Name,patindex('%Nitto%',Name) from nop_Product where ProductID=9
*/  
END

GO

I rewrote the whole nop_productloadallpaged stored proc with CTEs to avoid this many temp tables.

On my 27000 product database, I managed to reduce execution time from 21 seconds to 2 seconds (for max longint page) and to 700ms for a 20 items page.

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,
  @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
  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)
      INNER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
      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
    WHERE
    (
      --P.ProductId = 9
      --AND
      (
        -- See if product is published
        @ShowHidden = 1 OR p.Published = 1
      )
      AND
      (
        -- See if at least one product variant is published
        @ShowHidden = 1 OR (SELECT Max(Convert(int,Published)) FROM nop_ProductVariant where ProductID=p.ProductID) = 1
      )
      AND
      (
        p.Deleted=0
      )
      AND
      (
        -- Make sure the product is listed between a certain price range
        (SELECT Min(Price) FROM nop_ProductVariant WHERE ProductID=p.ProductID GROUP BY ProductID)
        BETWEEN IsNull(@PriceMin,0) AND IsNull(@PriceMax,999999999999)
      )
      AND
      (
        @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
    (
  -- search standard content
      patindex(@Keywords, isnull(p.name, '')) > 0
      or patindex(@Keywords, isnull(pv.name, '')) > 0
      or patindex(@Keywords, isnull(pv.sku , '')) > 0
      or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)
      or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)
      or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)
      -- search language content
      -- commented out localized product variants to save speed
      --or patindex(@Keywords, isnull(pl.name, '')) > 0
      --or patindex(@Keywords, isnull(pvl.name, '')) > 0
      --or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.ShortDescription, '')) > 0)
      --or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.FullDescription, '')) > 0)
      --or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(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.ProductTypeId,
    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
/*
--- Test Harness
exec [dbo].[Nop_ProductLoadAllPaged] @PageSize=2147483644
*/
END


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

PS: this is for 1.7

b.manolache wrote:

Thank you to Fuzion and p.manolache for sharing this solution. It works great on V1.8 and solved our slow manage products issue.

Thanks again!

JC