时间戳

首页 > 解决方案 > 有没有办法优化从 Oracle 到 SQL Server 的数据迁移并减少花费的时间？

sql-server - 有没有办法优化从 Oracle 到 SQL Server 的数据迁移并减少花费的时间？

问题描述

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

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

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

我的脚本有效，但是在迁移较大的表时，查询将关闭可用内存，并且迁移开始时每 1 行大约需要 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 行为一组迁移数据，但现在看来，根据执行计划，oracle 查询花费的时间最多（大约 80 %）。另外，虽然我尝试使用“开始事务”和“提交”（可能有更好的使用方法），但脚本如前所述增加了每次迭代的内存占用（大部分内存在提交，但它在后台缓慢增加）

标签： sql-serveroracletsqldatabase-administrationdata-migration

解决方案

有多种方法。

  1. 删除您将数据导入到的 SQL Server 表上的索引，并在导入后再次创建它们。

  2. 单独的出口、转运和装载。这意味着将Oracle服务器上的数据提取为纯文本文件，然后通过ftp将它们传输到SQL Server机器，然后将它们加载到SQL Server。

  3. （最有效的方法）使用 MS SQL Server 批量加载：https ://docs.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql- server?view=sql-server-2017 在这种情况下，您的 1m 记录不会通过整个数据库进行处理，而是直接写入数据库文件，速度要快得多。

推荐阅读