Dapper batch query en lugar de una sola consulta ejecutada muchas veces

dapper mysql

Pregunta

Estoy tratando de optimizar algunas consultas, y tengo esta loca. La idea básica es conseguir un grupo de salas que tenga algunas reuniones correspondientes. Actualmente ejecuto una consulta para obtener todas las salas, luego foreach room Necesito las reuniones, donde realizo una consulta para cada sala. Esto abre una gran cantidad de conexiones de bases de datos (es decir, 1000 habitaciones cada una tiene que abrir una conexión para sacar las reuniones), y me gustaría hacerlo como un lote en su lugar. Estoy utilizando dapper para asignar mis consultas a los modelos y estoy tratando de usar los parámetros de la lista que se describen aquí

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

Sin entrar en detalles de la loca secuencia de unión larga, actualmente tengo que hacer esta consulta, mucho . Se ha escrito inicialmente como:

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

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

Que a su vez lo llama un montón de veces y ejecuta la consulta:

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

Obviamente, esto requiere bastantes conexiones a la base de datos, y me gustaría evitar esto haciendo que dapper realice múltiples consultas, pero obtengo el siguiente error si intento agregar los parámetros como una lista que se ve así:

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");

Entonces usaría la lista de parámetros así:

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

El error que recibo es:

dapper Información adicional: una secuencia enumerable de parámetros (matrices, listas, etc.) no está permitida en este contexto

Respuesta aceptada

En primer lugar, es posible reutilizar una única conexión para múltiples consultas, de modo que pueda recuperar todos sus datos con múltiples llamadas de "Consultas" de Dapper utilizando la misma conexión.

Algo como lo siguiente (que no es exactamente la misma consulta que mostraste ya que estaba probando esto en mi propia computadora con una base de datos local; sin embargo, debería ser fácil ver cómo se podría modificar para que funcione con tu consulta) -

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

Sin embargo, si realmente es el número de consultas que desea reducir a través del procesamiento por lotes, no hay nada en Dapper que lo haga muy fácil ya que cada parámetro debe tener un nombre único, lo que no será el caso si proporciona múltiples instancias de un tipo como el valor de "parámetros" (ya que habrá "n" valores de Id que se llaman todos "Id", por ejemplo).

Podría hacer algo un poco raro para producir una única cadena de consulta que arroje resultados de múltiples conjuntos de parámetros, como los siguientes:

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

.. pero esto se siente un poco sucio. Sin embargo, podría ser una opción para explorar si está absolutamente seguro de que realizar esas consultas una a una es un cuello de botella de rendimiento.

Otra cosa que debe tener en cuenta: cuando necesita los datos para 1000 identificadores diferentes, ¿las horas de inicio y fin son siempre las mismas para cada una de las 1000 consultas? Si es así, entonces posiblemente podría cambiar su consulta a lo siguiente:

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

Puede haber un problema con esto si lo llamas con un gran número de ID, debido a la forma en que Dapper convierte la cláusula IN, como se describe en https://stackoverflow.com/a/19938414/3813189 (donde alguien advierte contra usarlo con grandes conjuntos de valores).

Si ese enfoque falla, entonces podría ser posible hacer algo similar a la carga masiva de la tabla temporal que se sugiere aquí: https://stackoverflow.com/a/9947259/3813189 , donde obtiene todas las claves que desea datos en un tabla temporal y luego realizar una consulta que se une a esa tabla para las claves (y luego lo elimina nuevamente después de que tenga los datos).



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué