非常慢的 ExecuteNonQuery(存储过程)与使用 SQL Server Management Studio 的快速执行 [英] Very slow ExecuteNonQuery (Stored Procedure) vs fast execution using SQL Server Management Studio

问题描述

尽管围绕这个话题有很多问题，但这些建议似乎都不起作用.我有几个应该每天运行的存储过程——其中一些存储过程非常简单，而另一些则比较棘手.但是，即使是最简单的过程在使用 SqlClient 从 C# 程序(控制台)调用时也会无限期地运行.此客户端在服务器上运行，当它实际运行时应该提升为 Windows 服务.

Although there are many questions on this topic going around, none of the suggestions seem to work. I've got a couple of stored procedures which should be run on a daily basis - some of these stored procedures are quite straight forward, others a bit more tricky. But even the simplest of procedures will run indefinitely when called from a C# program (console) using the SqlClient. This client is running on the server and should be promoted to a windows service when it's actually functioning.

到目前为止我尝试过的.

What I've tried so far.

1. 添加 ARITHABORT ON(或 OFF)作为连接初始化后的第一次执行.
2. 在存储过程中添加 ARITHABORT ON(或 OFF)作为第一个命令
3. 使用WITH RECOMPILE
4. 将 ARITHABORT 添加为全局配置.

(EXEC sys.sp_configure N'user options', N'64'走使用覆盖重新配置去)

存储过程(所有)没有输入参数，最简单的(我目前唯一使用的)是这样的:

The stored procedures (all of them) have no input parameters and the simplest (the only one I currently use) is this:

CREATE PROCEDURE [dbo].[_clean_messageLog]
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    set arithabort on;

    DELETE FROM MessageLog WHERE Moment < GETDATE() - 60;

    DELETE FROM MessageLog WHERE Moment < GETDATE() - 30 AND [Status] = 200;
END

没有要实际删除的消息，并且在 SSMS 中，存储过程在几毫秒内执行(如预期).然而，从 C# 控制台应用程序中它需要永远(字面意思).

There are no messages to be actually deleted and in SSMS the stored procedures executes (as expected) within milliseconds. From the C# Console Application however it takes forever (literally).

主要方法:

const int TIME_OUT = 900000; // 15 minutes
            timer.Stop();
            foreach (var command in commands.Where(command => !string.IsNullOrWhiteSpace(command)))
            {
                var start = DateTime.Now;
                WriteEvent(string.Format("Starting: {0}", command), EventLogEntryType.Information);
                using (var sql = new Lib.Data.SqlServerHelper(connectionString))
                {
                    sql.newCommand(command.Trim());
                    sql.execute(TIME_OUT); 
                }
                WriteEvent(string.Format("Done in {0} seconds", DateTime.Now.Subtract(start).TotalSeconds), EventLogEntryType.Information);
            }

有人有什么建议吗?

sqlHelper 只是一个基本的(非常简单的)包装器.但即使我把上面的代码改成这样:

The sqlHelper is just a basic (very simple) wrapper. But even when I change the above code to this:

foreach (var command in commands.Where(command => !string.IsNullOrWhiteSpace(command)))
            {
                var start = DateTime.Now;
                WriteEvent(string.Format("Starting: {0}", command), EventLogEntryType.Information);
                using (var sql = new SqlConnection(connectionString))
                {
                    sql.Open();
                    var sqlCommand = new SqlCommand(command.Trim(), sql) {CommandType = CommandType.StoredProcedure};
                    sqlCommand.ExecuteNonQuery();
                }
                WriteEvent(string.Format("Done in {0} seconds", DateTime.Now.Subtract(start).TotalSeconds), EventLogEntryType.Information);
            }

完全一样.

是否有一个选项可以安排这些存储过程在某个时间间隔或特定时间由 SQL Server 本身运行?

Is there an option I can schedule these stored procedures to be run by SQL Server itself on an interval or specific time?

有点，虽然我从来没有找到真正的 C# 解决方案来解决我使用 SQL Server 代理的问题.由于死锁问题，C# 进程被锁定 - 有时也会在作业中发生(不像控制台程序那么多)，但我们正在努力解决这个问题.

Kinda, although I've never found an actual C# solution to my problem using the SQL Server Agent did the trick. The C# processes were locked due to deadlock issues - which sometimes also occur on the jobs (not as many as the console program), but we're working on that.

推荐答案

有没有可以安排这些存储过程由其运行的选项

Is there an option I can schedule these stored procedures to be run by

SQL Server 本身是在某个时间间隔还是特定时间?

SQL Server itself on an interval or specific time?

是的，SQL Server 代理可以根据特定的时间或间隔运行作业.

Yes, SQL Server Agent can run jobs based on specific time or interval.

创建 SQL Server 作业

SSMS ->SQL Server 代理 ->右键单击 ->新工作 ->选择名称、数据库、代码和时间表

SSMS -> SQL Server Agent -> Right-Click -> New Job -> Select Name, Database, Code and Schedule

完成后，您可以单击Script 按钮并获取创建作业的脚本(如果需要).

When you finish you can click Script button and get script that create job (if needed).

您还可以使用 T-SQL 启动作业(例如从应用程序/另一个存储过程或触发器):

You can also start Job using T-SQL (for example from application/another stored procedure or trigger):

EXEC msdb.dbo.sp_start_job N'JobName';

这篇关于非常慢的 ExecuteNonQuery(存储过程)与使用 SQL Server Management Studio 的快速执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！