I am optimizing sql query and I am welcoming advices how to improve this query. It's part of nopcommerce open source solution for loading products in categories. At this time it takes some 8-9 seconds to load the page, we want to bring that to 3-4 seconds if we can.
Here is the query, I am welcoming every advice how I can improve that:
ALTER PROCEDURE [dbo].[Nop_ProductLoadForCategory]
(
@CategoryID int = 0,
@manufacturerId int = 0,
@PageIndex int = 0,
@PageSize int = 2147483644,
@TotalRecords int = null OUTPUT,
@VehiclesYear varchar(4) = null,
@VehiclesMake varchar(20) = null,
@VehiclesModel varchar(50) =null,
@VehiclesSubmodel varchar(50) =null,
@Universal bit = 1
)
AS
SET NOCOUNT ON
BEGIN
--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
)
if isnull(@manufacturerId, 0) = 0
if @Universal = 1
INSERT INTO #DisplayOrderTmp ([ProductID])
select a.ProductID from (
SELECT TOP 100 PERCENT
row_number() over(order by pv.DisplayOrder) as ID,
p.ProductID
FROM dbo.Nop_Product p with (NOLOCK)
INNER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON pv.ProductID = p.ProductId
left outer join WC_ProductVehicleApplications pva with (NOLOCK) on pva.ProductID=p.ProductId
left outer join [WC_Vehicles] v with (NOLOCK) on pva.VehicleID = v.VehicleID
WHERE
(pcm.CategoryID=@CategoryID)
AND (p.Published = 1)
AND (p.Deleted=0)
and (((@VehiclesYear between [YEAR] and YearEnd or @VehiclesYear is null)
and ([Make] = @VehiclesMake or @VehiclesMake is null)
and ([Model] = @VehiclesModel or @VehiclesModel is null)
and ([SubModel] = @VehiclesSubmodel or @VehiclesSubmodel is null))
or p.IsUniversal = 1)
) a
GROUP BY
ProductID
ORDER BY
min([ID])
else
INSERT INTO #DisplayOrderTmp ([ProductID])
select a.ProductID from (
SELECT TOP 100 PERCENT
row_number() over(order by pv.DisplayOrder) as ID,
p.ProductID
FROM dbo.Nop_Product p with (NOLOCK)
INNER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON pv.ProductID = p.ProductId
left outer join WC_ProductVehicleApplications pva with (NOLOCK) on pva.ProductID=p.ProductId
left outer join [WC_Vehicles] v with (NOLOCK) on pva.VehicleID = v.VehicleID
WHERE
(pcm.CategoryID=@CategoryID)
AND (p.Published = 1)
AND (p.Deleted=0)
and (((@VehiclesYear between [YEAR] and YearEnd or @VehiclesYear is null)
and ([Make] = @VehiclesMake or @VehiclesMake is null)
and ([Model] = @VehiclesModel or @VehiclesModel is null)
and ([SubModel] = @VehiclesSubmodel or @VehiclesSubmodel is null))
and p.IsUniversal <> 1)
) a
GROUP BY
ProductID
ORDER BY
min([ID])
else
INSERT INTO #DisplayOrderTmp ([ProductID])
select a.ProductID from (
SELECT TOP 100 PERCENT
row_number() over(order by pv.DisplayOrder) as ID,
p.ProductID
FROM dbo.Nop_Product p with (NOLOCK)
INNER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON pv.ProductID = p.ProductId
left outer join WC_ProductVehicleApplications pva with (NOLOCK) on pva.ProductID=p.ProductId
left outer join [WC_Vehicles] v with (NOLOCK) on pva.VehicleID = v.VehicleID
WHERE
(pcm.CategoryID=@CategoryID)
AND (p.Published = 1)
AND (p.Deleted=0)
and (((@VehiclesYear between [YEAR] and YearEnd or @VehiclesYear is null)
and ([Make] = @VehiclesMake or @VehiclesMake is null)
and ([Model] = @VehiclesModel or @VehiclesModel is null)
and ([SubModel] = @VehiclesSubmodel or @VehiclesSubmodel is null))
or p.IsUniversal = 1)
) a
GROUP BY
ProductID
ORDER BY
min([ID])
--CREATE UNIQUE NONCLUSTERED INDEX IX_1 on #DisplayOrderTmp ([ID], [ProductID])
--CREATE TABLE #PageIndex
--(
-- [IndexID] int IDENTITY (1, 1) NOT NULL,
-- [ProductID] int NOT NULL
--)
--INSERT INTO #PageIndex ([ProductID])
--SELECT TOP 100 PERCENT
-- Row_Number() Over(Order By min([ID])) as RowNum, ProductID
--FROM #DisplayOrderTmp with (NOLOCK)
--GROUP BY ProductID
--ORDER BY min([ID])
--select ProductID
--FROM #DisplayOrderTmp with (NOLOCK)
--GROUP BY ProductID
--SELECT
-- ProductID
--FROM
-- #DisplayOrderTmp with (NOLOCK)
--GROUP BY
-- ProductID
--ORDER BY
-- min([ID])
--select ProductID from #DisplayOrderTmp with (NOLOCK) --order by min([ID])
--CREATE UNIQUE NONCLUSTERED INDEX IX_2 on #PageIndex ([IndexID], [ProductID])
--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,
p.[IsUniversal],
p.FullDescriptionSave
FROM
--(SELECT TOP 100 PERCENT
-- Row_Number() Over(Order By min([ID])) as RowNum, ProductID
--FROM #DisplayOrderTmp with (NOLOCK)
--GROUP BY ProductID
--ORDER BY min([ID])
--) [pi]
--inner join
#DisplayOrderTmp [pi]
--on dot.ProductID = [pi].ProductID
INNER JOIN Nop_Product p with (NOLOCK) on p.ProductID = [pi].ProductID
INNER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
WHERE
--[pi].IndexID > @PageLowerBound AND
--[pi].IndexID < @PageUpperBound
[pi].ID > @PageLowerBound AND
[pi].ID < @PageUpperBound
ORDER BY
[pi].ID
SET ROWCOUNT 0
DROP TABLE #DisplayOrderTmp
END
Thanks in advance, Laziale
