现在，用于循环遍历1700万条记录以删除重复项的查询已经运行了大约16个小时，我想知道查询是否立即停止，如果它将完成delete语句，或者在运行此语句时是否已将其删除询问？确实，如果我停止了它，它是否完成删除或者回滚？

我发现当我做一个

select count(*) from myTable

它返回的行(在执行此查询时)比开始的行数少大约5. 显然，服务器资源非常匮乏，这是否意味着此过程需要16个小时才能找到5个重复项(实际上有数千个重复项)，并且可能要运行数天？

该查询对2000行测试数据花费了6秒，并且在该数据集上效果很好，因此我认为整个数据集将花费15个小时。

有任何想法吗？

下面是查询：

--Declare the looping variable
DECLARE @LoopVar char(10)

    DECLARE
     --Set private variables that will be used throughout
      @long DECIMAL,
      @lat DECIMAL,
      @phoneNumber char(10),
      @businessname varchar(64),
      @winner char(10)

    SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable)

    WHILE @LoopVar is not null
    BEGIN

      --initialize the private variables (essentially this is a .ctor)
      SELECT 
        @long = null,
        @lat = null,
        @businessname = null,
        @phoneNumber = null,
        @winner = null

      -- load data from the row declared when setting @LoopVar  
      SELECT
        @long = longitude,
        @lat = latitude,
        @businessname = BusinessName,
        @phoneNumber = Phone
      FROM MyTable
      WHERE RecordID = @LoopVar

      --find the winning row with that data. The winning row means 
      SELECT top 1 @Winner = RecordID
      FROM MyTable
      WHERE @long = longitude
        AND @lat = latitude
        AND @businessname = BusinessName
        AND @phoneNumber = Phone
      ORDER BY
        CASE WHEN webAddress is not null THEN 1 ELSE 2 END,
        CASE WHEN caption1 is not null THEN 1 ELSE 2 END,
        CASE WHEN caption2 is not null THEN 1 ELSE 2 END,
        RecordID

      --delete any losers.
      DELETE FROM MyTable
      WHERE @long = longitude
        AND @lat = latitude
        AND @businessname = BusinessName
        AND @phoneNumber = Phone
        AND @winner != RecordID

      -- prep the next loop value to go ahead and perform the next duplicate query.
      SET @LoopVar = (SELECT MIN(RecordID) 
    FROM MyTable
    WHERE @LoopVar < RecordID)
    END

解决方案

我很确定那是一个否定。否则交易的重点是什么？

查询未包装在事务中，因此不会回滚单个delete语句已进行的更改。

我使用以下查询在自己的SQL Server上专门对此进行了测试，即使我取消了查询，ApplicationLog表也为空：

declare @count int
select @count = 5
WHILE @count > 0
BEGIN
  print @count
  delete from applicationlog;
  waitfor time '20:00';
  select @count = @count -1
END

但是，查询可能需要花费几天或者几周的时间，而要花费的时间要长于15个小时。我们估计每6秒钟可以处理2000条记录的做法是错误的，因为while循环中的每次迭代花费的时间远远超过了2000万行，因此需要花费1700万行。因此，除非查询对2000行的查询花费的时间明显少于一秒钟，否则所有这1700万行查询都将花费几天的时间。

我们应该问一个新的问题，关于如何有效删除重复的行。

否，如果我们停止执行查询，则sql server将不会回滚已经执行的删除操作。 oracle需要明确提交操作查询，否则数据将被回滚，而不是mssql。

使用sql server，除非我们在事务上下文中专门运行并且回滚该事务，否则它将不会回滚，或者在未提交事务的情况下关闭连接。但我在我们上面的查询中没有看到交易上下文。

我们还可以尝试重新组织查询，以使删除操作更有效率，但是从根本上讲，如果包装盒规格不理想，那么我们可能会被迫等待。

今后，我们应该在表上创建一个唯一索引，以免自己再次经历该过程。

作为一个循环，即使使用适当的索引，查询也很难很好地扩展。根据上一个问题中的建议，查询应重写为单个语句。

如果我们没有在事务中显式运行它，它将仅回滚执行语句。

如果我们未对事务进行任何明确的操作，则连接将处于自动提交事务模式。在这种模式下，每个SQL语句都被视为事务。

问题是这是否意味着单个SQL语句是事务，因此在我们执行过程中就被提交，还是外部WHILE循环是否算作事务。

在MSDN上的WHILE构造的描述中似乎没有对此进行任何讨论。但是，由于WHILE语句无法直接修改数据库，因此它不会启动自动提交事务似乎是合乎逻辑的。

我继承了一个系统，该系统的逻辑类似于我们在SQL中实现的逻辑。在我们的例子中，我们试图使用具有相似名称/地址等的模糊匹配将行链接在一起，并且逻辑完全是在SQL中完成的。在我继承它的时候，表中大约有300,000行，根据计时，我们计算出要匹配所有这些行需要一年的时间。

为了了解我可以在SQL之外执行此操作有多快的实验，我编写了一个程序来将db表转储到平面文件中，将平面文件读入C ++程序，构建自己的索引，然后在其中执行模糊逻辑然后将平面文件重新导入数据库。在C ++应用程序中花了30年时间花了SQL一年的时间。

因此，我的建议是，甚至不要尝试使用SQL进行操作。导出，处理，重新导入。

我认为如果使用游标使用单遍算法将其重写，则查询效率会更高。我们将按经度，纬度，BusinessName和@phoneNumber来排序光标表。我们一次只能浏览一行。如果一行的经度，纬度，公司名称和电话号码与上一行相同，则将其删除。

到现在为止已执行的删除将不会回滚。

作为有问题的代码的原始作者，并发布了性能将取决于索引的警告，我将提出以下建议以加快此过程。

RecordId最好是PRIMARY KEY。我不是指身份，是指主键。使用sp_help确认

在评估此查询时应使用一些索引。找出这四列中哪一列的重复次数最少，并为其编制索引。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

SELECT *
FROM MyTable
WHERE @long = longitude
  AND @lat = latitude
  AND @businessname = BusinessName
  AND @phoneNumber = Phone

在添加此索引之前和之后，请检查查询计划以查看是否已添加索引扫描。

隐式交易

如果未设置"隐式事务"，则循环中的每个迭代都会提交更改。

任何SQL Server都可以设置为"隐式事务"。这是一个数据库设置(默认情况下为OFF)。我们还可以在Management Studio内部特定查询的属性中(在查询窗格>选项中右键单击)，客户端默认设置或者SET语句中的隐式事务。

SET IMPLICIT_TRANSACTIONS ON;

不管是哪种情况，无论查询执行是否中断，我们仍然都需要执行一个显式的COMMIT / ROLLBACK。

隐式交易参考：

http://msdn.microsoft.com/en-us/library/ms188317.aspx

http://msdn.microsoft.com/en-us/library/ms190230.aspx

我认为我们需要认真考虑方法。
我们需要开始考虑集合(尽管为了提高性能，我们可能需要批处理，但不需要对着1700万条记录表逐行进行处理。)

首先，所有记录都有重复吗？我怀疑不是，所以我们要做的第一件事就是将处理限制为仅包含重复的记录。由于这是一个很大的表，因此我们可能需要根据时间进行批量删除，具体取决于正在进行的其他处理，因此首先将要处理的记录拉入它们自己的表中，然后对其进行索引。如果我们将能够同时执行所有这些操作而不用停止它，则还可以使用一个临时表，否则，请在数据库中创建一个表并将其删除。

像这样(注意，我没有编写create index语句，我认为我们可以自己检查一下)：

SELECT min(m.RecordID), m.longitude, m.latitude, m.businessname, m.phone  
     into  #RecordsToKeep    
FROM MyTable   m
join 
(select longitude, latitude, businessname, phone
from MyTable
group by longitude, latitude, businessname, phone
having count(*) >1) a 
on a.longitude = m.longitude and a.latitude = m.latitude and
a.businessname = b.businessname and a.phone = b.phone 
group by  m.longitude, m.latitude, m.businessname, m.phone   
ORDER BY CASE WHEN m.webAddress is not null THEN 1 ELSE 2 END,        
    CASE WHEN m.caption1 is not null THEN 1 ELSE 2 END,        
    CASE WHEN m.caption2 is not null THEN 1 ELSE 2 END

while (select count(*) from #RecordsToKeep) > 0
begin
select top 1000 * 
into #Batch
from #RecordsToKeep

Delete m
from mytable m
join #Batch b 
        on b.longitude = m.longitude and b.latitude = m.latitude and
        b.businessname = b.businessname and b.phone = b.phone 
where r.recordid <> b.recordID

Delete r
from  #RecordsToKeep r
join #Batch b on r.recordid = b.recordid

end

Delete m
from mytable m
join #RecordsToKeep r 
        on r.longitude = m.longitude and r.latitude = m.latitude and
        r.businessname = b.businessname and r.phone = b.phone 
where r.recordid <> m.recordID