mega - ¿SQL IN es malo para el rendimiento?
sql server 2018 (14)
Aquí está tu respuesta ...
http://www.4guysfromrolla.com/webtech/031004-1.shtml
Básicamente, desea crear una función que dividirá una cadena y rellenará una tabla temporal con los contenidos divididos. Entonces puedes unirte a esa tabla temporal y manipular tus datos. Lo anterior explica las cosas bastante bien. Utilizo mucho esta técnica.
En su caso específico, utilice una unión a la tabla temporal en lugar de una cláusula in, mucho más rápido.
Tengo una consulta haciendo algo como:
SELECT FieldX, FieldY FROM A
WHERE FieldW IN (108, 109, 113, 138, 146, 160,
307, 314, 370, 371, 441, 454 ,457, 458, 479, 480,
485, 488, 490, 492, 519, 523, 525, 534, 539, 543,
546, 547, 550, 564, 573, 629, 642, 643, 649, 650,
651, 694, 698, 699, 761, 762, 768, 772, 773, 774,
775, 778, 784, 843, 844, 848, 851, 852, 853, 854,
855, 856, 857, 858, 859, 860, 861, 862, 863, 864,
865, 868, 869, 871, 872, 873, 891)
Tener una cláusula IN con tantas opciones, ¿es malo para el rendimiento de las consultas? Estoy experimentando muchos tiempos de espera en mi aplicación, y creo que podría ser una fuente de este tipo de problema. ¿Puedo optimizar la consulta sin eliminar los números, usando cualquier buena sugerencia de SQL?
EDITAR:
@KM estas son claves en una tabla diferente. Esta es una aplicación de foro, que explica brevemente: c # obtiene todos los foros de la base de datos y los almacena en el caché de la aplicación. Antes de que C # llame a un procedimiento que obtiene los hilos para estos foros y para este usuario, c # hace un poco de lógica al filtrar la colección "todos los foros", teniendo en cuenta los permisos y cierta lógica comercial. El tiempo de espera ocurre en la base de datos y no en la aplicación en sí. Hacer toda esta lógica en la consulta requerirá muchas combinaciones internas y no estoy 100% seguro de poder hacer todo esto dentro del procedimiento.
Estoy usando SQL Server 2000
Básicamente lo que hace esa cláusula where es "FieldW = 108 O FieldW = 109 O FieldW = 113 ...". A veces puede obtener un mejor rendimiento haciendo selecciones múltiples y combinándolos con unión. Por ejemplo:
SELECT FieldX, FieldY FROM A WHERE FieldW = 108
UNION ALL
SELECT FieldX, FieldY FROM A WHERE FieldW = 109
Pero, por supuesto, eso no es práctico cuando se compara con tantos valores.
Otra opción podría ser insertar esos valores en una tabla temporal y luego unir la tabla A con esa tabla temporal.
Dependiendo de su distribución de datos, los predicados adicionales en su cláusula WHERE pueden mejorar el rendimiento. Por ejemplo, si el conjunto de identificadores es pequeño en relación con el número total en la tabla, y usted sabe que los identificadores están relativamente juntos (tal vez serán agregados recientes, y por lo tanto agrupados en el extremo superior del rango), podría intentar incluir el predicado "AND FieldW ENTRE 109 Y 891" (después de determinar el id. mínimo y máximo en su conjunto en el código C #). Puede ser que hacer un escaneo de rango en esas columnas (si está indexado) funciona más rápido que lo que se está utilizando actualmente.
El rendimiento solo se puede juzgar en el contexto de lo que intenta hacer. En este caso, está solicitando la recuperación de alrededor de 70 filas (suponiendo que sean valores únicos), por lo que puede esperar algo así como 70 veces la duración de recuperar un solo valor. Puede ser menos debido al almacenamiento en caché, o por supuesto.
Sin embargo, el optimizador de consultas puede necesitar o elegir realizar un escaneo de tabla completo para recuperar los valores, en cuyo caso el rendimiento será un poco diferente que recuperar un único valor a través del mismo plan de acceso.
En general, utilizaría un tipo de tabla definido por el usuario para consultas como esta.
CREATE TYPE [dbo].[udt_int] AS TABLE (
[id] [int] NOT NULL
)
Usando una variable de tabla y llenándola con filas para cada uno de sus números, puede hacer:
SELECT
FieldX,
FieldY
FROM A
INNER JOIN @myIds B ON
A.FieldW = B.id
Hay mejores formas de codificarlo, pero dudo que sea la causa de tus tiempos de espera, especialmente si solo es SELECCIONADO. Sin embargo, debería poder determinar eso al observar los rastreos de su consulta. Pero recodificar esto sería una optimización adivinando, y una suposición poco probable al respecto.
Comencemos con un plan de consulta para la consulta que en realidad se está agotando. ¿Sabes con seguridad qué consulta es?
Hay varias consideraciones cuando se escribe una consulta usando el operador IN que puede afectar el rendimiento.
En primer lugar, las cláusulas IN generalmente se reescriben internamente en la mayoría de las bases de datos para utilizar el conector lógico OR. Entonces col IN (''a'',''b'',''c'') se reescribe a: (COL = ''a'') OR (COL = ''b'') or (COL = ''c'') . El plan de ejecución para ambas consultas probablemente será equivalente suponiendo que tiene un índice en col .
En segundo lugar, cuando usa IN u OR con un número variable de argumentos, hace que la base de datos tenga que volver a analizar la consulta y reconstruir un plan de ejecución cada vez que cambian los argumentos. Crear el plan de ejecución para una consulta puede ser un paso costoso. La mayoría de las bases de datos almacenan en caché los planes de ejecución para las consultas que ejecutan utilizando el texto de consulta EXACT como clave. Si ejecuta una consulta similar pero con diferentes valores de argumento en el predicado, lo más probable es que la base de datos pase una gran cantidad de tiempo analizando y construyendo planes de ejecución. Esta es la razón por la cual las variables de vinculación se recomiendan encarecidamente como una forma de garantizar un rendimiento de consulta óptimo.
En tercer lugar, muchas bases de datos tienen un límite en la complejidad de las consultas que pueden ejecutar; uno de esos límites es la cantidad de conexiones lógicas que se pueden incluir en el predicado. En su caso, es poco probable que unas pocas docenas de valores alcancen el límite incorporado de la base de datos, pero si espera pasar cientos o miles de valores a una cláusula IN, definitivamente puede suceder. En ese caso, la base de datos simplemente cancelará la solicitud de consulta.
En cuarto lugar, las consultas que incluyen IN y OR en el predicado no siempre se pueden reescribir de forma óptima en un entorno paralelo. Existen varios casos en los que no se aplica la optimización de servidor paralelo: MSDN tiene una introducción decente para optimizar las consultas de paralelismo. Sin embargo, en general, las consultas que usan el operador UNION ALL son trivialmente paralelas en la mayoría de las bases de datos, y son preferibles a las conectivas lógicas (como OR e IN) cuando es posible.
IN es exactamente lo mismo que escribir una gran lista de RUP. Y O a menudo hace que las consultas sean DESCARGABLES, por lo que es posible que se ignoren los índices y el plan realice una exploración completa.
Normalmente, la cláusula IN es perjudicial para el rendimiento, pero lo que es "malo" depende de la aplicación, los datos, el tamaño de la base de datos, etc. Debe probar su propia aplicación para ver qué es lo mejor.
Puede intentar algo como:
select a.FieldX, a.FieldY
from (
select FieldW = 108 union
select FieldW = 109 union
select FieldW = 113 union
...
select FieldW = 891
) _a
join A a on a.FieldW = _a.FieldW
Puede ser apropiado para su situación, como cuando desea generar una única instrucción SQL de forma dinámica. En mi máquina (SQL Server 2008 Express), probando con un número pequeño (5) de valores de FieldW y un número grande (100,000) de filas en A, esto usa una búsqueda de índice en A con una unión de bucles anidados entre A y _a, que es probablemente lo que estás buscando.
Puede intentar crear una tabla temporal, insertar sus valores y usar la tabla en su lugar en el predicado IN .
AFAIK, SQL Server 2000 no puede construir una tabla hash del conjunto de constantes, lo que priva al optimizador de la posibilidad de utilizar un HASH SEMI JOIN .
Esto ayudará solo si no tiene un índice en FieldW (que debería tener).
También puede intentar incluir sus columnas FieldX y FieldY en el índice:
CREATE INDEX ix_a_wxy ON a (FieldW, FieldX, FieldY)
para que la consulta solo pueda ser servida usando el índice.
SQL Server 2000 carece de la opción INCLUDE para CREATE INDEX y esto puede degradar un poco el rendimiento de DML pero mejorar el rendimiento de la consulta.
Actualizar:
De su plan de ejecución veo que necesita un índice compuesto en (SettingsID, SectionID)
SQL Server 2000 hecho puede construir una tabla hash fuera de una lista constante (y lo hace), pero Hash Semi Join probablemente será menos eficiente que un Nested Loop para la consulta de consulta.
Y solo una nota al margen: si necesita saber el recuento de filas que satisfacen la condición WHERE , no use COUNT(column) , use COUNT(*) lugar.
Un COUNT(column) no cuenta las filas para las cuales el valor de column es NULL .
Esto significa que, primero, puede obtener los resultados que no esperaba y, en segundo lugar, el optimizador deberá realizar una Key Lookup adicional / Bookmark Lookup si su columna no está cubierta por un índice que sirva la condición WHERE .
Dado que ThreadId parece ser una CLUSTERED PRIMARY KEY , está bien para esta misma consulta, pero trate de evitarla en general.
Si puede usar otras cosas que no sean IN: hágalo (yo estaba usando IN en algún caso no es realmente la mejor manera: puedo reemplazar fácilmente con existir y es más rápido)
En tu caso: parece que no está tan mal.
Si tiene un buen índice en FieldW, usar ese IN es perfectamente correcto.
Acabo de probar y SQL 2000 realiza un Análisis de índice agrupado cuando usa el IN.
el tamaño de su tabla determinará la velocidad al usar esta declaración. Si no es una tabla muy grande ... esta afirmación no está afectando su rendimiento.