Nop_ProductLoadAllPaged performance

Why not using pagination TSQL syntax to ha better performance finding a range of products in Sql Server?

you could look at this page to understand what i'm talknig about.

Obviously it will work only for SQL Server 2005 or greater :

http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

i will give it a try and i will let you know the result as soon as i can.


If anyone have time to try it before i do, please tell us ;)

Bye,
Luigi

I changed the stored procedure this way and now seems to work better....but i have still not verified nop_commerce reaction to the change, i only tried to call the stored with some sensed parameters....
the output of the query produce  a string like this one :
CALLING the stored procedure with this parameters :

execute NopCommerce_1_8_0.dbo.Nop_ProductLoadAllPaged 0,0,0,null,0,100000,0,'',0,1,2,30,null,7,0,null

a

OK , i got it, the new version of my stored procedure is the one below.

Comments are appreciated!!

I forgot to assign a value to @totalrecords, that let's know how many records are returned by the query, for the pagination.
I have found only one way to assign the right value to @totalrecords, i executed again the query but without orderby,  getting only Count(*)  in the select and assigning it to @totalrecords.

I have 120.000 products with wide description and now i wait more or less 20 seconds in Admin area to load products, and avoid the timeout.


here is the stored  :


USE [NopCommerce_1_8_0]
GO
/****** Object:  StoredProcedure [dbo].[Nop_ProductLoadAllPaged]    Script Date: 10/18/2010 11:00:09 ******/
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 = 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, 15 - creation date
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  declare @sql nvarchar(4000)
  declare @orderbysql nvarchar(500)
  --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;
  


set @orderbysql = 'ORDER BY'


set @orderbysql =
CASE  when @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
    THEN @orderbysql + ' pcm.DisplayOrder ASC,' else @orderbysql end




select @orderbysql =     
CASE WHEN @OrderBy = 0 AND @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
    THEN @orderbysql + ' pmm.DisplayOrder ASC,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 0 AND @RelatedToProductID IS NOT NULL AND @RelatedToProductID > 0
    THEN @orderbysql + ' rp.DisplayOrder asc,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 0
    THEN @orderbysql + ' isnull(p.name,pl.name) asc,'
else @orderbysql end




select @orderbysql =     
CASE WHEN @OrderBy = 5
    THEN @orderbysql +  ' isnull(p.name,pl.name) asc,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 10
    THEN @orderbysql + ' pv.Price asc,' else @orderbysql END


select @orderbysql =     
CASE WHEN @OrderBy = 15
    THEN @orderbysql + ' p.CreatedOn desc,' else @orderbysql END

    
select @orderbysql = LEFT(@orderbysql,LEN(@orderbysql)-1)
    

set @sql =
'SELECT * FROM Nop_Product nopp inner join
  (SELECT ROW_NUMBER() OVER
    (' + @orderbysql +    
    ') as ROWNUMBER,
    p.ProductId,
    isnull(p.Name,pl.name) as pname,
    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 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 = ' + convert(nvarchar,@LanguageID) + '
  LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = ' + convert(nvarchar,@LanguageID) + '
  WHERE
    (
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR p.Published = 1
      )
    AND
      (
        p.Deleted=0
      )
    AND
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR pv.Published = 1
      )
    AND
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR pv.Deleted = 0
      )
    AND (
        ' + convert(nvarchar,@CategoryID) + 'IS NULL OR '  +convert(nvarchar,@CategoryID) + '=0
        OR (pcm.CategoryID='  +convert(nvarchar,@CategoryID) + ' AND ('  +convert(nvarchar,isnull(@FeaturedProducts,0)) + ' IS NULL OR pcm.IsFeaturedProduct='  +convert(nvarchar,isnull(@FeaturedProducts,0)) + '))
      )
    AND (
        '  +convert(nvarchar,@ManufacturerID) + ' IS NULL OR '  +convert(nvarchar,@ManufacturerID) + ' =0
        OR (pmm.ManufacturerID='  +convert(nvarchar,@ManufacturerID) + ' AND ('  +convert(nvarchar,isnull(@FeaturedProducts,0)) + ' IS NULL OR pmm.IsFeaturedProduct='  +convert(nvarchar,isnull(@FeaturedProducts,0)) + '))
      )
    AND (
        '  +convert(nvarchar,@ProductTagID) + ' IS NULL OR '  +convert(nvarchar,@ProductTagID) + '=0
        OR ptpm.ProductTagID='  +convert(nvarchar,@ProductTagID) + '
      )
    AND (
        '  +convert(nvarchar,@RelatedToProductID) + ' IS NULL OR '  +convert(nvarchar,@RelatedToProductID) + '=0
        OR rp.ProductID1='  +convert(nvarchar,@RelatedToProductID) + '
      )
    AND (
        pv.Price BETWEEN '  +convert(nvarchar,@PriceMin) + ' AND '  +convert(nvarchar,@PriceMax) + '
      )
     AND  (
        -- search standard content
         patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(p.name, '''')) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pv.name, '''')) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pv.sku , '''')) > 0
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(p.ShortDescription, '''')) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(p.FullDescription, '''')) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pv.Description, '''')) > 0)          
        -- search language content
         or patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pl.name, '''')) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pvl.name, '''')) > 0
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pl.ShortDescription, '''')) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pl.FullDescription, '''')) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', isnull(pvl.Description, '''')) > 0)
       )
    AND
      (
        '  +convert(nvarchar,@ShowHidden) + ' = 1
        OR
        (getutcdate() between isnull(pv.AvailableStartDateTime, ''1/1/1900'') and isnull(pv.AvailableEndDateTime, ''1/1/2999''))
      )
    AND
      (
        --filter by specs
        '  +convert(nvarchar,@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
              )
            )
          
        )
      )
    )
  ) res on res.ProductId = nopp.ProductId
  and res.ROWNUMBER >= '  +convert(nvarchar,@PageLowerBound) + '
  AND ROWNUMBER <= '  +convert(nvarchar,@PageUpperBound) + ''
  
  

SELECT @TotalRecords = COUNT(*)
  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
              )
            )
          
        )
      )
    )
    
    

  
  execute sp_executesql @sql
  
  
  


  SET ROWCOUNT 0

  DROP TABLE #FilteredSpecs

END

Thanks a lot

I also added 2 indexes in Nop_ProductVariant columns : Name and Sku to gain few seconds

Optimized again....halved execution time, avoiding to execute a new query for count records and assigning the total to @totalrecords...this is the new version of the stored


USE [NopCommerce_1_8_0]
GO
/****** Object:  StoredProcedure [dbo].[Nop_ProductLoadAllPaged]    Script Date: 10/26/2010 21:02:09 ******/
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 = 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, 15 - creation date
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  
  
  declare @ParmDefinition nvarchar(500)
  declare @tmpval int
  declare @sql nvarchar(4000)
  declare @sqlrowcount nvarchar(4000)
  declare @orderbysql nvarchar(500)
  declare @SearchKeywords bit
  
  --init
  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)
  
  --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;
  

  
  



------------- CREATE Temporary Table structure to maintain it also after "sp_executeSQL" ---------------------
SELECT 0 as tot,
  0 as ROWNUMBER,
    p.ProductId  
    into #tmpPage
  FROM Nop_Product p with (NOLOCK)
  LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = @LanguageID
  WHERE 0=1




set @orderbysql = 'ORDER BY'
--raiserror(@orderbysql,16,1)

set @orderbysql =
CASE  when @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
    THEN @orderbysql + ' pcm.DisplayOrder ASC,' else @orderbysql end




select @orderbysql =     
CASE WHEN @OrderBy = 0 AND @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
    THEN @orderbysql + ' pmm.DisplayOrder ASC,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 0 AND @RelatedToProductID IS NOT NULL AND @RelatedToProductID > 0
    THEN @orderbysql + ' rp.DisplayOrder asc,' else @orderbysql END



select @orderbysql =     
CASE WHEN @OrderBy = 0
    THEN @orderbysql + ' isnull(p.name,pl.name) asc,'
else @orderbysql end

--raiserror(@orderbysql,15,1)


select @orderbysql =     
CASE WHEN @OrderBy = 5
    THEN @orderbysql +  ' isnull(p.name,pl.name) asc,' else @orderbysql END
--raiserror(@orderbysql,16,1)


select @orderbysql =     
CASE WHEN @OrderBy = 10
    THEN @orderbysql + ' pv.Price asc,' else @orderbysql END
--raiserror(@orderbysql,16,1)

select @orderbysql =     
CASE WHEN @OrderBy = 15
    THEN @orderbysql + ' p.CreatedOn desc,' else @orderbysql END
--raiserror(@orderbysql,16,1)


--remove ending Comma    
select @orderbysql = LEFT(@orderbysql,LEN(@orderbysql)-1)
    
---------------- Create query that retrieve Products and populate Temporary table with results --------
set @sql =
N'INSERT INTO #tmpPage SELECT res.*  FROM Nop_Product nopp inner join
  (SELECT DISTINCT COUNT(*) OVER() as tot,
  ROW_NUMBER() OVER
    (' + @orderbysql +    
    ') as ROWNUMBER,
    p.ProductId    
      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 = ' + convert(nvarchar,@LanguageID) + '
  LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = ' + convert(nvarchar,@LanguageID) + '
  WHERE
    (
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR p.Published = 1
      )
    AND
      (
        p.Deleted=0
      )
    AND
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR pv.Published = 1
      )
    AND
      (
        ' + convert(nvarchar,@ShowHidden) + '= 1 OR pv.Deleted = 0
      )
    AND (
        ' + convert(nvarchar,@CategoryID) + 'IS NULL OR '  +convert(nvarchar,@CategoryID) + '=0
        OR (pcm.CategoryID='  +convert(nvarchar,@CategoryID) + ' AND ('  +convert(nvarchar,isnull(@FeaturedProducts,0)) + ' IS NULL OR pcm.IsFeaturedProduct='  +convert(nvarchar,isnull(@FeaturedProducts,0)) + '))
      )
    AND (
        '  +convert(nvarchar,@ManufacturerID) + ' IS NULL OR '  +convert(nvarchar,@ManufacturerID) + ' =0
        OR (pmm.ManufacturerID='  +convert(nvarchar,@ManufacturerID) + ' AND ('  +convert(nvarchar,isnull(@FeaturedProducts,0)) + ' IS NULL OR pmm.IsFeaturedProduct='  +convert(nvarchar,isnull(@FeaturedProducts,0)) + '))
      )
    AND (
        '  +convert(nvarchar,@ProductTagID) + ' IS NULL OR '  +convert(nvarchar,@ProductTagID) + '=0
        OR ptpm.ProductTagID='  +convert(nvarchar,@ProductTagID) + '
      )
    AND (
        '  +convert(nvarchar,@RelatedToProductID) + ' IS NULL OR '  +convert(nvarchar,@RelatedToProductID) + '=0
        OR rp.ProductID1='  +convert(nvarchar,@RelatedToProductID) + '
      )
    AND (
        '  +convert(nvarchar,@PriceMin) + ' IS NULL OR ' + convert(nvarchar,@PriceMin) + '=0
        OR pv.Price >  '  +convert(nvarchar,@PriceMin) + '   
      )
    AND (
        '  +convert(nvarchar,@PriceMax) + ' IS NULL OR ' + convert(nvarchar,@PriceMax) + '=2147483644 -- max value
        OR pv.Price < '  +convert(nvarchar,@PriceMax) + '
      )
     AND  (
        -- search standard content
        '  +convert(nvarchar,@SearchKeywords) + ' = 0 or
        (
         patindex('''  +convert(nvarchar,@Keywords) + ''', p.name) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pv.name) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pv.sku) > 0
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', p.ShortDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', p.FullDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pv.Description) > 0)          
        -- search language content
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pl.name) > 0
         or patindex('''  +convert(nvarchar,@Keywords) + ''', pvl.name) > 0
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pl.ShortDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pl.FullDescription) > 0)
         or ('  +convert(nvarchar,@SearchDescriptions) + ' = 1 and patindex('''  +convert(nvarchar,@Keywords) + ''', pvl.Description) > 0)
        )
       )
    AND
      (
        '  +convert(nvarchar,@ShowHidden) + ' = 1
        OR
        (getutcdate() between isnull(pv.AvailableStartDateTime, ''1/1/1900'') and isnull(pv.AvailableEndDateTime, ''1/1/2999''))
      )
    AND
      (
        --filter by specs
        '  +convert(nvarchar,@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
              )
            )
          
        )
      )
    )
  ) res on res.ProductId = nopp.ProductId
  and res.ROWNUMBER >= '  +convert(nvarchar,@PageLowerBound) + '
  AND ROWNUMBER <= '  +convert(nvarchar,@PageUpperBound) + ''
  
  
  print(@sql)
  

  --Populate Temporary Table with Products search result
  execute sp_executesql @sql;
  
  
  --Assign Value to @TotalRecords, stored in column "tot" of every record of the temporary table #tmpPage
  SET @ParmDefinition = N'@totrecords int OUTPUT'
  SET @sql = 'SELECT @totrecords = MAX(tot) FROM #tmpPage'
  execute sp_executesql
  @sql,
  @ParmDefinition,
  @totrecords = @TotalRecords OUTPUT;
  
  SET @TotalRecords = isnull(@TotalRecords,0)
  
  --Perform SELECT to return exact columns that NopCommerce expected from old Stored Procedure
  SELECT DISTINCT tb1.* FROM
  (SELECT  TOP(@TotalRecords) p.ProductId,
    p.name as 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 #tmpPage pc
    LEFT JOIN Nop_Product p on p.ProductId = pc.ProductId
  ORDER BY pc.ROWNUMBER) tb1
  --ORDER BY necessary cause getting from temporary table order is not garanted to be the one used to create ROWNUMBER values


  
      --Can Be Null if no records selected with the parameters passed to the stored procedure
  SET @TotalRecords = isnull(@TotalRecords,0)
  
  
  SET ROWCOUNT 0

  DROP TABLE #FilteredSpecs
  DROP Table #tmpPage
  
  
END

I am sorry to say that this new procedure doesn't work. On my database it returns twice as much data as it should - i.e. it duplicates all products which are of the single product variant.

I am just going to print out and compare with the original to see if I can spot the problem...

I was mistaken in my description above - it doesn't work for all products which have more than one variant.

The reason why is because the original code does a GROUP BY ProductID by using the following code:

  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])

Note the GROUP BY. This code is missing from the proc posted on this thread. I modified the new proc to use the

I have also ommented out the rowcount code and using @@rowcount placed after the insert into #PageIndex - code added to bottom of procedure:

  --comment out the print as it may be affecting execution times..
  --print(@sql)
  

  --Populate Temporary Table with Products search result
  execute sp_executesql @sql;
  
  
  --Assign Value to @TotalRecords, stored in column "tot" of every record of the temporary table #tmpPage
  /*
  SET @ParmDefinition = N'@totrecords int OUTPUT'
  SET @sql = 'SELECT @totrecords = MAX(tot) FROM #tmpPage'
  execute sp_executesql
  @sql,
  @ParmDefinition,
  @totrecords = @TotalRecords OUTPUT;
*/

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

  INSERT INTO #PageIndex ([ProductID])
  SELECT ProductID
  FROM #tmpPage with (NOLOCK)
  GROUP BY ProductID
  ORDER BY min([ROWNUMBER])
      
  --total records
  SET @TotalRecords = @@rowcount  
  SET ROWCOUNT @RowsToReturn

  --Perform SELECT to return exact columns that NopCommerce expected from old Stored Procedure
  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
  
  
  --Can Be Null if no records selected with the parameters passed to the stored procedure
  SET @TotalRecords = isnull(@TotalRecords,0)
    
  
  SET ROWCOUNT 0

  DROP TABLE #FilteredSpecs
  DROP Table #tmpPage
  DROP TABLE #PageIndex  
  
END

When I run the modified procedure, it works as I would expect, but is only slightly faster than the original, 1500 ms compared to 1700ms for the original on the one query I tested it with...

So in conclusion, I might keep the original proc for now as its not worth risking such a big change on a live site for this percentage improvement, not least until the new proc is fully tested...

I hope this helps somebody..

I'm sorry, the fix I have specified - the GROUP BY does not work correctly. Its not returning the same results in my database as the original live procedure did.

I have spent a few hours today trying to work out what the correct fix should be but have given up for now because my strategy is to sideline this stored proc in any case, its really slow in my system - please see my other posts on SLOW PAGE LOADS, I am approx half way through sidelining this stored proc already...