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

There is something very wrong with Manage Products section in Admin area .
When I hit edit product it takes some time, usually 4-5 times longer than in V1.4 version until data is displayed .
In current state editing products in other than demo sites is almost impossible.
It looks that there are some cache misses and locale data is loaded on every request.

Here is sql profiler data screen shot - http://refactored.eu/images/sshot.gif.
First window for V1.4 , second for V1.6  - take a look at times and scroll bar.

You are not the only one. I've had this issue for weeks and still not have resolved it. We have increased memory at Applied Innovations and still no results. Have you resolved your issue?

Try new nopCommerce 1.80 and let me know if you still experience this issue

Queries in 1.8 looks much better but  perf. issue still exist to some degree.
I didn't have much time to look into code, but disabling HTML editor solves all of this problems - navigating to "Edit Product Detail" feels 4x times faster - the same as in V1.4, so it must be something with this control .

We have upgraded to V1.8 and modification of products are still slow.
Slawek, you mentioned that "disabling the HTML editor" solves all of these problems". Please let me know where to disable the HTML Editor. Thanks

Pysanka wrote:

I disabled HTMl editor in code only to check what was causing this slow behaviour.  Queries were ok so it had to be something else - in this case editor control or something in the process of rendering it, but I didn't have much time for investigation.  If you disable this control you won't be able to edit product description.

Now i'm playing with compiled linq queries to find if it will give us some performance gains.

Not sure if this is your problem but there are several SQL queries that need optimization..

This stored procedure started really choking our system on manufacturer product queries, product detail pages and admin pages.

On 19 nop_product records with a deep bench of product attributes and categories, this query would return over 500,000 rows in a resultset, which were then copied into a temp table using an orderby clause.

If you alter the proc to use DISTINCT and eliminate the ORDERBY section (don't need it for temp table inserts), then the site speeds up considerably.

I wouldn't assume that all the queries are tuned properly.

Thanks!

Jared Nielsen
www.FUZION.org

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,
  @Keywords      nvarchar(MAX),  
  @SearchDescriptions bit = 0,
  @ShowHidden      bit = 0,
  @PageIndex      int = 0,
  @PageSize      int = 2147483644,
  @FilteredSpecs    nvarchar(300) = null,  --filter by attributes (comma-separated list). e.g. 14,15,16
  @LanguageID      int = 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, pv.Price, pcm.DisplayOrder, pmm.DisplayOrder
  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_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
    (
      (
        @ShowHidden = 1 OR p.Published = 1
      )
    AND
      (
        @ShowHidden = 1 OR pv.Published = 1
      )
    AND
      (
        p.Deleted=0
      )
    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 (
        pv.Price BETWEEN @PriceMin AND @PriceMax
      )
    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
        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
              )
            )
          
        )
      )
    )
  --ORDER BY
  --  CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
  --  THEN pcm.DisplayOrder END,
  --  CASE WHEN @OrderBy = 0 AND @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
  --  THEN pmm.DisplayOrder END,
  --  CASE WHEN @OrderBy = 0
  --  THEN dbo.NOP_getnotnullnotempty(pl.[Name],p.[Name]) END,
  --  CASE WHEN @OrderBy = 5
  --  THEN dbo.NOP_getnotnullnotempty(pl.[Name],p.[Name]) END,
  --  CASE WHEN @OrderBy = 10
  --  THEN pv.Price END

  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.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
    #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
END

FUZION wrote:


Fusion, you're right:). I made this change in procedure, and the site was much speed. Do you have any suggestions for other procedure? Slow Manage Products section in Admin area V1.6, 1,7 & 1,8 Thanks ..

I'm using V1.8 and when i run this spl in sql server, i get this error, can you help me!

 Procedure or function Nop_ProductLoadAllPaged has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function Nop_ProductLoadAllPaged has too many arguments specified.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Thank's so much!

I think this is a case where a stored proc initially designed to hydrate a cached product list is now being used for about eight different functions (filtering, search results, etc).

The biggest problem is the recursive looping it does where every recursive loop does a complete table scan rather than a filtered recursion.

I am going to rewrite this query using CTEs and proper recursion and possibly will break it into several procs (one for search, one for filtering, one for raw product listings).

I think we are also going to shove the resultset into a SQL Dependency Cache so the query won't run every time a page loads.  It's just really punishing our servers right now.

I'll let you know when I have the code base for it.  I'm on 1.7 but I will do a 1.8 version as well.

Thanks,

Jared Nielsen
producer@FUZION.org