optimizing sql query in nopcommerce

Question

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

Answer 1

NopCommerce is deadly slow. If you have more than few thousands products, you're already in trouble. You will need to do a heavy caching for Home Page, Category Home & Product Listing page.

We have the same issue, after implementing a cache we bring down the load time to 1.5-2 seconds from the 8-10 seconds. We have load tested Nop Commerce 2.2/2.3/2.4/2.5 but frankly speaking the performance improvement is very minor in 2.5 and you should look at the aggressive caching to improve site performance. Without cache your site will see a huge issues only with few hundred simultaneous requests.

EBarr is right, its not the SQL that is slow, the application is also very slow if you profile it using any good profiler. Note that NopCommerce has mini profiler already implemented, which you can enable from admin side.

Update 30 October 2015

Numbers of newer versions of nopCommerce has been released since this answer was originally written, and it has improved significantly in terms of out of the box performance.

Moreover, we integrated nopCommerce with Apache Solr, that way nopCommerce can be used for huge sites with millions of products and visitors, with faster catalog navigation pages, faster facets and improved and faster drill down search. The integration is done as a standard plugin which is popular as nopAccelerate http://www.nopaccelerate.com/