오라클 사용하여 대량 삽입 .NET

.NET을 사용하여 Oracle로 대량 삽입을 할 수있는 가장 빠른 방법은 무엇입니까? 나는 오라클에 .NET을 사용 160K 레코드에 대한 전송해야합니다. 현재, 내가 삽입 문을 사용하고 실행하고있어 160K times.It를 완료하는 데 25분 정도 걸립니다. 원본 데이터는 다른 데이터베이스 질의의 결과로서, DataTable에에 (MySQL의) 저장

이 작업을 수행 할 수있는 더 좋은 방법이 있나요?

편집 : 나는 현재의 System.Data.OracleClient를 사용하고 있지만, 기꺼이 다른 공급자 (ODP.NET, DevArt를, 등)를 사용하여 솔루션을 수락

해결법

==============================
1.나는 15 또는 ODP.NET에 바인딩 배열을 사용하여 초, 그래서 5 만 개 기록을로드하고있어

나는 15 또는 ODP.NET에 바인딩 배열을 사용하여 초, 그래서 5 만 개 기록을로드하고있어

그것은 반복해서 지정 저장 프로 시저를 호출하여 작동합니다 (그리고있는 당신은 업데이트 / 삽입 / 삭제 할 수 있습니다)하지만 대량으로 데이터베이스에 .NET의 다중 매개 변수 값을 전달합니다.

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

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

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

/ 데미안
==============================
2.나는 최근 대량 삽입 (ODP.NET)에 대한 굉장 전문 클래스를 발견했다. Oracle.DataAccess.Client.OracleBulkCopy! 그것은 매개 변수로 데이터 테이블합니다, 당신은 WriteTOServer 메소드를 호출 ... 매우 빠르고 효과적입니다, 행운을 빌어 요!

나는 최근 대량 삽입 (ODP.NET)에 대한 굉장 전문 클래스를 발견했다. Oracle.DataAccess.Client.OracleBulkCopy! 그것은 매개 변수로 데이터 테이블합니다, 당신은 WriteTOServer 메소드를 호출 ... 매우 빠르고 효과적입니다, 행운을 빌어 요!
==============================
3.그가하지 않는 바인드 자신의 값하지만 그는 () 및 String.format를 사용하기 때문에 롭 스티븐슨 - Legget의 솔루션은 느립니다.

그가하지 않는 바인드 자신의 값하지만 그는 () 및 String.format를 사용하기 때문에 롭 스티븐슨 - Legget의 솔루션은 느립니다.

당신은이 문장의이 값을 계산로 시작하는 SQL 문을 실행하기 위해 오라클을 요청합니다. 그 후 그것은 이미 문을 알고 여부를 해시 테이블에서 찾습니다. 그것은 이미 문을 알고있는 경우이 해시 테이블에서의 실행 경로를 검색 할 수 있으며, 오라클은 전에이 문을 실행했기 때문에 정말 빨리이 문을 실행합니다. 이 라이브러리 캐시라고하고 바인드 SQL 문하지 않으면 제대로 작동하지 않습니다.

예를 들어하지 않는다 :

INT N;
```
    for (n = 0; n < 100000; n ++)
    {
      mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
      mycommand.ExecuteNonQuery();
    }
```
하지만 수행
```
      OracleParameter myparam = new OracleParameter();
      int n;

      mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
      mycommand.Parameters.Add(myparam);

      for (n = 0; n < 100000; n ++)
      {
        myparam.Value = n + 1;
        mycommand.ExecuteNonQuery();
      }
```
아니 사용하여 매개 변수는 SQL 주입을 일으킬 수 있습니다.
==============================
4.SQL 서버의 SqlBulkCopy의이 눈부시게 빠르다. 불행히도, 난 OracleBulkCopy 훨씬 느린 것으로 나타났습니다. 또한 그것은 문제가있다 :

SQL 서버의 SqlBulkCopy의이 눈부시게 빠르다. 불행히도, 난 OracleBulkCopy 훨씬 느린 것으로 나타났습니다. 또한 그것은 문제가있다 :

당신이 작은 기록하지만 많은 행이있는 테이블을 채우려면 사실 System.Data.OracleClient.OracleDataAdapter 빨리 OracleBulkCopy보다. OracleDataAdapter위한 최적의 BatchSize에서이 OracleBulkCopy보다 작은,하지만 당신은 조정하기 위해 배치 크기가 필요합니다.

나는 86 실행으로 윈도우 7 시스템에서 내 테스트를 실행하고 32 비트 ODP.Net 클라이언트 2.112.1.0. . OracleDataAdapter는 System.Data.OracleClient에 2.0.0.0의 일부입니다. 내 테스트 세트는 최대의 기록 크기 60에 대한 행이다. 102 바이트 (평균 크기 43 개 문자). 데이터 소스는 스트림으로 라인으로 라인을 읽어 25 메가 바이트 텍스트 파일입니다.

내 테스트에서 나는 고정 된 테이블 크기에 입력 데이터 테이블을 구축하고 서버에 데이터 블록을 복사 OracleBulkCopy 또는 OracleDataAdapter 중 하나를 사용했다. 나는 OracleBulkCopy 0으로 BatchSize에서 왼쪽 (그래서 현재 테이블의 내용을 하나 개의 일괄 처리로 복사된다)와 OracleDataAdapter의 테이블 크기 (다시는 내부적으로 하나의 배치를 작성해야합니다)로 설정합니다. 최상의 결과 :

비교하려고:

동일한 클라이언트 시스템은 테스트 서버는 SQL 서버 2008 R2입니다. SQL Server의 경우, 대량 복사 명확하게 갈 수있는 가장 좋은 방법입니다. 그것은 전체 빠른이지만, 서버의 부하는 데이터 어댑터를 사용하는 경우보다 낮을뿐만 아니라. 그것은 OracleBulkCopy 꽤 같은 경험을 제공하지 않는 것을 유감합니다 - 대량 복사 API는 데이터 어댑터보다 사용하기 훨씬 쉽다.
==============================
5.이 문제를 해결하기 위해 정말 빠른 방법은 MySQL 데이터베이스에 오라클 데이터베이스에서 데이터베이스 링크를 만드는 것입니다. 당신은 비 오라클 데이터베이스에 대한 데이터베이스 링크를 생성 할 수 있습니다. 데이터베이스 링크를 생성 한 후에는 ... A를 MySQL 데이터베이스에서 데이터를 검색에서 ... 문을 선택 *로 테이블을 MyData을 만들 수 있습니다. 이것은 이기종 연결이라고합니다. 당신이없는이 방법은 데이터를 이동하기 위해 .NET 응용 프로그램에서 아무것도 할 수 있습니다.

이 문제를 해결하기 위해 정말 빠른 방법은 MySQL 데이터베이스에 오라클 데이터베이스에서 데이터베이스 링크를 만드는 것입니다. 당신은 비 오라클 데이터베이스에 대한 데이터베이스 링크를 생성 할 수 있습니다. 데이터베이스 링크를 생성 한 후에는 ... A를 MySQL 데이터베이스에서 데이터를 검색에서 ... 문을 선택 *로 테이블을 MyData을 만들 수 있습니다. 이것은 이기종 연결이라고합니다. 당신이없는이 방법은 데이터를 이동하기 위해 .NET 응용 프로그램에서 아무것도 할 수 있습니다.

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

그러나 나는의 System.Data.OracleClient와 Oracle 테이블에서 160K 기록을 삽입하는 것은 25 분 걸릴해야한다고 생각하지 않습니다. 난 당신이 너무 많은 시간을 커밋 생각합니다. 그리고 매개 변수를 사용하여 삽입 문에 당신을 결합하여 값을하거나 당신이 당신의 값을 연결 않습니다. 바인딩이 훨씬 빠릅니다.

==============================

6.다소 혼란 링크 된 예를 찾기, 내가 테스트 테이블 (jkl_test)로 작업 배열 삽입을 보여 일부 코드를했다. 다음 표는 다음과 같습니다

다소 혼란 링크 된 예를 찾기, 내가 테스트 테이블 (jkl_test)로 작업 배열 삽입을 보여 일부 코드를했다. 다음 표는 다음과 같습니다

create table jkl_test (id number(9));

다음은 간단한 콘솔 응용 프로그램을위한 닷넷 코드는 오라클에 연결이 ODP.Net 및 삽입 (5 개) 정수 배열을 사용하는 것을 :

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();
        }
    }
}

==============================
7.내 결과와 테오의 제안에 후속 (사과 - 나는 현재 주석으로이를 게시 할 수있는 충분한 명성을하지 않습니다)

내 결과와 테오의 제안에 후속 (사과 - 나는 현재 주석으로이를 게시 할 수있는 충분한 명성을하지 않습니다)

먼저,이 몇 가지 명명 된 매개 변수를 사용하는 방법입니다 :
```
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();
}
```
그러나, 나는 사이의 속도에 어떤 변화를 보지 못했다 :

나는 삭제하고 트랜잭션 내에서 2500 행을 삽입,의 System.Data.OracleClient를 사용하고 있습니다
==============================
8.오라클은 말한다 (http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)

오라클은 말한다 (http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)

내 경험은 로더가 자신의 테이블 빠른 무엇보다도로드 것입니다.

==============================

9.당신이 관리되지 않는 오라클 클라이언트 (Oracle.DataAccess) 후 가장 빠른 방법은 OracleBulkCopy을 사용하는 것입니다을 사용하는 경우, 같은 타릭에 의해 지적되었다.

당신이 관리되지 않는 오라클 클라이언트 (Oracle.DataAccess) 후 가장 빠른 방법은 OracleBulkCopy을 사용하는 것입니다을 사용하는 경우, 같은 타릭에 의해 지적되었다.

당신이 최신 관리 오라클 클라이언트 (Oracle.ManagedDataAccess)를 사용하는 경우, 가장 빠른 방법은 데미안에 의해 지적 된 배열 바인딩을 사용하는 것입니다. 당신이 배열 바인딩 구체적인에서 청소 응용 프로그램 코드를 유지하려는 경우, 당신은 바인딩 배열을 사용 OracleBulkCopy 당신 자신의 구현을 작성할 수 있습니다.

여기에 실제 프로젝트에서 사용 예입니다 :

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

10K 기록은 500ms에 삽입!

여기 구현은 다음과 같습니다

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 패키지를 사용합니다 (훨씬 빠른 반사 이상)

==============================

10.나는 OracleBulkCopy 가장 빠른 방법 중 하나라고 생각한다. 나는 새로운 ODAC 버전이 필요하다고, 배울 수있는 몇 가지 문제가 있었다. 참조 유형 [Oracle.DataAccess.Client.OracleBulkCopy] 어디?

나는 OracleBulkCopy 가장 빠른 방법 중 하나라고 생각한다. 나는 새로운 ODAC 버전이 필요하다고, 배울 수있는 몇 가지 문제가 있었다. 참조 유형 [Oracle.DataAccess.Client.OracleBulkCopy] 어디?

여기에 적합한 기존 오라클 테이블에 쿼리에서 복사 할 전체 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 컬럼과 테이블을 복사하려면이 옵션을 사용합니다. 나는이 연결된 서버의 참조를 사용하여 일을하지 않았다 DBA에 대한 질문입니다. 나는 재시도 새로운 ODAC와 함께 게재 연결하지 않았다.

from https://stackoverflow.com/questions/343299/bulk-insert-to-oracle-using-net by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL은 - 소수점 이하 2 자리에서 반올림 (0)	2020.04.07
[SQL] CTE에서 테이블의 업데이트 기록 (0)	2020.04.07
[SQL] 절 "과"MySQL을 포함하지 않음? (0)	2020.04.06
[SQL] 가입과 방법을 찾을 수 CakePHP의 (0)	2020.04.06
[SQL] 어떻게 SQL Server에서 문자열의 각 단어의 첫 글자를 대문자로 할 수있는 가장 좋은 방법 (0)	2020.04.06

복붙노트

[SQL] 오라클 사용하여 대량 삽입 .NET

오라클 사용하여 대량 삽입 .NET

해결법

1.나는 15 또는 ODP.NET에 바인딩 배열을 사용하여 초, 그래서 5 만 개 기록을로드하고있어

2.나는 최근 대량 삽입 (ODP.NET)에 대한 굉장 전문 클래스를 발견했다. Oracle.DataAccess.Client.OracleBulkCopy! 그것은 매개 변수로 데이터 테이블합니다, 당신은 WriteTOServer 메소드를 호출 ... 매우 빠르고 효과적입니다, 행운을 빌어 요!

3.그가하지 않는 바인드 자신의 값하지만 그는 () 및 String.format를 사용하기 때문에 롭 스티븐슨 - Legget의 솔루션은 느립니다.

4.SQL 서버의 SqlBulkCopy의이 눈부시게 빠르다. 불행히도, 난 OracleBulkCopy 훨씬 느린 것으로 나타났습니다. 또한 그것은 문제가있다 :

6.다소 혼란 링크 된 예를 찾기, 내가 테스트 테이블 (jkl_test)로 작업 배열 삽입을 보여 일부 코드를했다. 다음 표는 다음과 같습니다

7.내 결과와 테오의 제안에 후속 (사과 - 나는 현재 주석으로이를 게시 할 수있는 충분한 명성을하지 않습니다)

8.오라클은 말한다 (http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)

9.당신이 관리되지 않는 오라클 클라이언트 (Oracle.DataAccess) 후 가장 빠른 방법은 OracleBulkCopy을 사용하는 것입니다을 사용하는 경우, 같은 타릭에 의해 지적되었다.

10.나는 OracleBulkCopy 가장 빠른 방법 중 하나라고 생각한다. 나는 새로운 ODAC 버전이 필요하다고, 배울 수있는 몇 가지 문제가 있었다. 참조 유형 [Oracle.DataAccess.Client.OracleBulkCopy] 어디?

'SQL' 카테고리의 다른 글

티스토리툴바