여러 번 실행 된 단일 쿼리 대신 Dapper 일괄 쿼리

dapper mysql

문제

일부 쿼리를 최적화하려고하는데이 미친 쿼리가 있습니다. 기본 아이디어는 몇 가지 대응하는 회의가있는 방을 잔뜩 얻는 것입니다. 나는 현재 모든 방을 가져 오기 위해 쿼리를 실행 한 다음 회의실을 가져야하는 foreach 방을보고 각 방의 쿼리를 수행합니다. 이것은 많은 데이터베이스 연결 (즉, 회의를 끌어 오기 위해 각각 연결을 열어야하는 1000 개의 방)을 열어 놓았습니다. 대신 배치로 사용하고 싶습니다. 내 쿼리를 모델에 맵핑하기 위해 dapper를 사용하고 있으며 여기에 설명 된 목록 매개 변수를 사용하려고합니다.

SELECT 
      mm.id,
      mm.organizer_name as Organizer,
      mm.subject as Subject,
      mm.start_time as StartTime,
      mm.end_time as EndTime,
      (mm.deleted_at IS NOT NULL) as WasCancelled,
      (am.interactive = 0 AND am.cancelled_at IS NOT NULL) as WasNoShow,
      c.name as name
FROM master_meeting mm
LEFT JOIN master_meeting__exchange mme ON mme.id=mm.id
LEFT JOIN master_meeting__forwarded_exchange mmfe ON mmfe.id=mm.id
LEFT JOIN meeting_instance__exchange mie ON mie.meeting_id=mm.id
LEFT JOIN meeting_instance__forwarded_exchange mife ON mife.meeting_id=mm.id
LEFT JOIN appointment_meta__exchange ame ON mie.item_id=ame.item_id
LEFT JOIN appointment_meta__exchange ame2 ON mife.item_id=ame2.item_id
LEFT JOIN appointment_meta am ON am.id=ame.id
LEFT JOIN appointment_meta am2 ON am2.id=ame2.id                
LEFT JOIN calendar c on mie.calendar_id=c.id
WHERE mie.calendar_id = @Id OR mife.calendar_id=@Id 
AND mm.start_time BETWEEN @StartTime AND @EndTime

미친 긴 조인 시퀀스에 대한 세부 사항을 살펴 보지 않고도 현재이 쿼리를 많이 수행해야합니다. 그것은 처음에 다음과 같이 작성되었습니다 :

List<Result> resultSet = new List<Result>();

foreach(int id in idList){
         resultSet.AddRange(
             _queryHandler.Handle(
                 new MeetingQuery(id, "FixedStartTime", "FixedEndTime")
             )
         );            
}

어느 차례 차례로 이것을 호출하고 쿼리를 실행합니다.

_connection.Query<Meeting>(sql, 
    new { 
        Id = query.id, 
        StartTime = query.StartTime, 
        EndTime = query.EndTime 
    }
);

이것은 분명히 꽤 많은 데이터베이스 연결을 필요로하며, 여러 쿼리를하는 dapper를 사용하는 것을 피하고 싶습니다. 그러나 다음과 같은 목록으로 매개 변수를 추가하려고하면 다음 오류가 발생합니다.

class Parameters {
    int Id;
    string StartTime;
    string EndTime;
}
List<Parameters> parameters = new List<Parameters>();
foreach(int id in idList)
     parameters.Add(new Parameters(id, "SameStartTime", "SameEndTime");

그런 다음 매개 변수 목록을 다음과 같이 사용합니다.

_connection.Query<Meeting>(sql,parameters);

내가 얻는 오류 :

dapper 추가 정보 :이 컨텍스트에서 열거 가능한 일련의 매개 변수 (배열, 목록 등)는 허용되지 않습니다.

수락 된 답변

첫째, 여러 쿼리에 대해 단일 연결을 재사용 할 수 있으므로 동일한 연결을 사용하여 여러 Dapper "Query"호출로 모든 데이터를 검색 할 수 있습니다.

다음과 같은 것 (이것은 로컬 데이터베이스가있는 내 컴퓨터에서 이것을 테스트 한 이후의 정확한 쿼리가 아니므로 쿼리와 함께 작동하는 방법을 쉽게 볼 수 있어야합니다) -

private static IEnumerable<Record> UnbatchedRetrieval(IEnumerable<Parameters> parameters)
{
    var allResults = new List<Record>();
    using (var conn = GetConnection())
    {
        foreach (var parameter in parameters)
        {
            allResults.AddRange(
                conn.Query<Record>(
                    "SELECT Id, Title FROM Posts WHERE Id = @id",
                    parameter
                )
            );
        }
    }
    return allResults;
}

public class Parameters
{
    public int Id { get; set; }
}

그러나 일괄 처리를 통해 줄이려는 쿼리의 개수가 실제로 많다면 Dapper에는 각 매개 변수가 고유하게 이름 지정되어야하므로 매우 쉽습니다. 제공하는 경우에는 그렇지 않습니다. (예를 들어 "Id"라고 불리는 "n"개의 Id 값이 있기 때문에) "매개 변수"값으로 유형의 여러 인스턴스를 생성 할 수 있습니다.

다음과 같이 여러 매개 변수 집합의 결과를 반환하는 단일 쿼리 문자열을 생성하기 위해 약간 해킹 할 수 있습니다.

private static IEnumerable<Record> BatchedRetrieval(IEnumerable<Parameters> parameters)
{
    using (var conn = GetConnection)
    {
        var select = "SELECT Id, Title FROM Posts";
        var where = "Id = {0}";

        var sqlParameters = new DynamicParameters();
        var combinedWheres =
            "(" +
            string.Join(
                ") OR (",
                parameters.Select((parameter, index) =>
                {
                    sqlParameters.Add("id" + index, parameter.Id);
                    return string.Format(where, "@id" + index);
                })
            ) +
            ")";

        return conn.Query<Record>(
            select + " WHERE " + combinedWheres,
            sqlParameters
        );
    }
}

public class Parameters
{
    public int Id { get; set; }
}

.. 그러나 이것은 약간 더러웠다. 하지만 이러한 쿼리를 하나씩 수행하는 것이 성능 병목 현상이라는 것을 확실히 알고 있다면 탐색하는 것이 좋습니다.

고려해야 할 또 다른 사항 - 1000 개의 서로 다른 ID에 대한 데이터가 필요한 경우 1000 개의 쿼리 각각에 대해 항상 시작 및 종료 시간이 동일합니까? 그렇다면 쿼리를 다음과 같이 변경할 수 있습니다.

private static IEnumerable<Record> EfficientBatchedRetrieval(
    IEnumerable<int> ids,
    DateTime startTime,
    DateTime endTime)
{
    using (var conn = GetConnection())
    {
        return conn.Query<Record>(
            @"SELECT 
                    mm.id,
                    mm.organizer_name as Organizer,
                    mm.subject as Subject,
                    mm.start_time as StartTime,
                    mm.end_time as EndTime,
                    (mm.deleted_at IS NOT NULL) as WasCancelled,
                    (am.interactive = 0 AND am.cancelled_at IS NOT NULL) as WasNoShow,
                    c.name as name
            FROM master_meeting mm
            LEFT JOIN master_meeting__exchange mme ON mme.id=mm.id
            LEFT JOIN master_meeting__forwarded_exchange mmfe ON mmfe.id=mm.id
            LEFT JOIN meeting_instance__exchange mie ON mie.meeting_id=mm.id
            LEFT JOIN meeting_instance__forwarded_exchange mife ON mife.meeting_id=mm.id
            LEFT JOIN appointment_meta__exchange ame ON mie.item_id=ame.item_id
            LEFT JOIN appointment_meta__exchange ame2 ON mife.item_id=ame2.item_id
            LEFT JOIN appointment_meta am ON am.id=ame.id
            LEFT JOIN appointment_meta am2 ON am2.id=ame2.id
            LEFT JOIN calendar c on mie.calendar_id=c.id
            WHERE mie.calendar_id IN @Ids OR mife.calendar_id IN @Ids
            AND mm.start_time BETWEEN @StartTime AND @EndTime",
            new { Ids = ids, StartTime = startTime, EndTime = endTime }
        );
    }
}

하지만 Dapper가 IN 절을 변환하는 방식 때문에 많은 수의 ID를 사용하여 호출하면이 문제가 발생할 수 있습니다 ( https://stackoverflow.com/a/19938414/3813189 (누군가 경고하는 곳)). 큰 값 집합을 사용하는 것에 비해).

이 방법이 실패하면 여기에 제안 된 임시 테이블 대량로드와 비슷한 작업을 수행 할 수 있습니다. https://stackoverflow.com/a/9947259/3813189 여기에서 데이터를 원하는 모든 키를 얻을 수 있습니다. 임시 테이블을 만든 다음 키에 대해 해당 테이블에 조인하는 쿼리를 수행 한 다음 데이터가있는 경우 다시 삭제합니다.



아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.