存储过程优化有关问题

--存储过程
ALTER PROCEDURE [dbo].[p_BuyerVendorManager] 
(
    @PageIndex            INT,                --页数
    @PageSize            INT,                --每页数据条数
    @BigArea            INT,                --大区ID
    @Province            INT,                --省ID
    @City                INT,                --城市ID
    @VendorClass        INT,                --客户分类
    @SuperVenderTvaId    INT,                --客户所属
    @VendorFullName        NVARCHAR(100),        --客户名称
    @VendorTvaId        INT,                --客户tvaId
    @VendorStatus        INT,                --客户状态
    @AccountType        INT,                --客户类型
    @StartDateTime        DATETIME,            --拍品开始时间
    @EndDateTime        DATETIME,            --拍品结束时间
    @OrderByField        VARCHAR(100),        --排序字段  
    @RecordCount        INT OUTPUT            --输出总数据数                                                          
)
AS
BEGIN
    --===========================查询条件拼接====================================
    DECLARE @queryCondition VARCHAR(2000)
    SET @queryCondition=' WHERE vvm.Status<>-1';                                  
    IF @BigArea<>0
        SET @queryCondition=@queryCondition+' AND vvm.BigAreaId='+CAST(@BigArea AS VARCHAR);                      
    IF @Province<>0
        SET @queryCondition=@queryCondition+' AND vvm.ProvinceId='+CAST(@Province AS VARCHAR);
    IF @City<>0
        SET @queryCondition=@queryCondition+' AND vvm.CityID='+CAST(@City AS VARCHAR);
    IF @VendorClass<>0
        SET @queryCondition=@queryCondition+' AND vvm.VendorClass='+CAST(@VendorClass AS VARCHAR);
    IF @SuperVenderTvaId<>0
        SET @queryCondition=@queryCondition+' AND vvm.SuperVendorTvaID='+CAST(@SuperVenderTvaId AS VARCHAR);
    IF @VendorFullName<>''
        SET @queryCondition=@queryCondition+' AND vvm.VendorFullName like ''%'+@VendorFullName+'%''';
    IF @VendorTvaId<>0
        SET @queryCondition=@queryCondition+' AND vvm.TvaID='+CAST(@VendorTvaId AS VARCHAR);
    IF @VendorStatus<>-1
        SET @queryCondition=@queryCondition+' AND vvm.Status='+CAST(@VendorStatus AS VARCHAR);
    IF @AccountType<>0
        SET @queryCondition=@queryCondition+' AND (vvm.AccountType=3 OR vvm.AccountType='+CAST(@AccountType AS VARCHAR)+')';
    --=============================================================================
    DECLARE @SelectSql VARCHAR(100)
    DECLARE @FieldSql VARCHAR(MAX)
    DECLARE @BidPublishNumSql VARCHAR(1000)
    DECLARE @BuyerPublishNumSql VARCHAR(1000)
    DECLARE @AttentionPublishSql VARCHAR(1000)
    DECLARE @TradeSuccessPublishSql VARCHAR(1000)
    DECLARE @AppealedPublishSql VARCHAR(1000)
    DECLARE @FromSql VARCHAR(100)
    DECLARE @JoinSql VARCHAR(1000)
    DECLARE @ExecSql VARCHAR(MAX)
    
    SET @SelectSql='SELECT '
    SET @FieldSql='vvm.*,TradeOrder.TradeAmount,TradeOrder.Sevice_Pay,TradeOrder.Sevice_NotPay,TradeOrder.LogisticsFee,TradeOrder.TransferFee,'
    SET @BidPublishNumSql='dbo.GetBidPublish(vvm.TvaID,'''+CAST(@StartDateTime AS VARCHAR)+''','''+CAST(@EndDateTime AS VARCHAR)+''') AS BidPublishNum,'
    SET @BuyerPublishNumSql='dbo.BuyerPublish(vvm.TvaID,'''+CAST(@StartDateTime AS VARCHAR)+''','''+CAST(@EndDateTime AS VARCHAR)+''') AS BuyerPublishNum,'
    SET @AttentionPublishSql='dbo.AttentionPublish(vvm.TvaID,'''+CAST(@StartDateTime AS VARCHAR)+''','''+CAST(@EndDateTime AS VARCHAR)+''') AS AttentionPublishNum,'
    SET @TradeSuccessPublishSql='dbo.GetTradeSuccessPublish(vvm.TvaID,'''+CAST(@StartDateTime AS VARCHAR)+''','''+CAST(@EndDateTime AS VARCHAR)+''') AS TradeSuccessPublishNum,'
    SET @AppealedPublishSql='dbo.GetAppealPublish(vvm.TvaID,'''+CAST(@StartDateTime AS VARCHAR)+''','''+CAST(@EndDateTime AS VARCHAR)+''') AS AppealedPublishNum'
    SET @FromSql=' FROM V_VendorManager vvm'
    SET @JoinSql=' LEFT JOIN (SELECT * FROM dbo.GetVendorTradeData('''+CAST(@StartDateTime AS VARCHAR)+''','''+CAST(@EndDateTime AS VARCHAR)+''')) AS TradeOrder
                  ON vvm.TvaID=TradeOrder.BuyerTvaId'
    
    DECLARE @RecordCountSql NVARCHAR(4000)
    SET @RecordCountSql=@SelectSql+' @RecordCount=COUNT(1) '+@FromSql+@queryCondition
    EXEC SP_EXECUTESQL @RecordCountSql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
    
    SET @ExecSql=@SelectSql+
                 @FieldSql+
                 @BidPublishNumSql+
                 @BuyerPublishNumSql+
                 @AttentionPublishSql+
                 @TradeSuccessPublishSql+
                 @AppealedPublishSql+
                 @FromSql+
                 @JoinSql+
                 @queryCondition                 
    --实现数据的分页
    DECLARE @StartRow int,@EndRow INT
    IF @PageIndex<>-1
        BEGIN
            SET @StartRow=(@PageIndex-1)*@PageSize+1--起始页
            SET @EndRow=@StartRow+@PageSize-1--结束页                                                                  
            EXEC('
            SELECT * FROM (
                SELECT *, ROW_NUMBER() OVER (ORDER BY '+@OrderByField+') AS RowNumber
                FROM (
                        SELECT *,
                        dbo.GetTradeProportion(vmt.BuyerPublishNum, vmt.BidPublishNum) AS SuccessTradeProportion,
                        dbo.GetTradeProportion(vmt.AppealedPublishNum, vmt.BidPublishNum) AS AppealTradeProportion
                        from ('+
                        @ExecSql +') AS vmt
                ) AS vmtTable'+
            ') AS LastTable
            WHERE LastTable.RowNumber BETWEEN '+@StartRow+' AND '+@EndRow
            )
        END
    ELSE
        BEGIN
            EXEC('
                SELECT *, ROW_NUMBER() OVER (ORDER BY '+@OrderByField+') AS RowNumber
                FROM (
                        SELECT *,
                        dbo.GetTradeProportion(vmt.BuyerPublishNum, vmt.BidPublishNum) AS SuccessTradeProportion,
                        dbo.GetTradeProportion(vmt.AppealedPublishNum, vmt.BidPublishNum) AS AppealTradeProportion
                        from ('+
                        @ExecSql +') AS vmt
                ) AS vmtTable'
            )
        END
END

--自定义函数，取一个复杂的GetAppealPublish：
ALTER FUNCTION [dbo].[GetAppealPublish]
(
    @TvaId INT,
    @StartTime DATETIME,
    @EndTime DATETIME    
)
RETURNS INT
AS
BEGIN
    DECLARE @AppealPublishNum INT=0
    
    ;WITH TradeFailPublish AS (
        SELECT ato.TstOrderID
        FROM AuctionTstOrder ato
        WHERE ato.BuyerID=@TvaId AND (ato.TstResult=3 OR ato.TstResult=4) 
    ),
    
    AppealManager AS (
        SELECT ata2.ArbResult, ata2.OfflineResult, ata2.ArbTime, 
               ata.TstOrderId,atar.RespTvaId, atar.VendorType
        FROM AuctionTstAppeal ata
        LEFT JOIN AuctionTstArb ata2 ON ata.AppealId=ata2.AppealId
        LEFT JOIN AuctionTstArbResponsibility atar ON ata.AppealId=atar.AppealId
        WHERE ata.AppealStatus=2 AND (ata2.ArbResult=2 OR (ata2.ArbResult=3 AND ata2.OfflineResult=2))
              AND (ata.AppealType=1 OR ata.AppealType=3)
    )
    
    SELECT @AppealPublishNum=COUNT(1) FROM (
        SELECT am.RespTvaId,am.ArbTime FROM TradeFailPublish tfp 
        LEFT JOIN AppealManager AS am ON tfp.TstOrderID=am.TstOrderId
    ) AS PublishAppeal
    WHERE PublishAppeal.RespTvaId=@TvaId AND PublishAppeal.ArbTime BETWEEN @StartTime AND @EndTime
    
    RETURN @AppealPublishNum
END