Optimizing sql query in nopcommerce

I am optimizing a sql query and I welcome advice on how to improve this query. It is part of nopcommerce's free solution to download products in categories. At this time, it takes about 8-9 seconds to load the page, we can bring this to 3-4 seconds if we can.

Here is a request, I welcome every piece of advice on how I can improve this:

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