.NET을 사용하여 Oracle에 대량 삽입

.NET을 사용하여 Oracle에 대량 삽입

.NET을 사용하여 Oracle에 대량 삽입하는 가장 빠른 방법은 무엇입니까?.NET을 사용하여 약 16만 개의 레코드를 Oracle로 전송해야 합니다.현재 저는 insert 문을 사용하고 있으며 160,000회 실행하고 있습니다.완료하는 데 약 25분이 걸립니다.소스 데이터는 다른 데이터베이스(MySQL)의 쿼리 결과로 DataTable에 저장됩니다.

이보다 더 좋은 방법이 없을까요?

편집 : 현재 시스템을 사용하고 있습니다.Data.OracleClient이지만 다른 공급자(ODP.NET, DevArt 등)를 사용하는 솔루션을 수락할 의사가 있음

ODP.NET의 Array Binding을 사용하여 약 15초 안에 50,000개의 레코드를 로드합니다.

지정한 저장 프로시저(및 업데이트/삽입/삭제를 수행할 수 있음)를 반복적으로 호출하여 작동하지만 여러 매개 변수 값을 .NET에서 데이터베이스로 대량 전달합니다.

저장 프로시저에 대해 각 모수에 대해 단일 값을 지정하는 대신 각 모수에 대한 값 배열을 지정합니다.

오라클은 매개 변수 배열을 .NET에서 데이터베이스로 한 번에 전달한 다음 지정한 매개 변수 값을 사용하여 사용자가 지정한 저장 프로시저를 반복적으로 호출합니다.

http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

/데미안

최근 대량 삽입에 적합한 특수 클래스(ODP.NET)를 발견했습니다.오라클.데이터 액세스.클라이언트. Oracle 대량 복사!데이터 테이블을 매개 변수로 사용한 다음 쓰기를 호출합니다.TOServer 방법...매우 빠르고 효과적입니다, 행운을 빕니다!!

Rob Stevenson-Legget은 자신의 가치관을 구속하지 않고 끈을 사용하기 때문에 해결이 느립니다.형식( ).

Oracle에 sql 문을 실행하도록 요청하면 이 문의 has 값을 계산하는 것으로 시작됩니다.그런 다음 이 문을 이미 알고 있는지 여부를 해시 테이블에서 확인합니다.Oracle이 문을 이미 알고 있는 경우 이 해시 테이블에서 실행 경로를 검색하여 이 문을 실행한 적이 있으므로 이 문을 실행한 적이 있기 때문입니다.이를 라이브러리 캐시라고 하며 sql 문을 바인딩하지 않으면 제대로 작동하지 않습니다.

예를 들어 하지 않음:

for (int n = 0; n < 100000; n ++)
{
    mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
    mycommand.ExecuteNonQuery();
}

하지만 실행:

OracleParameter myparam = new OracleParameter();
mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
mycommand.Parameters.Add(myparam);
    
for (int n = 0; n < 100000; n ++)
{
    myparam.Value = n + 1;
    mycommand.ExecuteNonQuery();
}

매개 변수를 사용하지 않으면 SQL 주입이 발생할 수도 있습니다.

SQL Server의 SQL BulkCopy는 엄청나게 빠릅니다.안타깝게도 Oracle BulkCopy가 훨씬 느립니다.또한 다음과 같은 문제도 있습니다.

• Oracle BulkCopy를 사용하려면 입력 데이터가 깨끗한지 확인해야 합니다.기본 키 위반이 발생하면 ORA-26026이 발생하여 복구할 수 없는 것으로 보입니다.인덱스를 다시 작성하는 것은 도움이 되지 않으며 테이블의 후속 삽입도 실패하고 일반 삽입도 실패합니다.
• 데이터가 깨끗하더라도 Oracle BulkCopy가 WriteToServer 내부에서 고착되는 경우가 있습니다.문제는 배치 크기에 따라 달라지는 것 같습니다.제 테스트 데이터에서 문제는 제가 반복할 때 테스트와 정확히 같은 지점에서 발생합니다.더 크거나 더 작은 배치 크기를 사용하면 문제가 발생하지 않습니다.배치 크기가 클수록 속도가 불규칙하다는 것을 알 수 있습니다. 이는 메모리 관리와 관련된 문제를 나타냅니다.

실제로 시스템.Data.Oracle Client.작은 레코드이지만 행이 많은 테이블을 채우려면 Oracle DataAdapter가 OracleBulkCopy보다 빠릅니다.배치 크기를 조정해야 하지만 Oracle Data Adapter의 최적 배치 크기는 Oracle BulkCopy의 경우보다 작습니다.

x86 실행 파일과 32비트 ODP.Net 클라이언트 2.112.1.0. . . . Oracle Data Adapter는 시스템의 일부입니다.Data.Oracle Client 2.0.0.0.0.테스트 세트는 최대 102바이트(평균 43자)의 레코드 크기를 가진 약 60만 행입니다.데이터 원본은 25MB 텍스트 파일로, 스트림으로 한 줄씩 읽습니다.

테스트에서 입력 데이터 테이블을 고정된 테이블 크기로 구성한 다음 OracleBulkCopy 또는 OracleDataAdapter를 사용하여 데이터 블록을 서버에 복사했습니다.Oracle BulkCopy에서 BatchSize를 0으로 유지하고(현재 테이블 내용이 한 배치로 복사되도록) Oracle DataAdapter에서 BatchSize를 테이블 크기로 설정했습니다(내부적으로 단일 배치를 생성해야 함).최상의 결과:

• Oracle BulkCopy: 테이블 크기 = 500, 총 기간 4'22"
• Oracle Data Adapter: 테이블 크기 = 100, 총 기간 3'03"

비교를 위해:

• SqlBulkCopy: 테이블 크기 = 1000, 총 기간 0'15"
• SqlDataAdapter: 테이블 크기 = 1000, 총 기간 8'05"

동일한 클라이언트 시스템, 테스트 서버는 SQL Server 2008 R2입니다.SQL 서버의 경우 대량 복사가 가장 좋은 방법입니다.전체적으로 가장 빠를 뿐만 아니라 데이터 어댑터를 사용할 때보다 서버 부하가 낮습니다.Oracle BulkCopy가 DataAdapter보다 BulkCopy API를 훨씬 쉽게 사용할 수 있는 환경을 제공하지 못하는 것은 유감스러운 일입니다.

이 문제를 해결하는 정말 빠른 방법은 Oracle 데이터베이스에서 MySQL 데이터베이스로 데이터베이스 링크를 만드는 것입니다.Oracle이 아닌 데이터베이스에 대한 데이터베이스 연결을 작성할 수 있습니다.데이터베이스 링크를 만든 후에는 ...create table mydata를 ...에서 *를 선택하여 MySQL 데이터베이스에서 데이터를 검색할 수 있습니다.이를 이기종 연결이라고 합니다.이렇게 하면 .net 응용 프로그램에서 데이터를 이동하는 작업을 수행할 필요가 없습니다.

또 다른 방법은 ODP.NET을 사용하는 것입니다.ODP.NET에서는 OracleBulkCopy 클래스를 사용할 수 있습니다.

하지만 저는 시스템으로 오라클 테이블에 16만 개의 레코드를 삽입하는 것은 아니라고 생각합니다.Data.Oracle Client는 25분이 소요됩니다.당신은 너무 많이 저지르는 것 같아요.그리고 매개 변수를 사용하여 값을 삽입 문에 바인딩하거나 값을 연결하시겠습니까?바인딩이 훨씬 빠릅니다.

링크된 예제가 다소 혼란스럽다는 것을 발견하고 테스트 테이블(jkl_test)에 작업 배열 삽입을 보여주는 코드를 작성했습니다.표는 다음과 같습니다.

create table jkl_test (id number(9));

여기 있습니다.ODP.Net을 사용하여 Oracle에 연결하고 5개의 정수 배열을 삽입하는 단순 콘솔 응용 프로그램의 Net 코드:

using Oracle.DataAccess.Client;

namespace OracleArrayInsertExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Open a connection using ODP.Net
            var connection = new OracleConnection("Data Source=YourDatabase; Password=YourPassword; User Id=YourUser");
            connection.Open();

            // Create an insert command
            var command = connection.CreateCommand();
            command.CommandText = "insert into jkl_test values (:ids)";

            // Set up the parameter and provide values
            var param = new OracleParameter("ids", OracleDbType.Int32);
            param.Value = new int[] { 22, 55, 7, 33, 11 };

            // This is critical to the process; in order for the command to 
            // recognize and bind arrays, an array bind count must be specified.
            // Set it to the length of the array.
            command.ArrayBindCount = 5;
            command.Parameters.Add(param);
            command.ExecuteNonQuery();
        }
    }
}

테오의 제안을 제 연구 결과로 후속 조치하기 위해 (사과 - 저는 현재 이 글을 댓글로 올릴 만큼 충분한 평판이 없습니다)

먼저 여러 명명된 매개 변수를 사용하는 방법은 다음과 같습니다.

String commandString = "INSERT INTO Users (Name, Desk, UpdateTime) VALUES (:Name, :Desk, :UpdateTime)";
using (OracleCommand command = new OracleCommand(commandString, _connection, _transaction))
{
    command.Parameters.Add("Name", OracleType.VarChar, 50).Value = strategy;
    command.Parameters.Add("Desk", OracleType.VarChar, 50).Value = deskName ?? OracleString.Null;
    command.Parameters.Add("UpdateTime", OracleType.DateTime).Value = updated;
    command.ExecuteNonQuery();
}

그러나 다음과 같은 속도에는 차이가 없었습니다.

• 각 행에 대한 새 명령String(String)을 구성합니다.포맷)
• 각 행에 대해 매개 변수화된 명령 구성String
• 단일 명령 사용String 및 매개 변수 변경

시스템을 사용하고 있습니다.Data.OracleClient, 트랜잭션 내에서 2500개 행 삭제 및 삽입

Oracle은 다음과 같이 말합니다.

SQL*Loader는 외부 파일의 데이터로 오라클 테이블을 빠르게 채우는 기본 방법입니다.

제 경험으로는 로더가 테이블을 가장 빨리 로딩하는 것 같습니다.

관리되지 않는 오라클 클라이언트(Oracle)를 사용하는 경우.DataAccess) 그러면 Tarik이 지적한 것처럼 Oracle BulkCopy를 사용하는 것이 가장 빠른 방법입니다.

최신 관리 오라클 클라이언트(Oracle)를 사용하는 경우.Managed Data Access)를 사용하는 가장 빠른 방법은 Damien이 지적한 대로 어레이 바인딩을 사용하는 것입니다.애플리케이션 코드를 어레이 바인딩 세부 사항으로부터 깨끗하게 유지하려면 어레이 바인딩을 사용하여 Oracle BulkCopy의 구현을 직접 작성할 수 있습니다.

다음은 실제 프로젝트의 사용 예입니다.

var bulkWriter = new OracleDbBulkWriter();
    bulkWriter.Write(
        connection,
        "BULK_WRITE_TEST",
        Enumerable.Range(1, 10000).Select(v => new TestData { Id = v, StringValue=v.ToString() }).ToList());

500ms에 10K 레코드가 삽입됩니다!

구현은 다음과 같습니다.

public class OracleDbBulkWriter : IDbBulkWriter
{
    public void Write<T>(IDbConnection connection, string targetTableName, IList<T> data, IList<ColumnToPropertyMapping> mappings = null)
    {
        if (connection == null)
        {
            throw new ArgumentNullException(nameof(connection));
        }
        if (string.IsNullOrEmpty(targetTableName))
        {
            throw new ArgumentNullException(nameof(targetTableName));
        }
        if (data == null)
        {
            throw new ArgumentNullException(nameof(data));
        }
        if (mappings == null)
        {
            mappings = GetGenericMappings<T>();
        }

        mappings = GetUniqueMappings<T>(mappings);
        Dictionary<string, Array> parameterValues = InitializeParameterValues<T>(mappings, data.Count);
        FillParameterValues(parameterValues, data);

        using (var command = CreateCommand(connection, targetTableName, mappings, parameterValues))
        {
            command.ExecuteNonQuery();
        }
    }

    private static IDbCommand CreateCommand(IDbConnection connection, string targetTableName, IList<ColumnToPropertyMapping> mappings, Dictionary<string, Array> parameterValues)
    {
        var command = (OracleCommandWrapper)connection.CreateCommand();
        command.ArrayBindCount = parameterValues.First().Value.Length;

        foreach(var mapping in mappings)
        {
            var parameter = command.CreateParameter();
            parameter.ParameterName = mapping.Column;
            parameter.Value = parameterValues[mapping.Property];

            command.Parameters.Add(parameter);
        }

        command.CommandText = $@"insert into {targetTableName} ({string.Join(",", mappings.Select(m => m.Column))}) values ({string.Join(",", mappings.Select(m => $":{m.Column}")) })";
        return command;
    }

    private IList<ColumnToPropertyMapping> GetGenericMappings<T>()
    {
        var accessor = TypeAccessor.Create(typeof(T));

        var mappings = accessor.GetMembers()
            .Select(m => new ColumnToPropertyMapping(m.Name, m.Name))
            .ToList();

        return mappings;
    }

    private static IList<ColumnToPropertyMapping> GetUniqueMappings<T>(IList<ColumnToPropertyMapping> mappings)
    {
        var accessor = TypeAccessor.Create(typeof(T));
        var members = new HashSet<string>(accessor.GetMembers().Select(m => m.Name));

        mappings = mappings
                        .Where(m => m != null && members.Contains(m.Property))
                        .GroupBy(m => m.Column)
                        .Select(g => g.First())
                        .ToList();
        return mappings;
    }

    private static Dictionary<string, Array> InitializeParameterValues<T>(IList<ColumnToPropertyMapping> mappings, int numberOfRows)
    {
        var values = new Dictionary<string, Array>(mappings.Count);
        var accessor = TypeAccessor.Create(typeof(T));
        var members = accessor.GetMembers().ToDictionary(m => m.Name);

        foreach(var mapping in mappings)
        {
            var member = members[mapping.Property];

            values[mapping.Property] = Array.CreateInstance(member.Type, numberOfRows);
        }

        return values;
    }

    private static void FillParameterValues<T>(Dictionary<string, Array> parameterValues, IList<T> data)
    {
        var accessor = TypeAccessor.Create(typeof(T));
        for (var rowNumber = 0; rowNumber < data.Count; rowNumber++)
        {
            var row = data[rowNumber];
            foreach (var pair in parameterValues)
            {
                Array parameterValue = pair.Value;
                var propertyValue = accessor[row, pair.Key];
                parameterValue.SetValue(propertyValue, rowNumber);
            }
        }
    }
}

참고: 이 구현에서는 속성에 대한 액세스를 최적화하기 위해 Fastmember 패키지를 사용합니다(반사 속도보다 훨씬 빠름).

Oracle BulkCopy가 가장 빠른 방법 중 하나라고 생각합니다.저는 새로운 ODAC 버전이 필요하다는 것을 배우는데 어려움을 겪었습니다.Cf. 여기서 는 [Oracle] 유형입니다.데이터 액세스.Client.Oracle BulkCopy] ?

다음은 쿼리에서 적합한 기존 Oracle 테이블로 복사할 전체 PowerShell 코드입니다.Sql-Server를 데이터 소스로 사용해 보았지만 다른 유효한 OLE-DB 소스는 사용할 것입니다.

if ($ora_dll -eq $null)
{
    "Load Oracle dll"
    $ora_dll = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") 
    $ora_dll
}

# sql-server or Oracle source example is sql-server
$ConnectionString ="server=localhost;database=myDatabase;trusted_connection=yes;Provider=SQLNCLI10;"

# Oracle destination
$oraClientConnString = "Data Source=myTNS;User ID=myUser;Password=myPassword"

$tableName = "mytable"
$sql = "select * from $tableName"

$OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$OLEDBConn.open()
$readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
$readcmd.CommandTimeout = '300'
$da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)
$OLEDBConn.close()
#Write-Output $dt

if ($dt)
{
    try
    {
        $bulkCopy = new-object ("Oracle.DataAccess.Client.OracleBulkCopy") $oraClientConnString
        $bulkCopy.DestinationTableName = $tableName
        $bulkCopy.BatchSize = 5000
        $bulkCopy.BulkCopyTimeout = 10000
        $bulkCopy.WriteToServer($dt)
        $bulkcopy.close()
        $bulkcopy.Dispose()
    }
    catch
    {
        $ex = $_.Exception
        Write-Error "Write-DataTable$($connectionName):$ex.Message"
        continue
    }
}

BTW: 저는 이것을 CLOB 열로 표를 복사하는 데 사용합니다.저는 링크된 서버 cf를 사용하여 작동하지 않았습니다. dba에 대한 질문.저는 새로운 ODAC로 연결된 서비스를 다시 시도하지 않았습니다.

언급URL : https://stackoverflow.com/questions/343299/bulk-insert-to-oracle-using-net