存储过程优化问题
--存储过程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 --输出总数据数 )ASBEGIN --===========================查询条件拼接==================================== 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' ) ENDEND--自定义函数，取一个复杂的GetAppealPublish：ALTER FUNCTION [dbo].[GetAppealPublish]( @TvaId INT, @StartTime DATETIME, @EndTime DATETIME )RETURNS INTASBEGIN 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 @AppealPublishNumEND
SEELCT *FROM V_VendorManager vvm LEFT JOIN (SELECT * FROM dbo.GetVendorTradeData('','')) AS TradeOrder ON vvm.TvaID=TradeOrder.BuyerTv
[解决办法]
楼主的整个脚本速度，被函数拖了很多速度，
数了数至少用10多个函数，
当被调用的时候，
每个函数都要来回传递--返回值
函数 尽量不要在这里面使用
[解决办法]
函数应该是祸根，但是索引及where子句也是有改进的地方。先说函数，如果你的2000多数据都需要用到函数，就要调用2000多次函数，调一次花多少时间你算过没？何况你的函数有可能不止要用2000多次。
对于索引和where子句，这个比较大的课题，很难和你说清楚，而且我觉得你的脚本过于复杂。
你要先确认一点：你是用“面向过程”的思想来写程序还是“面向集合”的思想来写程序？
[解决办法]
分页函数？
都已经使用row_number()了，为什么还像只能使用top n一样拼凑sql呢？