SQL归档存储过程的最佳实践

问题描述：

我有一个非常大的数据库（〜100Gb），主要由两个我想减小大小的表（两者都有大约5000万条记录）组成。我有一个存档数据库设置在同一台服务器上，使用这两个表，使用相同的模式。我试图确定从实时数据库中删除行并将它们插入到存档数据库中的最佳概念方式。在伪代码，这是我现在在做什么：SQL归档存储过程的最佳实践

Declare @NextIDs Table(UniqueID) 
Declare @twoYearsAgo = two years from today's date 

Insert into @NextIDs 
    SELECT top 100 from myLargeTable Where myLargeTable.actionDate < twoYearsAgo 

Insert into myArchiveTable 
<fields> 
SELECT <fields> 
FROM myLargeTable INNER JOIN @NextIDs on myLargeTable.UniqueID = @NextIDs.UniqueID 

DELETE MyLargeTable 
FROM MyLargeTable INNER JOIN @NextIDs on myLargeTable.UniqueID = @NextIDs.UniqueID

眼下这需要一个缓慢的惊人，7分完成1000条记录。我已经测试了删除和插入，两者都需要约。 3.5分钟才能完成，所以它不一定比另一个效率低得多。任何人都可以在这里指出一些优化的想法吗？

谢谢！

这是SQL Server 2000中

编辑：在大桌子上有ActionDate场聚集索引。还有两个其他索引，但都不在任何查询中引用。存档表没有索引。在我的测试服务器上，这是唯一对SQL Server有影响的查询，所以它应该有足够的处理能力。

代码（这确实在1000个记录批循环一次）：

DECLARE @NextIDs TABLE(UniqueID int primary key) 
DECLARE @TwoYearsAgo datetime 
SELECT @TwoYearsAgo = DATEADD(d, (-2 * 365), GetDate()) 

WHILE EXISTS(SELECT TOP 1 UserName FROM [ISAdminDB].[dbo].[UserUnitAudit] WHERE [ActionDateTime] < @TwoYearsAgo) 
BEGIN 

BEGIN TRAN 

--get all records to be archived 
INSERT INTO @NextIDs(UniqueID) 
     SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[UserUnitAudit] WHERE [UserUnitAudit].[ActionDateTime] < @TwoYearsAgo 

--insert into archive table 
INSERT INTO [ISArchive].[dbo].[userunitaudit] 
(<Fields>) 
SELECT <Fields> 
FROM [ISAdminDB].[dbo].[UserUnitAudit] AS a 
     INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID 

--remove from Admin DB 
DELETE [ISAdminDB].[dbo].[UserUnitAudit] 
FROM [ISAdminDB].[dbo].[UserUnitAudit] AS a 
INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID 

DELETE FROM @NextIDs 

COMMIT 

END

你在主键字段上有聚簇索引吗？ – feihtthief 2009-12-09 20:18:19

NextID是表变量还是临时表？如果它是一个表变量，请尝试使用临时表。我在SQL Server 2000中有表变量的性能问题;虽然不是这样糟糕。 – 2009-12-09 20:23:06

NextID的当前声明为表变量。我将尝试使用临时表。 – Kevin 2009-12-09 20:25:09

答

的INSERT和DELETE语句上

[ISAdminDB].[dbo].[UserUnitAudit].UniqueID

加入如果有没有这方面的指标，并表示你没有，你做两个表扫描。这可能是速度慢的根源，SQL Server表扫描将整个表读入临时表，在临时表中搜索匹配的行，然后删除临时表。

我认为你需要在UniqueID上添加索引。维护它的性能要比表扫描小得多。您可以在完成存档后删除它。

这实际上导致我的解决方案。我没有跟踪需要通过没有索引的UniqueID移动的行，而是简单地使用了插入和删除中的WHERE [ActionDateTime] Kevin 2009-12-09 21:02:45

答

有没有对myLargeTable.actionDate和.UniqueID任何索引？

actionDate上有一个clusered索引，但uniqueID上没有。它正在插入的归档表上没有索引。 – Kevin 2009-12-09 20:19:26

对于JOIN，您需要myLargeTable.UniqueId上的索引。检查查询分析器中的执行计划，您可能会看到表扫描。 – 2009-12-09 20:27:01

感谢Jonas，我要去和设计数据库的人谈谈，看看为什么我们在uniqueID字段没有索引。似乎它是有道理的... – Kevin 2009-12-09 20:37:51

答

您是否试过比100更大的批量？

什么是最花时间？ INSERT，还是DELETE？

当我将批量调整到1000时，插入和删除操作需要大约3分半钟才能完成。初次插入到NextIDs只需要一秒钟。 – Kevin 2009-12-09 20:18:09

答

您有效地有三个选择哪需要你插入之前运行/ delete命令执行：

为第1插入：

SELECT top 100 from myLargeTable Where myLargeTable.actionDate < twoYearsAgo

的第二插入：

SELECT <fields> FROM myLargeTable INNER JOIN NextIDs 
on myLargeTable.UniqueID = NextIDs.UniqueID

用于删除：

(select *) 
FROM MyLargeTable INNER JOIN NextIDs on myLargeTable.UniqueID = NextIDs.UniqueID

我想尝试和优化这些，如果它们都很快，那么索引可能会减慢你的写入速度。几点建议：所有三个语句

开始探查，看看什么东西happenng与读/写等
检查索引的使用。
尝试运行SELECTs只返回PK，看看如果延迟执行查询或获取数据（也有例如，任何全文索引字段，TEXT场等）

答

你在用于过滤结果的列的源表上有索引？在这种情况下，这将是actionDate。

另外，在执行大量插入操作之前，通常可以帮助删除目标表中的所有索引，但在这种情况下，您一次只能执行100个索引。

你也可能会更好地在大批量生产。每次有100个查询的开销将最终占据成本/时间。

在此期间服务器上还有其他活动吗？有没有发生阻塞？

希望这给你一个出发点。

如果您可以提供您正在使用的确切代码（如果存在隐私问题，也许没有列名），那么也许有人可以找到其他优化方法。

编辑：您是否检查过您的代码块查询计划？我遇到了类似这样的表变量的问题，其中查询优化器无法确定表变量的大小很小，所以它总是试图对基表执行全表扫描。

在我的情况下，它最终成为一个争论点，所以我不知道最终的解决方案是什么。你当然可以在actionDate上添加一个条件给所有你选择的查询，这至少可以减少这种影响。

另一种选择是使用普通表来保存ID。

在OP中查看我的编辑。我认为这会回答你所有的问题。 – Kevin 2009-12-09 20:26:30

答

此使用输出条款，你可以尝试这样做：

declare @items table (
    <field list just like source table>) 

delete top 100 source_table 
    output deleted.first_field, deleted.second_field, etc 
    into @items 
    where <conditions> 

insert archive_table (<fields>) 
    select (<fields>) from @items

您可能也有能做到这一点在一个单一的查询，通过做“输出到”直接到档案表（省去了需要表VAR）

SQL归档存储过程的最佳实践

相关推荐