techniques - sql performance tuning
¿Hay algo más rápido que "contar" para mesas grandes? (5)
Si a menudo se realiza esa consulta, y la tabla rara vez se actualiza, puede mantener una tabla auxiliar con identificadores de palabras y recuentos de oraciones correspondientes, ¡es difícil pensar en una optimización adicional más allá de eso!
Aquí está mi consulta:
select word_id, count(sentence_id)
from sentence_word
group by word_id
having count(sentence_id) > 100;
La oración de tabla contiene 3 campos, wordid, oraciónid y una identificación de clave principal. Tiene 350k + filas. Esta consulta lleva 85 segundos y me pregunto (¿espero, rezar?) Que haya una manera más rápida de encontrar todos los wordids que tienen más de 100 sententes.
Intenté eliminar la parte del conteo selecto, y solo hice ''contar (1)'', pero ninguno lo acelera.
Apreciaría cualquier ayuda que pueda prestar. ¡Gracias!
Si aún no tiene uno, cree un índice compuesto en sentence_id, word_id.
Su consulta está bien, pero necesita un poco de ayuda (índices) para obtener resultados más rápidos.
No tengo mis recursos a mano (o acceso a SQL), pero intentaré ayudarte desde la memoria.
Conceptualmente, la única forma de responder esa consulta es contar todos los registros que comparten el mismo word_id. Eso significa que el motor de consulta necesita una forma rápida de encontrar esos registros. Sin un índice en word_id, lo único que puede hacer la base de datos es pasar por la tabla un registro a la vez y seguir ejecutando totales de cada palabra distinta_ que encuentre. Eso generalmente requeriría una tabla temporal y no se pueden enviar resultados hasta que se escanee toda la tabla. No está bien.
Con un índice en word_id, todavía tiene que pasar por la tabla, por lo que podría pensar que no ayudaría mucho. Sin embargo, el motor de SQL ahora puede calcular el recuento de cada word_id sin esperar hasta el final de la tabla: puede enviar la fila y el recuento de ese valor de word_id (si pasa su cláusula where ), o descartar la fila (si no lo hace); eso dará como resultado una menor carga de memoria en el servidor, posiblemente respuestas parciales, y la tabla temporal ya no es necesaria. Un segundo aspecto es el paralelismo; con un índice en word_id, SQL puede dividir el trabajo en fragmentos y usar núcleos de procesador separados para ejecutar la consulta en paralelo (dependiendo de las capacidades de hardware y la carga de trabajo existente).
Eso podría ser suficiente para ayudar a su consulta; pero deberás tratar de ver:
CREATE INDEX someindexname ON sentence_word (word_id)
(Sintaxis T-SQL; no especificó qué producto SQL está utilizando)
Si eso no es suficiente (o no ayuda en absoluto), existen otras dos soluciones.
En primer lugar, SQL le permite calcular previamente el COUNT (*) mediante el uso de vistas indizadas y otros mecanismos. No tengo los detalles a mano (y no hago esto a menudo). Si sus datos no cambian con frecuencia, eso le daría resultados más rápidos pero con un costo en complejidad y un poco de almacenamiento.
Además, es posible que desee considerar almacenar los resultados de la consulta en una tabla separada. Esto es práctico solo si los datos nunca cambian o cambian en un horario preciso (por ejemplo, durante una actualización de datos a las 2 de la mañana), o si cambia muy poco y puede vivir con resultados no perfectos durante unas horas ( tendría que programar una actualización periódica de datos); ese es el equivalente moral del almacén de datos de un hombre pobre.
La mejor manera de averiguar con certeza lo que funciona para usted es ejecutar la consulta y ver el plan de consulta con y sin algunos índices de candidatos como el anterior.
teniendo count (sentence_id)> 100;
Hay un problema con esto ... O la tabla tiene pares duplicados de palabras / oraciones, o no.
Si tiene pares duplicados de palabras / oraciones, debe usar este código para obtener la respuesta correcta:
HAVING COUNT(DISTINCT Sentence_ID) > 100
Si la tabla no tiene pares duplicados de palabra / oración ... entonces no debes contar sentence_ids, solo debes contar las filas.
HAVING COUNT(*) > 100
En ese caso, puede crear un índice en word_id solamente , para un rendimiento óptimo.
Sorprendentemente, hay una manera más rápida de lograr eso en grandes conjuntos de datos:
SELECT totals.word_id, totals.num
FROM (SELECT word_id, COUNT(*) AS num FROM sentence_word GROUP BY word_id) AS totals
WHERE num > 1000;