如何停止正在运行的查询？

I use RODBC to send queries to an SQL-Server. Sometimes they take too much time to run, so I need to cancel them.

我使用RODBC向SQL-Server发送查询。有时它们需要花费太多时间来运行，所以我需要取消它们。

Clicking the red "stop" button in RStudio yields this error message:

单击RStudio中的红色“停止”按钮会产生以下错误消息：

R is not responding to your request to interrupt processing so to stop the current operation you may need to terminate R entirely.

R没有响应您的中断处理请求，因此要停止当前操作，您可能需要完全终止R.

Terminating R will cause your R session to immediately abort. Active computations will be interrupted and unsaved source file changes and workspace objects will be discarded.

终止R将导致您的R会话立即中止。将中断活动计算，并且将丢弃未保存的源文件更改和工作空间对象。

Do you want to terminate R now?

你想现在终止R吗？

And if I click yes my session is indeed terminated. (note: using Rgui instead of RStudio doesn't make things better)

如果我单击是，我的会话确实已终止。（注意：使用Rgui代替RStudio不会让事情变得更好）

However:

然而：

when I use another software (named "Query ExPlus") to connect to this same SQL-Server, I have a similar stop button, and clicking it instantly interrupts the query, without any crash.

当我使用另一个软件（名为“Query ExPlus”）连接到同一个SQL-Server时，我有一个类似的停止按钮，然后单击它会立即中断查询，而不会发生任何崩溃。
when I connect to a PostgreSQL database using the RPostgres package I can also interrupt the query at any time.

当我使用RPostgres包连接到PostgreSQL数据库时，我也可以随时中断查询。

These two points lead me to think that there should be a way to solve my problem. What can I do?

这两点让我觉得应该有办法解决我的问题。我能做什么？

So far my workaround is:

到目前为止，我的解决方法是：

library(RODBC)
library(R.utils)

withTimeout(mydf <- sqlQuery(myconnection, myquery), timeout=120)

Note: I don't have permission to kill queries from the database side.

注意：我没有权限从数据库端终止查询。

4 个解决方案

#1

I've just stumbled upon the odbc package. It allows to interrupt a query at any time.

我偶然发现了odbc包。它允许随时中断查询。

Basic usage goes like this:

基本用法如下：

library(DBI)

myconnection <- dbConnect(odbc::odbc(),
                          driver = "SQL Server",
                          server = "my_server_IP_address",
                          database = "my_DB_name",
                          uid = "my_user_id",
                          pwd = "my_password")

dbGetQuery(myconnection, myquery)

I don't have a deep understanding of what happens behind the scenes, but for what I've seen so far in my personal use this package has other advantages over RODBC:

我对幕后发生的事情并不十分了解，但就我迄今为止在个人使用中看到的情况而言，这个套餐还有其他优于RODBC的优势：

really faster
真的更快
get the column types from the DB instead of guessing them (see here)
从数据库中获取列类型而不是猜测它们（请参阅此处）
no stringsAsFactors and as.is arguments necessary
没有stringsAsFactors和as.is参数是必要的

#2

Most SQL Server users use SQL Server Management Studio (which is free and can be downloaded from Microsoft) to connect to SQL Server or execute commands from the command line via a tool called SQLCMD.

大多数SQL Server用户使用SQL Server Management Studio（可免费下载并可从Microsoft下载）连接到SQL Server或通过名为SQLCMD的工具从命令行执行命令。

If you can determine the session id that the SQL Command is being run in you can kill the session which would stop any executing command(s). SQL Server will still need time (could be a 'long' time) to rollback any changes made during the execution of the command.

如果您可以确定正在运行SQL命令的会话ID，则可以终止会阻止任何正在执行的命令的会话。 SQL Server仍然需要时间（可能是'很长时间）来回滚在执行命令期间所做的任何更改。

Terminating a session (depending on the software) can take a while to communicate to SQL Server that the session has been terminated. When I connected to DB2 from SQL Server using linked servers DB2 would buffer the terminate command and it would frequently take up to an hour for DB2 to realize the session had been terminated.

终止会话（取决于软件）可能需要一段时间才能与SQL Server通信会话已终止。当我使用链接服务器从SQL Server连接到DB2时，DB2将缓冲terminate命令，并且DB2通常需要一个小时来实现会话已终止。

To determine what the session you are running in you can try:

要确定您正在运行的会话，您可以尝试：

select @@spid;

once you have the spid (lets say 86) you can then issue (depending on if you have permission to do so)

一旦你有了spid（比方说86）你可以发出（取决于你是否有权这样做）

kill 86;

but as Microsoft notes: Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete, particularly when it involves rolling back a long transaction.

但正如Microsoft所说：终止基于会话ID或工作单元（UOW）的用户进程。如果指定的会话ID或UOW需要大量撤消工作，则KILL语句可能需要一些时间才能完成，尤其是涉及回滚长事务时。

#3

-2

Try to close your "tab query" on SQL Server Management Studio Then it will appear pop-up,

尝试关闭SQL Server Management Studio上的“选项卡查询”然后它会弹出，

This Query is currently executing. Do you want to cancel this query ?

此查询当前正在执行。要取消此查询吗？

Cancel anyway, choose "yes".

无论如何取消，选择“是”。

#4

-2

try to set your connection prior to query:

尝试在查询之前设置连接：

sql = odbcConnect('Database name')

sql = odbcConnect（'数据库名称'）

Then use same line to run your query:

然后使用相同的行来运行您的查询：

mydf <- sqlQuery(sql, " myquery ")

mydf < - sqlQuery（sql，“myquery”）

Note: The running time is dependant on both database and R server but setting up the connection this way should resolve termination problem.

注意：运行时间取决于数据库和R服务器，但以这种方式设置连接应解决终止问题。

#1