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

Question

我有一个非常大的数据库（约100Gb），主要由两个表组成，我想要减小它们的大小（这两个表各有大约5000万条记录）。我在同一台服务器上设置了一个存档数据库，并使用相同的架构来存储这两个表。我正在尝试确定最佳的概念方法来从活动数据库中删除行并将它们插入存档数据库。以下是我现在正在进行的伪代码：

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

目前，处理1000条记录需要7分钟，速度非常慢。我已经测试了删除和插入操作，两者都需要大约3.5分钟完成，因此它们的效率差异不是很大。有人能提供一些优化建议吗？

谢谢！

这是SQL Server 2000。

编辑：在大表中，ActionDate字段上有一个聚集索引。还有两个其他的索引，但在任何查询中都没有被引用。Archive表中没有索引。在我的测试服务器上，这是唯一访问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

- Kevin

---

你的主键字段上有聚集索引吗？ - feihtthief

NextIDs是一个表变量还是临时表？如果是表变量，试试用临时表代替。我在SQL Server 2000中使用表变量遇到了性能问题，尽管没有这么糟糕。 - Darryl Peterson

NextID目前被声明为表变量。我将尝试使用临时表。 - Kevin

如果你真的想要过去两年，那么在使用dateadd函数时应该使用"yy"以及"-2"参数，而不是"d"以及"-2*365"参数。这只是个小细节，但我想提一下。 - Tom H

Answer 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

我会尝试优化这些内容，如果它们都很快，那么索引可能会减慢您的写入速度。以下是一些建议：

1. 启动分析器并查看读/写等操作。
2. 检查所有三个语句的索引使用情况。
3. 尝试仅返回PK运行SELECTs，以查看延迟是查询执行还是获取数据（例如是否有任何全文索引字段，TEXT字段等）。

- davek

Answer 2

您在源表上是否为您用于过滤结果的列（在本例中为actionDate）建立了索引？此外，通常在进行大量插入之前从目标表中删除所有索引可能会有所帮助，但在这种情况下，您每次只做100个。

您最好以较大的批次执行此操作。每次100个查询的开销将最终主导成本/时间。

在此期间服务器上是否还有其他活动？是否发生任何阻塞？

希望这给您提供了一个起点。

如果您可以提供您正在使用的确切代码（如果存在隐私问题，则可能不包括列名），那么也许有人可以发现其他优化方法。

编辑：您是否检查了块代码的查询计划？我遇到过像这样的表变量的问题，其中查询优化器无法确定表变量的大小，因此始终尝试在基本表上进行完整表扫描。

在我的情况下，最终成为一个无关紧要的问题，因此我不确定最终解决方案是什么。您可以肯定地向所有选择查询添加一个关于actionDate的条件，这至少会最小化其影响。

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

- Tom H

---

请看我在原始帖子中的编辑。我认为这回答了你所有的问题。 - Kevin

Answer 3

INSERT和DELETE语句正在连接

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

如果没有索引，并且您指示没有索引，那么您将执行两个表扫描。这很可能是缓慢的原因，因为SQL Server表扫描会将整个表读入到一个临时表中，然后在临时表中搜索匹配的行，最后删除临时表。

我认为您需要在UniqueID上添加索引。维护它的性能损失肯定比表扫描要小。而且在完成归档后，您可以将其删除。

- DaveE

---

这实际上导致了我的解决方案。我没有通过UniqueID进行索引来跟踪需要移动的行，而是在我的插入和删除中简单地使用了WHERE [ActionDateTime] < @TwoYearsAgo子句，效率大大提高。 - Kevin

Answer 4

我的大表中的actionDate和UniqueID上有没有索引？

- Jonas Lincoln

---

actionDate 上有一个聚集索引，但 uniqueID 上没有。在插入归档表时没有任何索引。 - Kevin

你需要在myLargeTable.UniqueId上建立索引以进行JOIN操作。在查询分析器中检查执行计划，你可能会看到表扫描。 - Jonas Lincoln

谢谢Jonas，我要去找设计数据库的那个人，看看为什么我们在uniqueID字段上没有索引。这似乎是有道理的... - Kevin

Answer 5

你尝试过比100更大的批量吗？

哪个步骤花费的时间最长？是插入还是删除？

- Joe

---

当我将批处理大小增加到1000时，单独运行插入和删除都需要约3分30秒才能完成。向NextIDs的初始插入仅需1秒钟。 - Kevin

Answer 6

你可以尝试使用输出子句来实现这个功能：

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

你也可以尝试在单个查询中完成此操作，通过直接将“output into”输出到归档表中（消除了对表变量的需求）

- Ray