Help optimizing a SQL query

Can any one give me any pointers optimizing the SQL Query below. I am not great with SQL but the below isn't even paging data efficiently as far as I can tell?

    GO
/****** Object:  StoredProcedure [dbo].[Nop_ProductLoadAllPaged]    Script Date: 04/25/2011 13:26:开发者_C百科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

As much as I hate doing this myself, I think you might need to break this down into some separate IF blocks.

It looks to me like you're joining on a lot of tables that aren't necessarily needed based on the input parameters. This is some needless overhead if speed is your concern. You can streamline the process by creating blocks that will only join on the needed tables. It kind of makes your SP a little messier, and I'd suggest maybe creating functions or other separate SPs to call within your case blocks.

If you'd like to keep things mostly the way that they are we'd need to see an actual execution plan to get a grasp on what might be happening. Without that it's just a guess.

Try using SQL Server's option to "Display Estimated Execution Plan" or "Display Actual Execution Plan" to see which parts of your query are taking up the most time. Doing this may also potentially give you suggestions for additional indexes.