operator - Manera eficiente de garantizar filas únicas en SQLite3

sqlite substring

Question

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 sentido, pero en el 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. Thefore esto:

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

se convirtió 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);

De hecho, esto impone la restricción de singularidad cuando lo necesito. Desafortunadamente, este cambio también incurre en una penalización de rendimiento mucho mayor de lo que esperaba. Hice algunas pruebas con la utilidad de línea de comandos sqlite3 para asegurarme de 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 al menos 1,000 filas y me sorprendió la caída de 15 veces en el rendimiento. Esperaba como máximo 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 la transacción de 100k. Supongo que la indexación implicada en el mantenimiento de las restricciones de clave primaria requiere un número significativamente mayor de operaciones DB sincrónicas, lo que hace que mis discos duros sean el cuello de botella en este caso.

El uso del modo WAL tiene algún efecto, un aumento del rendimiento de aproximadamente 15%. Lamentablemente, eso no es suficiente por sí solo. PRAGMA synchronous = NORMAL no parece tener ningún efecto.

Es posible que pueda recuperar algo de rendimiento al aumentar 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 fiabilidad.

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 (se supone que es) portátil para al menos Linux y Windows.

¿Hay alguna manera de mejorar el rendimiento de inserción sin aumentar el tamaño de la transacción? ¿Alguna configuración en SQLite (algo que no sea forzar permanentemente el DB en la operación asincrónica, eso es) o programáticamente en mi código de aplicación? Por ejemplo, ¿hay alguna manera de garantizar la singularidad de la fila sin usar un índice?

GENEROSIDAD:

Al usar el método de hash / indexación descrito en mi propia respuesta, logré moderar de algún modo 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 más lentas y lentas.

Estoy interesado en cualquier configuración técnica o de ajuste que aumente el rendimiento en este caso de uso particular, siempre que no implique hackear el código SQLite3 o que de otra manera el proyecto quede inutilizable.

Answer 1

(Normalmente, no respondo mis propias preguntas, pero me gustaría documentar algunas ideas / soluciones parciales para esto).

El principal problema con una clave primaria compuesta es la forma en que se manejan los índices. Las claves compuestas implican un índice en el valor compuesto, que en mi caso significa cadenas de indexación. Si bien la comparación de valores de cadena no es tan lenta, la indexación de un valor con una longitud de, digamos, 500 bytes significa que los nodos del árbol B en el índice pueden acomodar muchos menos punteros de fila / nodo que un árbol B que indexa un 64- valor entero de bit. Esto significa cargar muchas más páginas DB para cada búsqueda de índice, a medida que aumenta la altura del árbol B.

Para tratar con este problema, modifiqué mi código para que:

• Utiliza el modo WAL . El aumento en el rendimiento ciertamente valió un cambio tan pequeño, ya que no tengo ningún problema con que el archivo DB no sea autónomo.

• Utilicé la función hash MurmurHash3 , después de volver a escribirla en C y adaptarla, para producir un solo valor hash de 32 bits a partir de los valores de los campos que formarían la clave. Guarde este hash en una nueva columna indexada . Como este es un valor entero, el índice es bastante rápido. Este es el único índice para esta tabla. Dado que habrá como máximo 10.000.000 filas en la tabla, las colisiones hash no serán un problema de rendimiento, aunque realmente no puedo considerar que el valor hash sea UNIQUE , el índice solo devolverá una sola fila en el caso general.

En este punto, hay dos alternativas que he codificado y que actualmente están en fase de prueba:

• DELETE FROM Event WHERE Hash=? AND Fld0=? AND Fld2=? AND Fld3=? , seguido de un INSERT .

• UPDATE Event SET Fld1=?,... WHERE Hash=? AND Fld0=? AND Fld2=? AND Fld3=? , seguido de un INSERT si no hay filas donde se actualizó.

Espero que la segunda alternativa sea más rápida, pero primero tendré que completar la prueba. En cualquier caso, parece que con estos cambios la disminución del rendimiento (en comparación con la tabla sin índice original) se ha reducido a un factor de 5 o más, que es mucho más manejable.

EDITAR:

En este punto, me he conformado con usar la segunda variación, que de hecho es un poco más rápida. Parece, sin embargo, que cualquier tipo de índice ralentiza SQLite3 dramáticamente a medida que la tabla indexada se hace más grande. Aumentar el tamaño de la página DB a 8192 bytes parece ayudar un poco, pero no tan drásticamente como me gustaría.

Answer 2

Además de todas las otras excelentes respuestas, una cosa que puede hacer es dividir los datos en varias tablas.

Los INSERT de SQLite se vuelven cada vez más lentos a medida que aumenta el número de filas, pero si puedes dividir una tabla en varias, ese efecto disminuye (por ejemplo: "nombres" -> "nombres_a", "nombres_b", ... para nombres que empiezan por la letra x ). Más adelante, puede CREATE VIEW "names" AS SELECT * FROM "names_a" UNION SELECT * FROM "names_b" UNION ...

Answer 3

He utilizado sqlite para insertar millones de filas en tiempo de ejecución y esto es lo que he usado para aumentar el rendimiento:

• Use la menor cantidad de transacciones posible.
• Use comandos parametrizados para insertar los datos (prepare el comando una vez y simplemente cambie los valores del parámetro en el ciclo)
• Establezca PRAGMA síncrono en OFF (no estoy seguro de cómo funciona con WAL)
• Aumentar el tamaño de página de la base de datos.
• Incrementa el tamaño de la caché. Esta es una configuración importante, ya que hará que sqlite realmente escriba los datos en el disco menos veces y ejecutará más operaciones en la memoria, acelerando todo el proceso.
• Si necesita un índice, agréguelo después de insertar las filas ejecutando el comando sqlite necesario. En este caso, deberá asegurarse de ser único, ya que lo está haciendo ahora.

Si prueba esto, publique los resultados de su prueba. Creo que será interesante para todos.

Answer 4

La cláusula ON CONFLICT REPLACE hará que SQLite elimine filas existentes, luego inserte nuevas filas. Eso significa que SQLite probablemente va a pasar parte de su tiempo

• borrando filas existentes
• actualizar los índices
• insertando nuevas filas
• actualizar los índices

Esa es mi opinión, basada en la documentación SQLite y la lectura sobre otros sistemas de administración de bases de datos. No miré el código fuente.

SQLite tiene dos formas de expresar restricciones de exclusividad: PRIMARY KEY y UNIQUE . Ambos crean un índice, sin embargo.

Ahora lo realmente importante. . .

Es genial que hicieras pruebas. La mayoría de los desarrolladores no hacen eso. Pero creo que los resultados de su prueba son muy engañosos.

En su caso, no importa cuán rápido pueda insertar filas en una tabla que no tenga una clave principal. Una tabla que no tiene una clave principal no satisface sus requisitos básicos de integridad de datos. Eso significa que no puede confiar en su base de datos para darle las respuestas correctas.

Si no tiene que dar las respuestas correctas, puedo hacerlo realmente, muy rápido.

Para obtener un tiempo significativo para insertar en una tabla que no tiene clave, necesita

• ejecutar el código antes de insertar datos nuevos para asegurarse de que no infringe la restricción de clave primaria no declarada, y asegurarse de actualizar las filas existentes con los valores correctos (en lugar de insertar), o
• Ejecuta el código después de insertarlo en esa tabla para limpiar los duplicados en (Fld0, Fld2, Fld3) y para reconciliar los conflictos

Y, por supuesto, también debe tenerse en cuenta el tiempo que toman esos procesos.

FWIW, realicé una prueba ejecutando 100K SQL insert statements en su esquema en transacciones de 1000 declaraciones, y solo tomó 30 segundos. Una sola transacción de 1000 instrucciones de inserción, que parece ser lo que espera en producción, tomó 149 mseg.

Tal vez pueda acelerar las cosas insertando en una tabla temporal no rayada, y luego actualizando la tabla codificada a partir de eso.

Answer 5

Case When Exists((Select ID From Table Where Fld0 = value0 and Fld2 = value1 and Fld3 = value 2)) Then
    --Insert Statement
End

No estoy al 100% de que la inserción funcione así en SQLite, pero creo que debería. Esto con una indexación adecuada en los campos Where debería ser bastante rápido. Sin embargo, estas son dos transacciones que es algo a considerar.