A melhor forma de armazenar e recuperar dados relacionados com o Dapper

In english? Go

Pra começo de conversa, eu preciso deixar algo bem transparente: eu sou "lazy". E em inglês assim fica mais palatável do que a tradução, então é "lazy" mesmo. Sabendo disso, eu preciso revelar um segredo; se tem uma coisa que eu não gosto de fazer é escrever consulta ou comandos em sql. Normalmente as estruturas estão todas lindas e maravilhosas no meu imaginário, e quando eu preciso colocá-las no código, com um monte de "wheres" e "joins", já sinto vertigem. Por causa disso, eu tenho sido um usuário muito assíduo do ChatGpt, que tem me ajudado bastante fazendo a parte entediante.
E o que isso tudo tem a ver com esse artigo? Eu comecei a explorar formas diferentes de armazenar e recuperar dados de objetos ou relacionamentos complexos usando C#, Dapper e Sql Server, e eu vou retratar o resumo bem resumido dessas experiências.

Objetos e Relacionamentos

Para começar esses testes, precisamos de alguns objetos que possuam ao menos um relacionamento de um para muitos entre si, afinal, o objetivo desse estudo é descobrir a forma mais eficiente de trazer esse relacionamento montado em tempo de execução. Como atualmente no meu dia-a-dia eu trabalho muito com a parte financeira, sugiro uma conta e seus parcelamentos. Assim:

public class Account
{
    public Guid Id { get; set; }
    public string Description { get; set; } 
    public decimal TotalValue { get; set; }
    public List<Installment> Installments { get; set; } 
}

public class Installment
{
    public Guid Id { get; set; }
    public DateTime DueDate { get; set; }
    public decimal Value { get; set; }
    public Guid AccountId { get; set; }
}

É muito comum que objetos nessa configuração sejam armazenados num banco de dados relacional cada um em sua tabela com chaves estrangeiras servindo de referência, aka relacionamento. Segue o exemplo de SQL que usei:

 CREATE TABLE Accounts
  (
     Id          UNIQUEIDENTIFIER PRIMARY KEY,
     Description NVARCHAR(255) NOT NULL,
     TotalValue  DECIMAL(18, 2) NOT NULL
  ); 

CREATE TABLE Installments
  (
     Id        UNIQUEIDENTIFIER PRIMARY KEY,
     DueDate   DATETIME NOT NULL,
     Value     DECIMAL(18, 2) NOT NULL,
     AccountId UNIQUEIDENTIFIER NOT NULL,
     FOREIGN KEY(AccountId) REFERENCES Accounts(Id)
  ); 

Abordagens mais comuns

A forma mais comum na qual já me deparei para recuperar esse tipo de informação é usando uma única consulta sql com join e então realizar um mapeamento, que no meu caso é de parcelas para suas contas.

public const string ClassicQuery = @"
    SELECT acc.*, i.*  FROM Accounts acc
    inner join Installments i ON i.AccountId = acc.Id";

public List<Account> GetAllAccounts()
{
    var lookup = new Dictionary<Guid, Account>();

    _ = sqlConnection.Query<Account, Installment, Account>(SqlConstants.ClassicQuery,
        (acc, ins) =>
        {
            if (!lookup.TryGetValue(acc.Id, out var accEntry))
            {
                accEntry = acc;
                accEntry.Installments ??= new List<Installment>();
                lookup.Add(acc.Id, accEntry);
            }

            accEntry.Installments.Add(ins);
            return accEntry;

        }, splitOn: "Id");

    return lookup.Values.AsList();
}

Breves explicações: 1. Para quem já está habituado com o funcionamento do sql, sabe que esse tipo de consulta gera o produto cartesiano das tabelas, então se faz necessário o auxílio de uma tabela 'lookup' durante o mapeamento. Experimente fazer sem isso e você acabará com contas duplicadas e relacionamentos de um pra um. 2. Eu não tenho certeza absoluta, mas acredito que o compilador do C# é capaz de perceber que uma string hardcoded pode ser otimizada como constante ou estática, mas eu preferi já deixar explícito para não ter dúvidas, já que logo menos teremos testes de benchmark disso, e eu não quero que alocações de strings atrapalhem os resultados. 3. O método AsList() vem do Dapper e considera que, caso um enumerável já seja implementado como uma List<T>, ele reaproveita essa lista ao invés de alocar outra, como aconteceria se usasse o ToList().

Outra forma muito comum é usar múltiplas vezes o comando .Read<T>() em cima do resultado de um .QueryMultiple(). Assim:

public const string MultipleQuery = @"
    SELECT * FROM Accounts;
    SELECT * FROM Installments;";

public List<Account> GetAllAccounts()
{
    var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);

    var acc = res.Read<Account>();
    var installments = res.Read<Installment>();

    foreach (var account in acc)
    {
        account.Installments = installments.Where(i => i.AccountId == account.Id).ToList();
    }

    return acc.AsList();
}

O código é bem simples e direto, não tem nenhum mágica. Até o momento, eu nunca havia questionado a performance, usabilidade ou legibilidade disso e de fato, no mundo real, nunca houve um requisito para tentar aprimorar nada nesse sentido. Mas como eu sou 'lazy' eu comecei a enjoar de escrever esse tipo de código várias vezes.

E se...Json?

Pra quem já se aventurou em bancos de dados não-relacionais, como MongoDb ou Azure Cosmos, sabe que dá pra armazenar esse objeto todo como um grande e magnífico json. E se a gente analisar friamente, podemos tentar misturar um pouco de json no banco relacional, porque não, né? Todas as engines mainstream, incluindo o Sql Server, já possuem diretrizes para lidar com json nas tabelas, seja listar, inserir, filtrar, e várias outras atividades comuns em bancos de dados. No trecho de código abaixo, represento o relacionamento de um para muitos em forma de json e uso um recurso de mapeamento personalizado do Dapper para ler e gravar automaticamente os parcelamentos das contas nesse formato.

public const string CreateAccountsJson = @"
    CREATE TABLE AccountsJson(
        Id UNIQUEIDENTIFIER PRIMARY KEY,
        Description NVARCHAR(255) NOT NULL,
        TotalValue DECIMAL(18,2) NOT NULL,
        Installments NVARCHAR(MAX) NULL
    );";


public class AccountJson
{
    public Guid Id { get; set; }
    public string Description { get; set; } 
    public decimal TotalValue { get; set; }
    public List<InstallmentJson> Installments { get; set; } 
}

public struct InstallmentJson
{
    public DateTime DueDate { get; set; }
    public decimal Value { get; set; }
}


public class InstallmentJsonTypeMapper 
    : SqlMapper.TypeHandler<List<InstallmentJson>>
{
    public override List<InstallmentJson> Parse(object value)
    {
        if (value is null) return new List<InstallmentJson>();

        var json = value.ToString();

        if (string.IsNullOrWhiteSpace(json)) return new List<InstallmentJson>();

        var res = JsonSerializer.Deserialize<List<InstallmentJson>>(json);

        if (res is null) return new List<InstallmentJson>();

        return res;
    }

    public override void SetValue(IDbDataParameter parameter, 
                                  List<InstallmentJson> value)
    {
        parameter.Value = value is null ? null : JsonSerializer.Serialize(value);
    }
}

Funcionar eu sei que funciona, mas será que presta? Será que dá pra usar em produção e se sentir seguro? Vamos para um benchmark, usando BenchmarkDotNet. Populei um banco de dados local com 10 contas com 10 parcelamentos cada. E os resultados foram:

|     Method      | Accounts | Installments |   Mean    |  Gen0   |  Gen1  | Allocated  |
|-----------------|----------|--------------|----------:|--------:|-------:|-----------:|
|  ClassicQuery   |    10    |      1       | 153.5 us |  1.4648 |   -    |   9.38 KB  |
| MultipleQuery   |    10    |      1       | 135.7 us |  1.4648 |   -    |  10.16 KB  |
|    JsonQuery    |    10    |      1       | 146.0 us |  2.1973 |   -    |  14.51 KB  |
|  ClassicQuery   |    10    |      5       | 287.4 us |  3.9063 |   -    |  26.34 KB  |
| MultipleQuery   |    10    |      5       | 162.9 us |  2.9297 |   -    |  19.32 KB  |
|    JsonQuery    |    10    |      5       | 182.8 us |  5.1270 |   -    |  32.47 KB  |
|  ClassicQuery   |    10    |     10       | 376.6 us |  7.8125 |   -    |  48.88 KB  |
| MultipleQuery   |    10    |     10       | 204.1 us |  4.8828 |   -    |  31.19 KB  |
|    JsonQuery    |    10    |     10       | 214.3 us |  9.0332 | 0.2441 |  56.28 KB  |

Sinceramente eu estava esperando algo bem pior para a versão que usa json. A alocação de memória ficou mais alta do que as demais mas o tempo de execução se equiparou ao QueryMultiple(). Para mim isso já está se tornando um bom negócio.
Também já podemos parar de considerar a abordagem clássica. Ponderando entre alocação e tempo, ela foi a pior.

E se...estrutura de dados?

Vocês notaram uma pegadinha? O teste MultipleQuery está usando Linq e uma forma bem simplista para mapear as parcelas das contas. Poderíamos usar um artíficio de lookup igual ao que foi usado na abordagem clássica. Veja:

public List<Account> GetAllAccounts()
    {
        var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);

        var accounts = res.Read<Account>(buffered: true).AsList();
        var installments = res.Read<Installment>();

        var lookup = new Dictionary<Guid, int>();

        for (int i = 0; i < accounts.Count; i++)
        {
            lookup.Add(accounts[i].Id, i);
        }

        foreach (var installment in installments)
        {
            if (lookup.TryGetValue(installment.AccountId, out int i))
            {
                accounts[i].Installments.Add(installment);
            }
        }

        return accounts;
    }

Se a fama das estruturas de dados fizerem jus, essa versão será a melhor de todas, então, vamos aos testes.

|       Method       | Accounts | Installments |     Mean     |   Allocated  |
|:------------------:|:--------:|:------------:|------------:|------------:|
|   MultipleQuery    |     1    |      10      |    129.8 us |     6.18 KB  |
|     JsonQuery      |     1    |      10      |    121.9 us |     7.63 KB  |
|    MultipleLookup  |     1    |      10      |    121.8 us |     6.22 KB  |
|   MultipleQuery    |     1    |     100      |    188.2 us |     26.7 KB  |
|     JsonQuery      |     1    |     100      |    204.3 us |    48.57 KB  |
|    MultipleLookup  |     1    |     100      |    190.7 us |    26.74 KB  |
|   MultipleQuery    |    10    |      10      |    223.5 us |    31.19 KB  |
|     JsonQuery      |    10    |      10      |    236.4 us |    56.34 KB  |
|    MultipleLookup  |    10    |      10      |    197.2 us |    30.38 KB  |
|   MultipleQuery    |    10    |     100      |    720.0 us |   232.26 KB  |
|     JsonQuery      |    10    |     100      |  1,256.1 us |   465.83 KB  |
|    MultipleLookup  |    10    |     100      |    719.4 us |   231.43 KB  |
|   MultipleQuery    |   100    |      10      |  1,318.5 us |   276.86 KB  |
|     JsonQuery      |   100    |      10      |  1,244.9 us |   541.36 KB  |
|    MultipleLookup  |   100    |      10      |    751.1 us |   269.34 KB  |
|   MultipleQuery    |   100    |     100      | 13,786.4 us |  2386.42 KB  |
|     JsonQuery      |   100    |     100      | 12,036.7 us |  4635.98 KB  |
|    MultipleLookup  |   100    |     100      |  8,879.9 us |  2379.31 KB  |

Para ajudar a ler os resultados, precisamos entender que, apesar de números como 100 parcelas por conta não seja um caso muito realista, podemos considerar que em outro contexto um objeto pode ter uma centena de filhos em sua hierarquia. E é isso que torna o teste mais interessante.
De modo geral, o método MultipleLookup brilhou, e isso me traz uma certa tristeza, por ter que aceitar que preciso me preocupar mais com sql para garantir uma performance agradável.
Mas como todo bom 'lazy', eu não desisto facilmente de tentar fazer sempre o menos possível.

E se...SPANS?

Já faz um bom tempo em que Span<T> é usado para otimizar rotinas de alguma forma, e o que eu vou propor aqui é uma abordagem sinistra totalmente incomum mas que pode render bons frutos. Os objetos relacionados do caso em estudo são representados por uma coleção, e portanto elegível para se tornar um Span<T>.
A dúvida que fica então é como converter um conjunto de parcelas em uma Span de parcelas e salvar no banco de dados, e a resposta para isso pode estar em dados binários, veja:

public const string CreateAccountsSpan = @"
      CREATE TABLE AccountsSpan(
          Id UNIQUEIDENTIFIER PRIMARY KEY,
          Description NVARCHAR(255) NOT NULL,
          TotalValue DECIMAL(18,2) NOT NULL,
          Installments VARBINARY(MAX) NULL
      );";

public class InstallmentSpanTypeMapper 
    : SqlMapper.TypeHandler<List<InstallmentSpan>>
{
    public override List<InstallmentSpan> Parse(object value)
    {
        if (value is not byte[] bytes)
        {
            return new List<InstallmentSpan>();
        }

        var span = bytes.AsSpan();
        var structSpan = MemoryMarshal.Cast<byte, InstallmentSpan>(span);
        return  structSpan.ToArray().ToList();       
    }

    public override void SetValue(IDbDataParameter parameter,
                                  List<InstallmentSpan> value)
    {
        var s = CollectionsMarshal.AsSpan(value);
        Span<byte> span = MemoryMarshal.AsBytes(s);
        parameter.Value = span.ToArray();
    }
}

É no mínimo estranho, mas funciona. Se a coleção de parcelas na conta fosse um Installment[] ao inves de List<Installment> seria possível evitar o .ToArray().ToList(). Deve haver alguma forma de converter direto para lista, mas fui 'lazy'.
Vamos aos testes?

|         Method | Accounts | Installments |        Mean |  Allocated |
|--------------- |--------- |------------- |------------:|-----------:|
|      JsonQuery |        1 |           10 |    127.2 us |    7.64 KB |
|      SpanQuery |        1 |           10 |    107.7 us |    3.55 KB |
| MultipleLookup |        1 |           10 |    124.9 us |    6.23 KB |
|      JsonQuery |        1 |          100 |    205.9 us |   48.57 KB |
|      SpanQuery |        1 |          100 |    113.9 us |      12 KB |
| MultipleLookup |        1 |          100 |    188.0 us |   26.73 KB |
|      JsonQuery |       10 |           10 |    211.9 us |    56.2 KB |
|      SpanQuery |       10 |           10 |    124.5 us |   15.34 KB |
| MultipleLookup |       10 |           10 |    197.9 us |   30.45 KB |
|      JsonQuery |       10 |          100 |  1,237.3 us |  465.74 KB |
|      SpanQuery |       10 |          100 |    192.0 us |   99.84 KB |
| MultipleLookup |       10 |          100 |    674.6 us |  231.42 KB |
|      JsonQuery |      100 |           10 |  1,207.0 us |  541.52 KB |
|      SpanQuery |      100 |           10 |    268.6 us |  132.97 KB |
| MultipleLookup |      100 |           10 |    747.2 us |  269.28 KB |
|      JsonQuery |      100 |          100 | 11,859.5 us | 4636.01 KB |
|      SpanQuery |      100 |          100 |  1,234.6 us |  976.76 KB |
| MultipleLookup |      100 |          100 |  9,424.9 us | 2379.24 KB |

Agora sim! É bem rápido e aloca muito menos memória que os demais, porém tem uma desvantagem, se alguém precisar ler o banco de dados através de alguma ferramenta como o Sql Server Management Studio, não vai conseguir ver o que tem lá e se precisar de algum filtro, também não será possível (eu só acho).

Conclusão

Eu gostei demais da versão com Span<T> pelo fato de simplificar a consulta ao banco de dados. Por outro lado não sei até que ponto ela seria realmente útil num cenário real, com objetos reais que possuem muitas outras propriedades ou ainda com uma hierarquia mais aninhada. Valeu a pena investigar porque agora é mais uma porta aberta. A versão com json é prática, em versões mais recentes do Sql Server já é possível consultar e manipular json diretamente no banco de dados, então acho válida em casos onde um objeto muito complexo precisa ser armazenado e sempre será usado por completo e não existe esforço para adicionar um banco de dados NoSql na stack da equipe. No final, podemos refletir a respeito do fato de que, normalmente, não escrevemos uma nova tabela e nem um novo repositório no código a cada hora, então, minha sugestão é ficar com o QueryMultiple, aplicar uma estrutura de dados boa para cada caso ao mapear os objetos, e se realmente precisar otimizar algo, experimente essa versão com spans em binários no banco de dados e não se decida antes de rodar um benchmark.

Quer o código? Vem aqui.

PS(s):

1. O ambiente de testes usou uma conexão com banco de dados local para reduzir ao máximo o tempo de rede, e estava com essas configurações: BenchmarkDotNet=v0.13.5, OS=Windows 11 (10.0.22621.1848/22H2/2022Update/SunValley2) Intel Core i7-8700 CPU 3.20GHz (Coffee Lake), 1 CPU, 12 logical and 6 physical cores .NET SDK=7.0.300-preview.23122.5 [Host] : .NET 6.0.14 (6.0.1423.7309), X64 RyuJIT AVX2 DefaultJob : .NET 6.0.14 (6.0.1423.7309), X64 RyuJIT AVX2 Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)
2. Eu não sou lazy de verdade, quem é lazy é o personagem do artigo. Casos de performance do Dapper já foram amplamente testados, o que eu só queria mesmo era mostrar essa gambiarra com spans no banco de dados.
3. Não foi nenhuma IA que me deu essa ideia de usar spans, pensei nisso sozinho :)