我正在尝试将记录从数据库中的表存档到存档数据库中的相同表。我需要能够对日期大于三年前的所有记录进行插入,然后删除这些行。但是,此表有数百万条处于活动状态的记录,因此我想一次在大约100到1000个块的循环中运行它。到目前为止,我的存储过程执行了整个插入语句,然后执行了删除语句(在事务中),该语句具有与插入语句基本相同的WHERE子句。我的WHILE循环正在查找表中最早的日期,以确定循环何时完成。其中一些似乎效率很低。有没有一种方法可以对记录块进行插入和删除,而不必在同一循环执行中对它们进行两次查找?有没有更好的方法来确定WHILE语句何时完成?运行MS SQL Server 2000。
这是我当前的过程(ISAdminDB是主数据库,ISArchive是归档数据库):
WHILE ( (SELECT MIN( [MyTable].[DateTime]) FROM [ISAdminDB].[dbo].[MyTable]) < DATEADD(d, -(3 * 365), GetDate())) BEGIN INSERT INTO [ISArchive].[dbo].[MyTable] (<Fields>) SELECT TOP 1000 (<Fields>) FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < DATEADD(d, -(3 * 365), GetDate()) AND UniqueID in (SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[MyTable] ORDER BY [MyTable].[DateTime] ASC ) BEGIN TRAN DELETE FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < DATEADD(d, -(3 * 365), GetDate()) AND (UniqueID in (SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[MyTable] ORDER BY [MyTable].[DateTime] ASC)) COMMIT END
首先,您要删除3年前的特定日期的记录。您不在乎删除它们的顺序,只需要继续删除它们,直到没有剩余的为止。您还可以通过使用临时表存储ID,将截止日期存储在变量中并重复引用它来加快处理速度。
现在我们有了:
DECLARE @NextIDs TABLE(UniqueID int primary key) DECLARE @ThreeYearsAgo datetime SELECT @ThreeYearsAgo = DATEADD(d, -(3 * 365), GetDate()) WHILE EXISTS(SELECT 1 FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo) BEGIN BEGIN TRAN INSERT INTO @NextIDs(UniqueID) SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo INSERT INTO [ISArchive].[dbo].[MyTable] (<Fields>) SELECT (<Fields>) FROM [ISAdminDB].[dbo].[MyTable] AS a INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID DELETE [ISAdminDB].[dbo].[MyTable] FROM [ISAdminDB].[dbo].[MyTable] INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID DELETE FROM @NextIDs COMMIT TRAN END