Estoy usando SQLite3 en uno de mis proyectos y necesito asegurarme de que las filas que se insertan en una tabla sean únicas con respecto a una combinación de algunas de sus columnas. En la mayoría de los casos, las filas insertadas diferirán en ese aspecto, pero en caso de una coincidencia, la nueva fila debe actualizar / reemplazar la existente.

La solución obvia era usar una clave primaria compuesta, con una cláusula de conflicto para manejar las colisiones. Por lo tanto esto:

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

porque esto:

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

Esto sí impone la restricción de unicidad como la necesito. Desafortunadamente, este cambio también incurre en una penalización de rendimiento que va mucho más allá de lo que esperaba. Hice algunas pruebas usando elsqlite3 utilidad de línea de comandos para garantizar que no haya un error en el resto de mi código. Las pruebas implican ingresar 100,000 filas, ya sea en una sola transacción o en 100 transacciones de 1,000 filas cada una. Obtuve los siguientes 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     |

Mi aplicación actualmente realiza transacciones de un máximo de 1,000 filas y me sorprendió la caída de 15 veces en el rendimiento. Esperaba a lo sumo una caída de 3 veces en el rendimiento y un aumento en el uso de la CPU, como se ve en el caso de las transacciones de 100k. Supongo que la indexación involucrada en el mantenimiento de las restricciones de la clave principal requiere un número significativamente mayor de operaciones de base de datos síncronas, lo que hace que mis discos duros sean el cuello de botella en este caso.

Utilizando Modo WAL tiene algún efecto: un aumento del rendimiento de aproximadamente el 15%. Lamentablemente, eso no es suficiente por sí solo. @PRAGMA synchronous = NORMAL no parecía tener ningún efecto.

I podrí poder recuperar algo de rendimiento aumentando el tamaño de la transacción, pero prefiero no hacerlo, debido al mayor uso de memoria y las preocupaciones sobre la capacidad de respuesta y la confiabilidad.

Los campos de texto en cada fila tienen longitudes variables de aproximadamente 250 bytes en promedio. El rendimiento de la consulta no importa demasiado, pero el rendimiento de la inserción es muy importante. Mi código de aplicación está en C y es (se supone que es) portátil al menos para Linux y Windows.

¿Hay alguna forma de mejorar el rendimiento de la inserción sin aumentar el tamaño de la transacción? ¿Alguna configuración en SQLite (cualquier cosa menos forzar permanentemente el DB a una operación asincrónica) o programáticamente en mi código de aplicación? Por ejemplo, ¿hay alguna manera de garantizar la unicidad de fila sin usar un índice?

GENEROSIDAD

Al utilizar el método de hashing / indexación descrito en mi propia respuesta, logré moderar un poco la caída del rendimiento hasta un punto en el que probablemente sea aceptable para mi aplicación. Sin embargo, parece que a medida que aumenta el número de filas en la tabla, la presencia del índice hace que las inserciones sean cada vez más lentas.

stoy interesado en cualquier técnica o ajuste de ajuste que aumente el rendimiento en este caso de uso en particular, siempre y cuando no implique piratear el código SQLite3 o de lo contrario haga que el proyecto sea imposible de mantener.