estou usandoSQLite3 em um de meus projetos e preciso garantir que as linhas inseridas em uma tabela sejam exclusivas em relação a uma combinação de algumas de suas colunas. Na maioria dos casos, as linhas inseridas diferem nesse aspecto, mas, no caso de uma correspondência, a nova linha deve atualizar / substituir a existente.

A solução óbvia foi usar uma chave primária composta, com uma cláusula de conflito para lidar com colisões. Portanto, isso:

CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT);

tornou-se este:

CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE);

Isso realmente reforça a restrição de exclusividade, conforme eu preciso. Infelizmente, essa alteração também incorre em uma penalidade de desempenho que está muito além do que eu esperava. Eu fiz alguns testes usando osqlite3 utilitário de linha de comando para garantir que não haja uma falha no restante do meu código. Os testes envolvem a inserção de 100.000 linhas, em uma única transação ou em 100 transações de 1.000 linhas cada. Eu obtive os seguintes resultados:

                                | 1 * 100,000   | 10 * 10,000   | 100 * 1,000   |
                                |---------------|---------------|---------------|
                                | Time  | CPU   | Time  | CPU   | Time  | CPU   |
                                | (sec) | (%)   | (sec) | (%)   | (sec) | (%)   |
--------------------------------|-------|-------|-------|-------|-------|-------|
No primary key                  | 2.33  | 80    | 3.73  | 50    | 15.1  | 15    |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld3               | 5.19  | 84    | 23.6  | 21    | 226.2 | 3     |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld2, Fld3         | 5.11  | 88    | 24.6  | 22    | 258.8 | 3     |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld0, Fld2, Fld3   | 5.38  | 87    | 23.8  | 23    | 232.3 | 3     |

Atualmente, meu aplicativo realiza transações de no máximo 1.000 linhas e fiquei surpreso com a queda de 15 vezes no desempenho. Eu esperava no máximo uma queda de três vezes na taxa de transferência e um aumento no uso da CPU, como visto no caso de 100 mil transações. Acho que a indexação envolvida na manutenção das restrições da chave primária requer um número significativamente maior de operações síncronas do banco de dados, tornando meus discos rígidos o gargalo nesse caso.

UsandoModo WAL tem algum efeito - um aumento de desempenho de cerca de 15%. Infelizmente, isso não basta por si só.PRAGMA synchronous = NORMAL não pareceu ter nenhum efeito.

I poderia conseguir recuperar algum desempenho aumentando o tamanho da transação, mas prefiro não fazer isso, devido ao aumento do uso de memória e às preocupações com capacidade de resposta e confiabilidade.

Os campos de texto em cada linha têm comprimentos variáveis de cerca de 250 bytes em média. O desempenho da consulta não importa muito, mas o desempenho da inserção é muito importante. Meu código de aplicativo está em C e é (supostamente) portátil para pelo menos Linux e Windows.

Existe uma maneira de melhorar o desempenho da pastilha sem aumentar o tamanho da transação? Alguma configuração no SQLite (qualquer coisa, mas forçar permanentemente o banco de dados em operação assíncrona) ou programaticamente no código do meu aplicativo? Por exemplo, existe uma maneira de garantir a exclusividade da linha sem usar um índice?

RECOMPENSA:

Usando o método de hash / indexação descrito em minha própria resposta, consegui moderar um pouco a queda de desempenho a um ponto em que provavelmente é aceitável para o meu aplicativo. Parece, no entanto, que à medida que o número de linhas na tabela aumenta, a presença do índice torna as inserções cada vez mais lentas.

Estou interessado em qualquer configuração técnica ou de ajuste fino que aumentará o desempenho nesse caso de uso específico, desde que não envolva a invasão do código SQLite3 ou faça com que o projeto se torne impossível de manter.