如果特定的 sql 语句花费的时间超过指定的秒数,我希望能够中止该语句。 我怎样才能做到这一点?
例如,我想做类似的事情:
SET NextStatementTimeOutSeconds = 60
SELECT * FROM MyTable
IF @@LastStatementTimedOut = 1
PRINT 'Statement timed out after 60 seconds'
ELSE
PRINT 'Statement completed in less than 60 seconds'
请注意,我编写了 NextStatementTimeOutSeconds
和 @@LastStatementTimedOut
来说明我想要做什么。
感谢您的建议。 如果可能的话,我正在考虑在单个 sql 脚本/批处理中使用这样的超时,就像上面的那样。
背景:我们有几个存储过程,我希望它们的速度快于 X 秒。 所以我会依次称呼他们; 如果需要超过 X 秒,我就会中止并打印一条语句,如“sproc 13 花费太长时间”。 数据各不相同,并且我正在尝试不同的索引更改,因此我想在此类更改后对所有存储过程进行时间测试,以确保所有存储过程都低于 X 秒。
I would like to be able to abort a specific sql statement if it takes longer than a specified number of seconds. How can I do that?
For example, I would like to do something like:
SET NextStatementTimeOutSeconds = 60
SELECT * FROM MyTable
IF @@LastStatementTimedOut = 1
PRINT 'Statement timed out after 60 seconds'
ELSE
PRINT 'Statement completed in less than 60 seconds'
Please note that I made up NextStatementTimeOutSeconds
and @@LastStatementTimedOut
in order to illustrate what I would like to do.
Thanks for the suggestions. I'm thinking about using such a timeout inside a single sql script/batch like the one above, if possible.
Background: We have several stored procedures that I'd like to be faster than X seconds. So I'd call them one after each other; and if one takes more than X seconds, I'd just abort and print a statement like 'sproc 13 takes too long'. The data varies and I'm trying different index changes, so I'd like to time-test all sprocs after such changes in order to make sure that all of them are below X seconds.
发布评论
评论(4)
如果您使用的是 SQL Server,则可以将链接服务器添加到将运行查询的数据库中。 您可以设置链接服务器的超时,如下所示:
然后您可以运行如下查询:
您可以使用 TRY...CATCH 块捕获发生超时时的情况:
“查询超时已过期”的 @@error 将为 7320。
If you're using SQL Server, you can add a linked server to the database where the query will run. You can set the timeout for the linked server like:
Then you can run the query like:
You can use a TRY...CATCH block to capture the timeout when it occurs:
The @@error will be 7320 for "Query timeout expired".
一种可能性是您可以监视所有活动连接并终止任何在超时期间处于活动状态的连接。
不过我以前从未做过类似的事情。
One possibility could be that you could monitor all active connections and kill any that are active for your timeout.
I've never done anything like that before though.
通常,超时是在发出 SQL 语句的命令上设置的。 例如,如果是在 C# 中,那么您设置 IDbCommand.CommandTimeout 属性,如果语句尚未完成,它将在超时到期后抛出 SQLException。 这将回滚所有事务。
在 SQL Server 上的计划任务中,我希望当您配置作业时,向上/向下控件中会有一个值,允许您更改作业的超时。 我认为 SQL Server 中的术语是“执行超时”,但恐怕我不知道如何设置它。
重点是 - 查看运行该语句的事物,而不是实际的语句本身。
Typically the timeout is set on the command that issues the SQL statement. If that's in C# for example, then you set the IDbCommand.CommandTimeout property and it'll throw a SQLException after the timeout expires if the statement hasn't completed. This will then rollback any transactions.
In scheduled tasks on SQL Server, I'd expect that when you configure the job, there will be a value in an up/down control somewhere that allows you to change the timeout of the job. I think the terminology in SQL Server is a "Execution Timeout" but I don't know how to set it I'm afraid.
The point is - look at the thing running the statement, rather than the actual statement itself.
您可以使用查询调控器来限制查询运行的时间。 缺点是,它是一个全局选项,会影响针对特定数据库启动的所有查询。
You can use the query governor to limit the time a query can run. Downside is, it is a global option affecting all queries launched against a specific database.