sql database performance sqlite sqlite3

SQLite: COUNT lento en tablas grandes



select count(*) (8)

Tengo un problema de rendimiento en SQLite con un SELECT COUNT (*) en tablas grandes.

Como aún no recibí una respuesta útil e hice algunas pruebas adicionales, edité mi pregunta para incorporar mis nuevos hallazgos.

Tengo 2 mesas:

CREATE TABLE Table1 ( Key INTEGER NOT NULL, ... several other fields ..., Status CHAR(1) NOT NULL, Selection VARCHAR NULL, CONSTRAINT PK_Table1 PRIMARY KEY (Key ASC)) CREATE Table2 ( Key INTEGER NOT NULL, Key2 INTEGER NOT NULL, ... a few other fields ..., CONSTRAINT PK_Table2 PRIMARY KEY (Key ASC, Key2 ASC))

Table1 tiene alrededor de 8 millones de registros y Table2 tiene alrededor de 51 millones de registros, y el archivo de base de datos tiene más de 5 GB.

Table1 tiene 2 índices más:

CREATE INDEX IDX_Table1_Status ON Table1 (Status ASC, Key ASC) CREATE INDEX IDX_Table1_Selection ON Table1 (Selection ASC, Key ASC)

El campo "Estado" es obligatorio, pero tiene solo 6 valores distintos, "Selección" no es obligatorio y tiene solo alrededor de 1.5 millones de valores diferentes de nulos y solo alrededor de 600k valores distintos.

Hice algunas pruebas en ambas tablas, puede ver los horarios a continuación, y agregué el "plan de consulta de explicación" para cada solicitud (QP). Coloqué el archivo de la base de datos en una memoria USB para poder eliminarlo después de cada prueba y obtener resultados confiables sin interferencia en la memoria caché del disco. Algunas solicitudes son más rápidas en USB (supongo que debido a la falta de tiempo de búsqueda), pero algunas son más lentas (exploraciones de tabla).

SELECT COUNT(*) FROM Table1 Time: 105 sec QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows) SELECT COUNT(Key) FROM Table1 Time: 153 sec QP: SCAN TABLE Table1 (~1000000 rows) SELECT * FROM Table1 WHERE Key = 5123456 Time: 5 ms QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1 Time: 16 sec QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows) SELECT * FROM Table1 WHERE Selection = ''SomeValue'' AND Key > 5123456 LIMIT 1 Time: 9 ms QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Selection (Selection=?) (~3 rows)

Como se puede ver, los conteos son muy lentos, pero las selecciones normales son rápidas (excepto la segunda, que tomó 16 segundos).

Lo mismo ocurre con Table2:

SELECT COUNT(*) FROM Table2 Time: 528 sec QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~1000000 rows) SELECT COUNT(Key) FROM Table2 Time: 249 sec QP: SCAN TABLE Table2 (~1000000 rows) SELECT * FROM Table2 WHERE Key = 5123456 AND Key2 = 0 Time: 7 ms QP: SEARCH TABLE Table2 USING INDEX sqlite_autoindex_Table2_1 (Key=? AND Key2=?) (~1 rows)

¿Por qué SQLite no utiliza el índice creado automáticamente en la clave principal en Table1? ¿Y por qué, cuando usa el auto-índice en la Tabla 2, todavía lleva mucho tiempo?

Creé las mismas tablas con el mismo contenido e índices en SQL Server 2008 R2 y allí los conteos son casi instantáneos.

Uno de los comentarios a continuación sugirió la ejecución de ANALYZE en la base de datos. Lo hice y tardó 11 minutos en completarlo. Después de eso, volví a realizar algunas de las pruebas:

SELECT COUNT(*) FROM Table1 Time: 104 sec QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~7848023 rows) SELECT COUNT(Key) FROM Table1 Time: 151 sec QP: SCAN TABLE Table1 (~7848023 rows) SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1 Time: 5 ms QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid>?) (~196200 rows) SELECT COUNT(*) FROM Table2 Time: 529 sec QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~51152542 rows) SELECT COUNT(Key) FROM Table2 Time: 249 sec QP: SCAN TABLE Table2 (~51152542 rows)

Como puede ver, las consultas tomaron el mismo tiempo (excepto que el plan de consultas ahora muestra el número real de filas), solo la selección más lenta ahora también es rápida.

A continuación, creo un índice adicional de dan en el campo Clave de la Tabla1, que debe corresponder al índice automático. Hice esto en la base de datos original, sin los datos de ANALIZAR. Tomó más de 23 minutos crear este índice (recuerde, esto está en una memoria USB).

CREATE INDEX IDX_Table1_Key ON Table1 (Key ASC)

Luego volví a hacer las pruebas:

SELECT COUNT(*) FROM Table1 Time: 4 sec QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Key(~1000000 rows) SELECT COUNT(Key) FROM Table1 Time: 167 sec QP: SCAN TABLE Table2 (~1000000 rows) SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1 Time: 17 sec QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)

Como puede ver, el índice ayudó con el recuento (*), pero no con el recuento (Clave).

Finalmente, creé la tabla usando una restricción de columna en lugar de una restricción de tabla:

CREATE TABLE Table1 ( Key INTEGER PRIMARY KEY ASC NOT NULL, ... several other fields ..., Status CHAR(1) NOT NULL, Selection VARCHAR NULL)

Luego volví a hacer las pruebas:

SELECT COUNT(*) FROM Table1 Time: 6 sec QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows) SELECT COUNT(Key) FROM Table1 Time: 28 sec QP: SCAN TABLE Table1 (~1000000 rows) SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1 Time: 10 sec QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)

Aunque los planes de consulta son los mismos, los tiempos son mucho mejores. Por qué es esto ?

El problema es que ALTER TABLE no permite convertir una tabla existente y tengo muchas bases de datos existentes que no puedo convertir a este formulario. Además, el uso de una columna en lugar de una restricción de tabla no funcionará para la Tabla 2.

¿Alguien tiene alguna idea de lo que estoy haciendo mal y cómo resolver este problema?

Utilicé System.Data.SQLite versión 1.0.74.0 para crear las tablas y para ejecutar las pruebas. Utilicé SQLiteSpy 1.9.1.

Gracias,

Bagazo

¡No cuentes las estrellas, cuenta los registros! O en otro idioma, nunca emitir

SELECCIONAR CUENTA (*) DE nombre de tabla;

utilizar

SELECT COUNT (ROWID) FROM tablename;

Llame a EXPLAIN QUERY PLAN para que ambos vean la diferencia. Asegúrese de tener un índice en su lugar que contenga todas las columnas mencionadas en la cláusula WHERE.

Aquí hay una solución potencial para mejorar el rendimiento de la consulta. Desde el contexto, parece que su consulta tarda aproximadamente un minuto y medio en ejecutarse.

Suponiendo que tiene una columna date_created (o puede agregar una), ejecute una consulta en segundo plano todos los días a la medianoche (digamos a las 00:05 am) y conserve el valor en algún lugar junto con la fecha de última actualización que se calculó (volveré a que en un momento).

Luego, ejecutándose contra su columna date_created (con un índice), puede evitar un escaneo completo de la tabla haciendo una consulta como SELECT COUNT (*) DESDE LA TABLA DONDE date_updated> "[TODAY] 00:00:05".

Agregue el valor de conteo de esa consulta a su valor persistente, y tendrá un conteo razonablemente rápido que generalmente es preciso.

El único inconveniente es que desde las 12:05 a.m. hasta las 12:07 a.m. (la duración durante la cual se ejecuta la consulta de recuento total) tiene una condición de carrera en la que puede verificar el valor last_updated de su recuento de escaneo de la tabla completa (). Si tiene más de 24 horas de antigüedad, entonces su consulta de recuento incremental debe extraer un recuento de un día completo más el tiempo transcurrido hasta hoy. Si tiene <24 horas de antigüedad, entonces su consulta de recuento incremental debe extraer un recuento de un día parcial (solo el tiempo transcurrido hasta hoy).

De http://old.nabble.com/count(*)-slow-td869876.html

SQLite siempre realiza una exploración de tabla completa para el recuento (*). Eso
No mantiene la metainformación en las tablas para acelerar esto.
procesar hasta

No mantener la información meta es un diseño deliberado.
decisión. Si cada tabla almacenó un conteo (o mejor, cada
nodo del btree almacenado un recuento) luego mucho más actualización
Tendría que ocurrir en cada INSERT o DELETE. Esta
ralentizaría INSERTAR y BORRAR, incluso en el común
caso en el que la velocidad de conteo (*) no es importante.

Si realmente necesitas una cuenta rápida, entonces puedes crear
un disparador en INSERT y DELETE que actualiza una ejecución
contar en una tabla separada luego consultar que separar
tabla para encontrar el último recuento.

Por supuesto, no vale la pena mantener un recuento completo de filas si
necesita COUNT dependientes de las cláusulas WHERE (es decir, DONDE field1> 0 y field2 <1000000000).

En lo que respecta a la restricción de la columna, SQLite asigna las columnas que se declaran INTEGER PRIMARY KEY a la identificación de la fila interna (que a su vez admite una serie de optimizaciones internas). Teóricamente, podría hacer lo mismo para una restricción de clave primaria declarada por separado, pero parece no hacerlo en la práctica, al menos con la versión de SQLite en uso. (System.Data.SQLite 1.0.74.0 corresponde al núcleo SQLite 3.7.7.1. Es posible que desee volver a verificar sus cifras con 1.0.79.0; no debería necesitar cambiar su base de datos para hacer eso, solo la biblioteca).

Es posible que esto no ayude mucho, pero puede ejecutar el comando ANALYZE para reconstruir las estadísticas sobre su base de datos. Intente ejecutar " ANALYZE; " para reconstruir las estadísticas de toda la base de datos, luego vuelva a ejecutar la consulta y vea si es más rápida.

La salida para las consultas rápidas comienza con el texto "QP: SEARCH". Mientras que las de las consultas lentas comienzan con el texto "QP: SCAN", lo que sugiere que sqlite está realizando un escaneo de toda la tabla para generar el recuento.

Buscar en Google para el "conteo de la exploración de la tabla sqlite" encuentra lo siguiente , lo que sugiere que usar una exploración de la tabla completa para recuperar una cuenta es solo la forma en que funciona sqlite, y por lo tanto es probablemente inevitable.

Como solución alternativa, y dado que el estado tiene solo ocho valores, me pregunté si podría obtener un recuento rápidamente utilizando una consulta como la siguiente.

seleccione 1 donde status = 1 union seleccione 1 donde status = 2 ...

luego cuenta las filas en el resultado. Esto es claramente feo, pero podría funcionar si persuade a sqlite a ejecutar la consulta como una búsqueda en lugar de una exploración. La idea de devolver "1" cada vez es evitar la sobrecarga de devolver datos reales.

Tuve el mismo problema, en mi situación el comando VACUUM ayudó. Después de su ejecución en la base de datos, la velocidad COUNT (*) aumentó cerca de 100 veces. Sin embargo, el comando en sí necesita algunos minutos en mi base de datos (20 millones de registros). Resolví este problema ejecutando VACUUM cuando mi software sale después de la destrucción de la ventana principal, por lo que la demora no causa problemas al usuario.