Блог

Может ли кто-нибудь дать мне какие-либо указания по оптимизации 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
  

Как бы я ни ненавидел делать это сам, я думаю, вам, возможно, потребуется разбить это на несколько отдельных блоков IF.

Мне кажется, что вы объединяете множество таблиц, которые необязательно нужны на основе входных параметров. Это ненужные накладные расходы, если вас беспокоит скорость. Вы можете упростить процесс, создав блоки, которые будут объединяться только в необходимых таблицах. Это немного усложняет ваш SP, и я бы предложил, возможно, создать функции или другие отдельные SP для вызова в ваших блоках case.

Если вы хотите, чтобы все оставалось в основном таким, какое оно есть, нам нужно было бы увидеть фактический план выполнения, чтобы получить представление о том, что может происходить. Без этого это всего лишь предположение.

Попробуйте использовать опцию SQL Server для «Отображения расчетного плана выполнения» или «Отображения фактического плана выполнения», чтобы увидеть, какие части вашего запроса занимают больше всего времени. Выполнение этого также может потенциально дать вам рекомендации по дополнительным индексам.