是否有一种方法可以优化从Oracle到SQL Server的数据迁移并减少所花费的时间

我正在使用SSMS中的T-SQL脚本将数据从Oracle(12c不可插拔)数据库迁移到SQL server(2012)，并将Oracle数据库设置为链接服务器。模式已经同步，所以只剩下数据了。我面临的问题是迁移所花费的时间和它所消耗的内存量。

当运行我的脚本时，它需要很长时间，而且最初在迁移完整表时，我在SQL Server服务器上遇到了内存问题，所以我决定将表的迁移拆分为一次1000000行的块。然而，在我的代码中似乎存在"内存泄漏"，因为查询消耗的内存随着每次迭代而增加，并且查询花费了很长时间。

编辑：我已经删除了MS SQL Server数据库中的索引。

我的脚本可以工作，但当迁移较大的表时，查询将占用可用内存，并且迁移一开始每一百万行大约需要5分钟(并且随着每次迭代而缓慢增加)。当然，时间也取决于表中的行数等。

数据统计：*桌子：~1600*共行：~1张账单。(最大的桌子是300行)

USE INFODBA
GO
SET NOCOUNT ON
DECLARE @start BIGINT
DECLARE @end BIGINT
DECLARE @maxrows BIGINT
DECLARE @step BIGINT
DECLARE @sqlstr NVARCHAR(4000)
DECLARE @table_name VARCHAR(255)
DECLARE @counter INT
DECLARE @time TIME
DECLARE @error NVARCHAR(4000)
-- Iterates in @step rows at a time
SET @step = 1000000;
SET @start = 0;
SET @end = @start + @step;
SET @counter = 1;
SET @table_name = 'sourceTable'
PRINT @table_name;
-- GET exact rowcount of Oracle table
SELECT @maxrows = NUM_ROWS FROM OPENQUERY(ORACLETC, 'SELECT COUNT(*) AS NUM_ROWS FROM sourceTable')
WHILE @start < @maxrows
BEGIN
SELECT @time = CONVERT (time, CURRENT_TIMESTAMP)
SET @sqlstr = 'INSERT INTO targetTable SELECT * FROM OPENQUERY(ORACLETC,''SELECT COL1,COL2,COL3,COL4 FROM sourceTable'
SET @sqlstr = @sqlstr + ' OFFSET ' + CAST(@start AS NVARCHAR(255)) + ' ROWS FETCH NEXT ' + CAST(@step AS NVARCHAR(255)) + ' ROWS ONLY'') AS ROWSET_1';
-- Print output immediatly to capture progress
PRINT 'Iteration;' + CAST(@counter AS VARCHAR(255)) + ';Time;' + CAST(@time AS VARCHAR(255)) + ';Start;' + CAST(@start AS VARCHAR(255)) + ';End;' + CAST(@end AS VARCHAR(255)) + ';MAX;' + CAST(@maxrows AS VARCHAR(255)) + ';Query;' + @sqlstr
RAISERROR (N'', 0, 1) WITH NOWAIT
-- Start the migration query and catch error messages
BEGIN TRY
BEGIN TRANSACTION;
EXEC dbo.sp_executesql @sqlstr
COMMIT;
END TRY
BEGIN CATCH
SELECT @error = ERROR_MESSAGE();
PRINT 'ERROR on iteration: ' + CAST(@counter AS VARCHAR(255)) + ' with query: ' + @sqlstr + ' - Error: ' + @error
SELECT ERROR_MESSAGE() AS ErrorMessage;
RETURN
END CATCH
SET @counter += 1
SET @start = @end
SET @end += @step
END

这个脚本将以1 mill行为一组迁移数据，但现在看来，根据执行计划，oracle查询似乎花费了最多的时间(约80%)。此外，尽管我尝试使用"开始事务"one_answers"提交"(可能有更好的方法来使用它)，但正如所提到的，脚本增加了每次迭代的内存占用(大部分内存在提交时释放，但在后台增加缓慢)

有很多方法。

删除导入数据的SQL Server表上的索引，并在导入后再次创建索引。
单独导出、传输和加载。这意味着将Oracle服务器上的数据提取为纯文本文件，然后通过ftp将其传输到SQL server计算机，然后将其加载到SQL server。
(最有效的方法)使用MS SQL Server大容量加载：https://learn.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server?view=sql-服务器-2017在这种情况下，您的100万条记录将不会通过整个数据库进行处理，而是直接写入数据库文件，这要快得多。

Vlad的回答是正确的；您当前的查询包括动态SQL、WHILE循环和OPENQUERY，所有这些通常都会占用性能。使用大容量加载(即BCP)或SSIS包导入平面文件应该会得到更好的结果。

相关内容

最新更新

热门标签：