Dapper를 사용하여 예상보다 오래 걸리는 대량 삽입

Dapper를 사용하여 예상보다 오래 걸리는 대량 삽입

---

이 기사를 읽은 후 Dapper를 사용하는 방식을 자세히 살펴보기로 결정했습니다.

이 코드를 빈 데이터베이스에서 실행했습니다.

var members = new List<Member>();
for (int i = 0; i < 50000; i++)
{
    members.Add(new Member()
    {
        Username = i.toString(),
        IsActive = true
    });
}

using (var scope = new TransactionScope())
{
    connection.Execute(@"
insert Member(Username, IsActive)
values(@Username, @IsActive)", members);

    scope.Complete();
}

약 20 초가 걸렸습니다. 초당 2500 회 삽입입니다. 나쁘지는 않지만 블로그가 초당 45k 삽입을 달성했다고 생각하면 좋지 않습니다. Dapper에서이 작업을 수행하는 더 효율적인 방법이 있습니까?

또한 부수적으로 Visual Studio 디버거를 통해이 코드를 실행하는 데 3 분이 넘게 걸렸 습니다! 디버거가 속도를 조금 늦출 것이라고 생각했지만 그 정도를보고 정말 놀랐습니다.

최신 정보

그래서 이건

using (var scope = new TransactionScope())
{
    connection.Execute(@"
insert Member(Username, IsActive)
values(@Username, @IsActive)", members);

    scope.Complete();
}

이

    connection.Execute(@"
insert Member(Username, IsActive)
values(@Username, @IsActive)", members);

둘 다 20 초가 걸렸습니다.

하지만 4 초가 걸렸습니다!

SqlTransaction trans = connection.BeginTransaction();

connection.Execute(@"
insert Member(Username, IsActive)
values(@Username, @IsActive)", members, transaction: trans);

trans.Commit();

---

내가 달성 할 수 있었던 최고는이 접근 방식을 사용하여 4 초 만에 50k 레코드였습니다.

SqlTransaction trans = connection.BeginTransaction();

connection.Execute(@"
insert Member(Username, IsActive)
values(@Username, @IsActive)", members, transaction: trans);

trans.Commit();

---

나는 최근에 이것을 우연히 발견하고 연결이 열린 후에 TransactionScope가 생성된다는 것을 알았습니다 (Dappers Execute는 Query와 달리 연결을 열지 않기 때문에 이것을 가정합니다). 여기에 답변 Q4에 따르면 : https://stackoverflow.com/a/2886326/455904 연결이 TransactionScope에 의해 처리되지 않습니다. 내 동료는 몇 가지 빠른 테스트를 수행했으며 TransactionScope 외부에서 연결을 열면 성능이 크게 저하되었습니다.

따라서 다음으로 변경하면 작동합니다.

// Assuming the connection isn't already open
using (var scope = new TransactionScope())
{
    connection.Open();
    connection.Execute(@"
insert Member(Username, IsActive)
values(@Username, @IsActive)", members);

    scope.Complete();
}

---

이 모든 예가 불완전하다는 것을 알았습니다.

다음은 사용 후 연결을 올바르게 닫고이 스레드의 최신 및 더 나은 답변을 기반으로 Excecute 성능을 향상시키기 위해 트랜잭션 범위를 올바르게 사용하는 코드입니다.

using (var scope = new TransactionScope()) 
{
    Connection.Open();
    Connection.Execute(sqlQuery, parameters);

    scope.Complete();
}

---

Execute삽입 문이 하나만 있는 메서드를 사용하면 대량 삽입이 수행되거나 효율적이지 않습니다. 심지어로 허용 대답은 Transaction하지 않습니다 Bulk Insert.

을 수행 Bulk Insert하려면 https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy를 사용 SqlBulkCopy 하십시오.

이보다 더 빠른 것은 찾을 수 없습니다.

Dapper Plus

면책 조항 : 저는 Dapper Plus 프로젝트의 소유자입니다.

이 프로젝트는 무료는 아니지만 모든 대량 작업을 제공합니다.

• BulkInsert
• 대량 업데이트
• BulkDelete
• BulkMerge

(후드 아래에서 사용 SqlBulkCopy)

그리고 ID 값 출력과 같은 몇 가지 추가 옵션 :

// CONFIGURE & MAP entity
DapperPlusManager.Entity<Order>()
                 .Table("Orders")
                 .Identity(x => x.ID);

// CHAIN & SAVE entity
connection.BulkInsert(orders)
          .AlsoInsert(order => order.Items);
          .Include(x => x.ThenMerge(order => order.Invoice)
                         .AlsoMerge(invoice => invoice.Items))
          .AlsoMerge(x => x.ShippingAddress);   

라이브러리는 여러 공급자를 지원합니다.

• SQL 서버
• SQL Compact
• 신탁
• MySql
• PostgreSQL
• SQLite
• 파이어 버드

---

대량 삽입을 매우 빠르게 수행 할 수있는 확장 메서드를 만들었습니다.

public static class DapperExtensions
{
    public static async Task BulkInsert<T>(
        this IDbConnection connection,
        string tableName,
        IReadOnlyCollection<T> items,
        Dictionary<string, Func<T, object>> dataFunc)
    {
        const int MaxBatchSize = 1000;
        const int MaxParameterSize = 2000;

        var batchSize = Math.Min((int)Math.Ceiling((double)MaxParameterSize / dataFunc.Keys.Count), MaxBatchSize);
        var numberOfBatches = (int)Math.Ceiling((double)items.Count / batchSize);
        var columnNames = dataFunc.Keys;
        var insertSql = $"INSERT INTO {tableName} ({string.Join(", ", columnNames.Select(e => $"[{e}]"))}) VALUES ";
        var sqlToExecute = new List<Tuple<string, DynamicParameters>>();

        for (var i = 0; i < numberOfBatches; i++)
        {
            var dataToInsert = items.Skip(i * batchSize)
                .Take(batchSize);
            var valueSql = GetQueries(dataToInsert, dataFunc);

            sqlToExecute.Add(Tuple.Create($"{insertSql}{string.Join(", ", valueSql.Item1)}", valueSql.Item2));
        }

        foreach (var sql in sqlToExecute)
        {
            await connection.ExecuteAsync(sql.Item1, sql.Item2, commandTimeout: int.MaxValue);
        }
    }

    private static Tuple<IEnumerable<string>, DynamicParameters> GetQueries<T>(
        IEnumerable<T> dataToInsert,
        Dictionary<string, Func<T, object>> dataFunc)
    {
        var parameters = new DynamicParameters();

        return Tuple.Create(
            dataToInsert.Select(e => $"({string.Join(", ", GenerateQueryAndParameters(e, parameters, dataFunc))})"),
            parameters);
    }

    private static IEnumerable<string> GenerateQueryAndParameters<T>(
        T entity,
        DynamicParameters parameters,
        Dictionary<string, Func<T, object>> dataFunc)
    {
        var paramTemplateFunc = new Func<Guid, string>(guid => $"@p{guid.ToString().Replace("-", "")}");
        var paramList = new List<string>();

        foreach (var key in dataFunc)
        {
            var paramName = paramTemplateFunc(Guid.NewGuid());
            parameters.Add(paramName, key.Value(entity));
            paramList.Add(paramName);
        }

        return paramList;
    }
}

Then to use this extension method, you would write code like the following:

await dbConnection.BulkInsert(
    "MySchemaName.MyTableName",
    myCollectionOfItems,
    new Dictionary<string, Func<MyObjectToInsert, object>>
        {
            { "ColumnOne", u => u.ColumnOne },
            { "ColumnTwo", u => u.ColumnTwo },
            ...
        });

This is quite primitive and has further room for improvement, such as passing in a transaction or a commandTimeout value but it does the trick for me.

---

the fastest variant for me:

            var dynamicParameters = new DynamicParameters();
            var selects = new List<string>();
            for (var i = 0; i < members.Length; i++)
            {
                var member = members[i];
                var pUsername = $"u{i}";
                var pIsActive = $"a{i}";
                dynamicParameters.Add(pUsername, member.Username);
                dynamicParameters.Add(pIsActive, member.IsActive);
                selects.Add("select @{pUsername},@{pIsActive}");
            }
            con.Execute($"insert into Member(Username, IsActive){string.Join(" union all ", selects)}", dynamicParameters);

which generate sql like:

INSERT TABLENAME (Column1,Column2,...)
 SELECT @u0,@a0...
 UNION ALL
 SELECT @u1,@a1...
 UNION ALL
 SELECT @u2,@a2...

this query works faster because sql adds set of rows instead adding 1 row at a time. The bottleneck is not writing the data, it's writing what you're doing in the log.

Also, look into the rules of minimally logged transactions.

참고URL : https://stackoverflow.com/questions/10689779/bulk-inserts-taking-longer-than-expected-using-dapper