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