sql - subconsultas access

Los índices funcionan con la cláusula "IN"

Question

Si tengo una consulta como:

Select EmployeeId 
From Employee 
Where EmployeeTypeId IN (1,2,3)

y tengo un índice en el campo EmployeeTypeId , ¿SQL Server aún usa ese índice?

Answer 1

Entonces, ¿existe la posibilidad de que una cláusula "IN" ejecute un escaneo de tabla, pero el optimizador intentará encontrar la mejor manera de manejarlo?

El uso de un índice no varía tanto en el tipo de consulta, tanto del tipo y la distribución de los datos en la (s) tabla (s), cuán actualizadas son las estadísticas de la tabla y el tipo de datos real de la columna .

Los otros carteles son correctos de que un índice se utilizará en una exploración de tabla si:

• La consulta no tendrá acceso a más de un cierto porcentaje de las filas indexadas (digamos ~ 10% pero debería variar entre los DBMS).
• Alternativamente, si hay muchas filas, pero relativamente pocos valores únicos en la columna, también puede ser más rápido hacer una exploración de tabla.

La otra variable que puede no ser tan obvia es asegurarse de que los tipos de datos de los valores que se comparan sean los mismos. En PostgreSQL, no creo que los índices se utilizarán si está filtrando en un flotador, pero su columna está compuesta de entradas. También hay algunos operadores que no admiten el uso del índice (de nuevo, en PostgreSQL, el operador ILIKE es así).

Sin embargo, como se señaló, siempre verifique el analizador de consultas cuando tenga dudas y la documentación de su DBMS sea su amiga.

Answer 2

@Mike: gracias por el análisis detallado. Definitivamente hay algunos puntos interesantes que hagas allí. El ejemplo que publiqué es algo trivial, pero la base de la pregunta vino del uso de NHibernate.

Con NHibernate, puedes escribir una cláusula como esta:

int[] employeeIds = new int[]{1, 5, 23463, 32523};
NHibernateSession.CreateCriteria(typeof(Employee))
.Add(Restrictions.InG("EmployeeId",employeeIds))

NHibernate luego genera una consulta que se parece a

select * from employee where employeeid in (1, 5, 23463, 32523)

Entonces, como han señalado usted y otros, parece que habrá momentos en los que se usará un índice o se realizará un escaneo de tabla, pero no se puede determinar realmente hasta el tiempo de ejecución.

Answer 3

A menos que la tecnología haya mejorado en formas que no puedo imaginar últimamente, la consulta "IN" mostrada producirá un resultado que es efectivamente el OR de tres conjuntos de resultados, uno para cada uno de los valores en la lista "IN". La cláusula IN se convierte en una condición de igualdad para cada una de la lista y usará un índice si corresponde. En el caso de los ID únicos y una tabla lo suficientemente grande, esperaría que el optimizador utilizara un índice.

Sin embargo, si los elementos de la lista no fueran únicos, y supongo que en el ejemplo de que un "TypeId" es una clave externa, entonces estoy más interesado en la distribución. Me pregunto si el optimizador verificará las estadísticas de cada valor en la lista. Digamos que verifica el primer valor y descubre que está en el 20% de las filas (de una tabla lo suficientemente grande como para importar). Probablemente sea una exploración de tabla. ¿Pero se usará el mismo plan de consulta para los otros dos, incluso si son únicos?

Probablemente sea discutible: algo así como una tabla Employee probablemente sea lo suficientemente pequeño para que quede almacenado en la memoria y probablemente no notarás una diferencia entre eso y la recuperación indexada de todos modos.

Y, por último, mientras predico, tenga cuidado con la consulta en la cláusula IN: a menudo es una forma rápida de hacer que funcione algo y (al menos para mí) puede ser una buena forma de expresar el requisito, pero casi siempre es mejor reformularlo como unirme Su optimizador puede ser lo suficientemente inteligente como para detectar esto, pero puede que no. Si actualmente no compara el rendimiento con los volúmenes de datos de producción, hágalo: en estos días de optimización basada en costos, no puede estar seguro del plan de consultas hasta que tenga una carga completa y estadísticas representativas. Si no puede, prepárese para sorpresas en producción ...

Answer 4

Por lo general, a menos que la cláusula IN cubra demasiado de la tabla, y luego hará un escaneo de tabla. La mejor manera de averiguarlo en su caso específico sería ejecutarlo en el analizador de consultas y verificar el plan de ejecución.

Answer 5

Sí es cierto. Si su tabla de empleados tiene 10,000 registros, y solo 5 registros tienen identificada USEeypeID (1,2,3), entonces lo más probable es que use el índice para buscar los registros. Sin embargo, si encuentra que 9,000 registros tienen el employeeIDType (1,2,3), lo más probable es que haga un escaneo de tabla para obtener los EmployeeID correspondientes, ya que es más rápido simplemente recorrer toda la tabla que ir a cada rama del árbol de índice y mira los registros individualmente.

SQL Server hace muchas cosas para probar y optimizar cómo se ejecutan las consultas. Sin embargo, a veces no obtiene la respuesta correcta. Si sabe que SQL Server no está utilizando el índice, al consultar el plan de ejecución en el analizador de consultas, puede indicarle al motor de consulta que use un índice específico con el siguiente cambio en su consulta.

Select EmployeeId From Employee WITH (Index(Index_EmployeeTypeId )) Where EmployeeTypeId IN (1,2,3)

Suponiendo que el índice que tiene en el campo EmployeeTypeId se llama Index_EmployeeTypeId.

Answer 6

Select EmployeeId From Employee USE(INDEX(EmployeeTypeId))

Esta consulta buscará utilizando el índice que ha creado. Esto funciona para mi. Por favor, intente ...