Is anyone able to offer some advice on optimizing the query below?
I am not great with SQL, but isn't the below not doing efficient data paging?
I am not great with SQL, but isn't the below not doing efficient data paging?
Code:
GO
/****** Object: StoredProcedure [dbo].[Nop_ProductLoadAllPaged] Script Date: 04/25/2011 13:26:39 ******/
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
@WarehouseCombinationID int,
@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)) + '%'
--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
)
INSERT INTO #DisplayOrderTmp ([ProductID])
SELECT 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_ProductVariant_Warehouse_Mapping wpv with (NOLOCK) ON pv.ProductVariantID = wpv.ProductVariantID
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 wpv.Deleted=0
)
AND
(
@ShowHidden = 1 OR pv.Published = 1
)
AND (
@ShowHidden = 1 OR wpv.Published = 1
)
AND
(
@ShowHidden = 1 OR pv.Deleted = 0
)
AND (
@PriceMin IS NULL OR @PriceMin=0
OR wpv.Price > @PriceMin
)
AND (
@PriceMax IS NULL OR @PriceMax=2147483644 -- max value
OR wpv.Price < @PriceMax
)
AND (
wpv.WarehouseID IN (select WarehouseID from Nop_WarehouseCombination where UserWarehouseCombinationID = @WarehouseCombinationID)
)
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
)
)
)
)
)
ORDER BY
CASE WHEN @OrderBy = 0 AND @CategoryID IS NOT NULL AND @CategoryID > 0
THEN pcm.DisplayOrder END ASC,
CASE WHEN @OrderBy = 0 AND @ManufacturerID IS NOT NULL AND @ManufacturerID > 0
THEN pmm.DisplayOrder END ASC,
CASE WHEN @OrderBy = 0 AND @RelatedToProductID IS NOT NULL AND @RelatedToProductID > 0
THEN rp.DisplayOrder END ASC,
CASE WHEN @OrderBy = 0
THEN p.[Name] END ASC,
CASE WHEN @OrderBy = 5
THEN dbo.NOP_getnotnullnotempty(pl.[Name],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
DROP TABLE #FilteredSpecs
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
DROP TABLE #DisplayOrderTmp
--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 with (NOLOCK) on p.ProductID = [pi].ProductID
WHERE
[pi].IndexID > @PageLowerBound AND
[pi].IndexID < @PageUpperBound
ORDER BY
IndexID
SET ROWCOUNT 0
DROP TABLE #PageIndex
END
