2023年7月13日发(作者：)

使⽤mssql2008新特性（存储过程参数类型使⽤⽤户⾃定义表来实现批量DML更新多表）解。。。使⽤MSSQL2008有⼀段时间了，因为平常参与项⽬开发⼀般都使⽤NHbernate、Entity Framework等这些ORM⼯具来做数据的存储，所以并没有花时间去学习mssql2008，还是⽤mssql2000那会⼉积累的经验来使MSSQL2008。当然也是粗略的了解到MSSQL2008也有表分区啊、⾃定义数据类型等。代码重构前⼀次操作需要数分钟，甚⾄半⼩时的操作重构后只需数秒⾄⼗⼏秒，前端经常卡死Ajax调⽤后台⽅法，后台不返回执⾏结果，后台出现超时错误等。找到代码重构需要解决的问题：1、原有代码实现⽅式是Entity Framework和拼接SQL混合的⽅式、因为需要同时更新多个表，Entity Framework实现起来是异常的吃⼒啊，前同事喜欢拼接⼤段的TSQL脚本在代码⾥实现，我的直觉是⾃⼰熟悉数据库，第⼀个想的就是使⽤存储过程来优化。见下图写法:

看到的问题就是很混乱，没有事务控制，现在系统就是经常出现异常时有些数据提交了有些没提交，部分进了第⼀个表，第⼆个表⾥状态没更新，你懂的，如此如此那般那般，我得经常维护这个项⽬，⼀直都有事情做。呵呵...2、解决前台页⾯传递过来的JSON⾥处理多条记录，需要使⽤循环并嵌套循环的问题，提⾼效率。解决的办法：昨天看到知识库⾥的博⽂：“”，最后部分的使⽤MSSQL2008表参数来达到批量⾼效插⼊记录，转念⼀箱如果使⽤表参数⼀次传⼊需要循环多次传递的参数不是提⾼了速度吗？测试下来果真OK，效率提升了不⽌⼀点点啊，数⼗倍的提升！C#调存储过程： #region 准备DataTable DataTable dt = new DataTable(); (new DataColumn("RequestKeyID", typeof(Guid))); (new DataColumn("EntityCode", typeof(string))); (new DataColumn("Thirdparty", typeof(string))); (new DataColumn("PayStatus", typeof(int))); (new DataColumn("LoginUserName", typeof(string))); foreach (JavaScriptObject r in rows) { year = (r["Year"].ToString()); month = (r["Month"].ToString()); var row = (); row["RequestKeyID"] = d(); row["EntityCode"] = r["Entity"].ToString(); row["Thirdparty"] = r["ThirdPart"].ToString(); row["PayStatus"] = status; row["LoginUserName"] = login; (row); } #endregion

using (WHSEntities db = new WHSEntities()) { cuteProc(tionStrings["WHSEntities"].ConnectionString, "Proc_StreamBatchPay", new ameter[] { new ameter("@P_BatchRequestDataTable",ured){Value=dt}, new ameter("@PayYear",){Value=year}, new ameter("@PayMonth",){Value=month}, new ameter("@PayStatus",){Value=status} }); }存储过程：ALTER proc [dbo].[Proc_StreamBatchPay]

@P_BatchRequestDataTable DT_StreamBatchRequest READONLY , --临时表多个⽀付请求 @PayYear int , --⽀付的财务年 @PayMonth int, --⽀付的财务⽉ @PayStatus int --是⽀付1 还是拒绝0

/* ***XXXXX过程 Date： 2013-3-14 原因： 1、批量插⼊和更新，加快页⾯反映速度，原来代码是EF和SQL混搭没有事务处理和页⾯反馈慢 2、使⽤事务来保证数据⼀致性 3、操作⽇志痕迹的保留*/

ASDECLARE @EntityCode nvarchar(20)

DECLARE @Thirdparty nvarchar(40)DECLARE @LoginUserName nvarchar(60)

DECLARE @CountOfPaymentID intDECLARE @SerialID uniqueidentifierDECLARE @PaymentID uniqueidentifierBEGIN SET XACT_ABORT ON; SET NOCOUNT ON; BEGIN TRY --开启事务 BEGIN TRANSACTION; INSERT INTO _WH_PaymentDetail

SELECT NEWID(),@PayStatus,tID,GETDATE(),l,By,GETDATE(),art,ry

FROM _WH_Payment p

LEFT JOIN @P_BatchRequestDataTable i ON = Code and art = arty WHERE = @PayYear and = @PayMonth and ry = '3PLStream';

--⽀付

IF @PayStatus = 1 BEGIN UPDATE _WH_Payment SET UpdateBy= serName,UpdateDate=GETDATE(),Actual=Accrual FROM @P_BatchRequestDataTable p WHERE Entity = Code AND ThirdPart = arty

AND Category = '3PLStream' AND Year = @PayYear AND Month = @PayMonth;

SELECT ID,tID,r,th,p1.[Year],p1.[Month],,art into #PayMent1Temp FROM _WH_Payment1 p1

WHERE = @PayYear AND = @PayMonth AND +'-'+art IN (SELECT Code+'-'+arty FROM @P_BatchRequestDataTable E) AND

ry = '3PLStream';

--游标处理PayMent1⾥对应的记录 DECLARE CUR_PAYMENT1 CURSOR FOR SELECT SerialID,PaymentID FROM #PayMent1Temp

OPEN CUR_PAYMENT1; FETCH NEXT FROM CUR_PAYMENT1 INTO @SerialID,@PaymentID; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CountOfPaymentID = COUNT(*) FROM FMS_WH_Payment1

WHERE PaymentID=@PaymentID; --没跨⽉，当⽉发⽣当⽉⽀付 IF @CountOfPaymentID = 1

BEGIN UPDATE FMS_WH_Payment1 SET Actual=Accrual

WHERE SerialID = @SerialID; END --跨⽉，当⽉发⽣下⽉⽀付 IF @CountOfPaymentID = 2

BEGIN --更新发⽣⽉那笔payment1记录实际付款⾦额 UPDATE FMS_WH_Payment1 SET Actual=Accrual WHERE SerialID = @SerialID; --删除结转的那笔⽤于显⽰的payment1记录 DELETE FMS_WH_Payment1 WHERE PaymentID=@PaymentID AND OldMonth <> [Month]; END

--跨⽉，当⽉发⽣下下⽉⽀付 原则上不允许流量跨2⽉ IF @CountOfPaymentID = 3

BEGIN --更新发⽣⽉那笔payment1记录实际付款⾦额 UPDATE FMS_WH_Payment1 SET Actual=Accrual WHERE PaymentID=@PaymentID AND OldMonth = [Month]; --删除结转的那2笔⽤于显⽰的payment1记录 DELETE FMS_WH_Payment1 WHERE PaymentID=@PaymentID AND OldMonth <> [Month]; END

FETCH NEXT FROM CUR_PAYMENT1 INTO @SerialID,@PaymentID

END CLOSE CUR_PAYMENT1; DEALLOCATE CUR_PAYMENT1; --操作成功，写⽇志 INSERT INTO fms_system_log(dtDate,sThread,sLevel,sLogger,sMessage,sException) SELECT GETDATE(),'DBLOG','INFO','dbProc','02|执⾏了'+CAST(@PayYear AS VARCHAR)+''+CAST(@PayMonth AS VARCHAR)+'批量流量费⽤⽀付操作。','';

END --拒绝 IF @PayStatus = 0 BEGIN UPDATE _WH_Payment SET UpdateBy= serName,UpdateDate=GETDATE(),[status]=0 FROM @P_BatchRequestDataTable p WHERE Entity = Code AND ThirdPart = arty

AND Category = '3PLStream' AND Year = @PayYear AND Month = @PayMonth;

--写⽇志 INSERT INTO fms_system_log(dtDate,sThread,sLevel,sLogger,sMessage,sException) SELECT GETDATE(),'DBLOG','INFO','dbProc','02|执⾏了'+CAST(@PayYear AS VARCHAR)+''+CAST(@PayMonth AS VARCHAR)+'批量流量费⽤拒绝⽀付的操作。',''; END --提交事务 COMMIT TRANSACTION; END TRY BEGIN CATCH

IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRANSACTION; --异常⽇志记录 INSERT INTO fms_system_log(dtDate,sThread,sLevel,sLogger, sMessage,sException) SELECT GETDATE(),'DBLOG','ERROR','dbProc', '异常代码：'+CAST(ERROR_NUMBER() AS VARCHAR)+'异常消息：'+ERROR_MESSAGE(),CAST(ERROR_NUMBER() AS VARCHAR) END;

IF (XACT_STATE()) = 1 BEGIN COMMIT TRANSACTION;

END; END CATCH;END这是特定业务写的存储过程没什么⽤处，也不需要看懂，就是看看表参数是怎么⽤的，怎么使⽤事务来优化控制数据的⼀致性。看存储过程⾥的注释即可。别告诉哥EF怎么调整性能好，哥没那时间去学习，多年数据库编程经验哥⾃信在这个时刻对于我是最可靠的。

OK。搞定收⼯，主要是留下思路给⾃⼰以后回顾的，万⼀能帮到你也解决了类似问题，那我就更开⼼了！请不要说没有截图对⽐效率提升，⽆图⽆真相；是真不⽅便哦。没效果我就不会分享了。