SQL重复删除查询数百万行的性能 [英] SQL Duplicate Delete Query over Millions of Rows for Performance

问题描述

这是一场冒险。我从循环的重复查询开始，位于我以前的问题中，但每个循环都将超过所有 1700万条记录，意味着需要几周（只需运行 *从MyTable *中选择count * 使用MSSQL 2005将我的服务器4:30分钟）。我从这个网站和这个帖子中发现了一些信息。

并已到达以下查询。问题是，这是正确类型的查询运行在任何类型的表现的1700万条记录？如果不是，那是什么？

SQL QUERY：

  DELETE tl_acxiomimport.dbo.tblacxiomlistings 
 WHERE RecordID in 
（SELECT RecordID 
 FROM tl_acxiomimport.dbo.tblacxiomlistings 
 EXCEPT 
 SELECT RecordID 
 FROM（
 SELECT RecordID，Rank（）over（Partition BY BusinessName，纬度，经度，电话ORDER BY webaddress DESC，caption1 DESC，caption2 DESC）AS排名
 FROM tl_acxiomimport.dbo.tblacxiomlistings 
）al WHERE Rank = 1）
  

解决方案

查看QueryPlan会有帮助。 p>

这是否可行？

  SELECT m。* 
 into #temp 
 FROM tl_acxiomimport.dbo.tblacxiomlistings m 
 inner join（SELECT RecordID，
 Rank（）over（Partition BY BusinessName，
 latitude，
 longitude， 
电话
 ORDER BY webaddress DESC，
 caption1 DESC，
 caption2 DESC）AS排名
 FROM tl_acxiomimport.dbo.tblacxiomlistings 
）al（al。 RecordID = m.RecordID和al.Rank = 1）
 
 truncate table tl_acxiomimport.dbo.tblacxiomlistings 
 
 insert into tl_acxiomimport.dbo.tblacxiomlistings 
 select * from＃ temp 
  

This has been an adventure. I started with the looping duplicate query located in my previous question, but each loop would go over all 17 million records, meaning it would take weeks (just running *select count * from MyTable* takes my server 4:30 minutes using MSSQL 2005). I gleamed information from this site and at this post.

And have arrived at the query below. The question is, is this the correct type of query to run on 17 million records for any type of performance? If it isn't, what is?

SQL QUERY:

DELETE tl_acxiomimport.dbo.tblacxiomlistings
WHERE RecordID in 
(SELECT RecordID
    FROM tl_acxiomimport.dbo.tblacxiomlistings
    EXCEPT
    SELECT RecordID
    FROM (
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude,           Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
    FROM tl_acxiomimport.dbo.tblacxiomlistings
    ) al WHERE Rank = 1)

解决方案

Seeing the QueryPlan would help.

Is this feasible?

SELECT m.*
into #temp
FROM tl_acxiomimport.dbo.tblacxiomlistings m 
inner join (SELECT RecordID, 
                   Rank() over (Partition BY BusinessName, 
                                             latitude,  
                                             longitude,            
                                             Phone  
                                ORDER BY webaddress DESC,  
                                         caption1 DESC,  
                                         caption2 DESC ) AS Rank
              FROM tl_acxiomimport.dbo.tblacxiomlistings
           ) al on (al.RecordID = m.RecordID and al.Rank = 1)

truncate table tl_acxiomimport.dbo.tblacxiomlistings

insert into tl_acxiomimport.dbo.tblacxiomlistings
     select * from #temp

这篇关于SQL重复删除查询数百万行的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！