帮助优化 SQL 查询

Question

任何人都可以给我任何优化下面的 SQL 查询的指导吗？我不太擅长 SQL，但据我所知，下面的内容甚至不能有效地分页数据？

    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

Answer 1

尽管我讨厌自己这样做，但我认为您可能需要将其分解为一些单独的 IF 块。

在我看来，您正在加入许多根据输入参数不一定需要的表。如果您关心速度，那么这是一些不必要的开销。您可以通过创建仅连接所需表的块来简化流程。这会让你的 SP 变得有点混乱，我建议也许创建函数或其他单独的 SP 来在你的 case 块中调用。

如果您想让事情保持原样，我们需要查看实际的执行计划以了解可能发生的情况。如果没有的话，这只是一个猜测。

Answer 2

尝试使用 SQL Server 的“显示估计执行计划”或“显示实际执行计划”选项来查看查询的哪些部分占用了最多时间。这样做还可能为您提供有关其他索引的建议。