sql - 优化 nopcommerce 中的 sql 查询

Question

我正在优化 sql 查询，欢迎提出如何改进此查询的建议。它是 nopcommerce 开源解决方案的一部分，用于按类别加载产品。此时加载页面需要大约 8-9 秒，如果可以的话，我们希望将其缩短到 3-4 秒。

这是查询，我欢迎提出如何改进的建议:

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

提前致谢，Laziale

Answer 1

NopCommerce 速度慢得要命。如果您的产品超过数千种，那么您就已经遇到麻烦了。您将需要对主页、类别主页和产品列表页面进行大量缓存。

我们也有同样的问题，在实现缓存后，我们将加载时间从 8-10 秒减少到 1.5-2 秒。我们对 Nop Commerce 2.2/2.3/2.4/2.5 进行了负载测试，但坦率地说，2.5 中的性能改进非常小，您应该考虑积极的缓存来提高站点性能。如果没有缓存，您的网站仅在几百个并发请求的情况下就会遇到巨大问题。

EBarr 是对的，慢的不是 SQL，如果使用任何好的分析器分析应用程序，应用程序也会非常慢。请注意，NopCommerce 已经实现了迷你分析器，您可以从管理端启用它。

2015 年 10 月 30 日更新

自从最初编写此答案以来，已经发布了许多新版本的 nopCommerce，并且它在开箱即用的性能方面有了显着的改进。

此外，我们将 nopCommerce 与 Apache Solr 集成，这样 nopCommerce 可用于拥有数百万产品和访问者的大型网站，具有更快的目录导航页面、更快的方面以及改进和更快的深入搜索。集成是作为标准插件完成的，该插件很受欢迎，如 nopAccelerate http://www.nopaccelerate.com/