时间戳

首页 > 解决方案 > Why does MSSQL procedure act differently called from Management Studio and from PHP?

php - Why does MSSQL procedure act differently called from Management Studio and from PHP?

问题描述

I have a main procedure, which calls sub-procedures. The main procedure has no param, it's only calls the sub-procedures. (the sub-procedures are the same with different params) The sub-procedures makes inserts and updates from a view. If I run from Management Studio everything seems ok, after a 3 minutes I have about 1000 message says '(1 row(s) affected)'. But when I call it from PHP, then it runs for about 1 seconds with no error, and only the first sub-procedure has an effect. If I remove the first procedure, then only second procedure has an effect. The PHP use the same user as Management Studio.

标签： phpsql-servertsqlstored-proceduresmoodle

解决方案

如果我从 Management Studio 运行，一切似乎都正常，3 分钟后我有大约 1000 条消息说“（受影响的 1 行）”。但是当我从 PHP 中调用它时，它会运行大约 1 秒而没有错误，并且只有第一个子程序有效果。

您在 SSMS 中看到的“（受影响的 1 行）”消息是对发送给客户端的消息的响应。如果客户端不使用这些消息，它可以阻止服务器端执行。

要尝试在主过程开始时添加set nocount on，并在 SSMS 中验证行计数消息已消失：

SET NOCOUNT ON 防止为存储过程中的每个语句向客户端发送 DONE_IN_PROC 消息。对于包含多个不返回太多实际数据的语句的存储过程，或者对于包含 Transact-SQL 循环的过程，将 SET NOCOUNT 设置为 ON 可以显着提高性能，因为网络流量大大减少。

推荐阅读