存储过程优化问题
SQL code--存储过程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 @[email protected]+' AND vvm.BigAreaId='+CAST(@BigArea AS VARCHAR); IF @Province<>0 SET @[email protected]+' AND vvm.ProvinceId='+CAST(@Province AS VARCHAR); IF @City<>0 SET @[email protected]+' AND vvm.CityID='+CAST(@City AS VARCHAR); IF @VendorClass<>0 SET @[email protected]+' AND vvm.VendorClass='+CAST(@VendorClass AS VARCHAR); IF @SuperVenderTvaId<>0 SET @[email protected]+' AND vvm.SuperVendorTvaID='+CAST(@SuperVenderTvaId AS VARCHAR); IF @VendorFullName<>'' SET @[email protected]+' AND vvm.VendorFullName like [email protected]+'%'''; IF @VendorTvaId<>0 SET @[email protected]+' AND vvm.TvaID='+CAST(@VendorTvaId AS VARCHAR); IF @VendorStatus<>-1 SET @[email protected]+' AND vvm.Status='+CAST(@VendorStatus AS VARCHAR); IF @AccountType<>0 SET @[email protected]+' 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 @[email protected]+' @RecordCount=COUNT(1) [email protected][email protected] EXEC SP_EXECUTESQL @RecordCountSql,[email protected] INT OUTPUT',@RecordCount OUTPUT SET @[email protected]+ @FieldSql+ @BidPublishNumSql+ @BuyerPublishNumSql+ @AttentionPublishSql+ @TradeSuccessPublishSql+ @AppealedPublishSql+ @FromSql+ @JoinSql+ @queryCondition --实现数据的分页 DECLARE @StartRow int,@EndRow INT IF @PageIndex<>-1 BEGIN SET @StartRow=(@PageIndex-1)[email protected]+1--起始页 SET @[email protected][email protected] EXEC(' SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY [email protected]+') 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 [email protected]+' AND [email protected] ) END ELSE BEGIN EXEC(' SELECT *, ROW_NUMBER() OVER (ORDER BY [email protected]+') 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 [email protected] 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 [email protected] AND PublishAppeal.ArbTime BETWEEN @StartTime AND @EndTime RETURN @AppealPublishNumEND