Optimización del mayor n por grupo.
¿Cómo puedo optimizar esta consulta de SQLite? Actualmente tarda 31 segundos en ejecutarse. Quiero mostrar los 10 mayores ganadores y los 10 perdedores de acciones en una aplicación web. La tabla tiene 2 millones de filas y debería aumentar ligeramente cada día que haya nuevos datos de precios disponibles.
Si esto no es posible, podría crear una tarea programada para almacenar en caché estos resultados en una tabla temporal de base de datos o en un archivo temporal. Parece trabajo extra pero es posible si es necesario.
CON todayPrices COMO (
SELECT * FROM (
SELECT *, row_number() OVER (
PARTITION BY CompanyID
ORDER BY Date DESC
) AS rn
FROM DimCompanyPrice
) a
WHERE rn = 1
ORDER BY CompanyID ASC
),
yestPrices COMO (
SELECT * FROM (
SELECT *, row_number() OVER (
PARTITION BY CompanyID
ORDER BY Date DESC
) AS rn
FROM DimCompanyPrice
) a
WHERE rn = 2
ORDER BY CompanyID ASC
)
SELECT todayPrices.CompanyID, 100.0 * (todayPrices.CloseAdjusted-yestPrices.CloseAdjusted) / yestPrices.CloseAdjusted AS gain
FROM todayPrices
INNER JOIN yestPrices on todayPrices.CompanyID=yestPrices.CompanyID
ORDER BY gain DESC
LIMIT 10
Me gustaría recibir comentarios sobre cuál sería el mejor enfoque para mejorar el rendimiento de esto. Cualquier comentario sería apreciado.
Resultados de EXPLAIN QUERY PLAN:
id parent notused detail
3 0 0 MATERIALIZE 2
5 3 0 CO-ROUTINE 1
8 5 0 CO-ROUTINE 6
11 8 0 SCAN TABLE DimCompanyPrice
36 8 0 USE TEMP B-TREE FOR ORDER BY
62 5 0 SCAN SUBQUERY 6
134 3 0 SCAN SUBQUERY 1 AS a
163 3 0 USE TEMP B-TREE FOR ORDER BY
174 0 0 MATERIALIZE 4
176 174 0 CO-ROUTINE 3
179 176 0 CO-ROUTINE 7
182 179 0 SCAN TABLE DimCompanyPrice
207 179 0 USE TEMP B-TREE FOR ORDER BY
233 176 0 SCAN SUBQUERY 7
305 174 0 SCAN SUBQUERY 3 AS a
334 174 0 USE TEMP B-TREE FOR ORDER BY
345 0 0 SCAN SUBQUERY 4
357 0 0 SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (CompanyID=?)
382 0 0 USE TEMP B-TREE FOR ORDER BY
davy.ai
Hay algunas optimizaciones potenciales que podrían ayudar a mejorar el rendimiento de esta consulta:
DimCompanyPricetenga índices apropiados para soportar las operacionesPARTITION BY,ORDER BYyJOINen la consulta.Reduzca las subconsultas: En lugar de utilizar dos subconsultas separadas para los precios de hoy y los precios de ayer, combínelas en una sola subconsulta utilizando una auto-unió. Esto puede reducir la cantidad de trabajo que la base de datos tiene que hacer para generar los resultados.
Utilice una tabla temporal: Como se mencionó en el aviso, podría crear una tabla temporal para almacenar en caché los resultados de la consulta, lo que aumentaría el tiempo inicial de configuración pero reduciría el tiempo de respuesta de la consulta en el futuro.
Materialice subconsultas: El paso
MATERIALIZEen el plan de consulta indica que SQLite está creando una tabla intermedia para almacenar los resultados de la subconsulta para su uso posterior. Esto puede ser un cuello de botella de rendimiento, por lo que ajustar la consulta para garantizar que las subconsultas solo se materialicen cuando sea necesario puede ayudar a mejorar el rendimiento.Optimice las declaraciones
ORDER BY: El pasoUSE TEMP B-TREE FOR ORDER BYen el plan de consulta sugiere que SQLite está utilizando un archivo temporal para ordenar los resultados de la consulta antes de devolverlos. Minimizar la cantidad de datos que necesitan ser ordenados (por ejemplo, eliminando columnas innecesarias de los resultados) o proporcionando un algoritmo de ordenamiento personalizado puede ayudar a acelerar la consulta.En general, una combinación de estas técnicas debería ayudar a optimizar la consulta y reducir el tiempo de ejecución.