techniques - sql performance tuning

¿Hay algo más rápido que "contar" para mesas grandes?

Question

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!

Answer 1

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!

Answer 2

Si aún no tiene uno, cree un índice compuesto en sentence_id, word_id.

Answer 3

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.

Answer 4

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.

Answer 5

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;