¿Qué es una consulta amplia en 2D?
Una consulta amplia 2D es uno donde dos columnas independientes están siendo probados con un rango de valores. Por ejemplo, la consulta:SELECCIONAR * DE MyTable DONDE @ constant_date ENTRE DateStart Y DateEnd
Tiene dos grados de libertad: tanto DateStart y DateEnd se están gama utilizada. DateStart debe estar entre {} y min_date constant_date @ y @ constant_date DateEnd entre {y} max_date. Se trata de una consulta amplia en 2D.Nota: la consulta muy similar de aspecto:
SELECCIONAR * DE MyTable DONDE DateColumn ENTRE constant_date1 @ Y @ constant_date2
Usted puede tener preguntas 2D gama sin usar el operador BETWEEN. Por ejemplo, la consulta:
SELECCIONAR * DE MyTable DONDE Columna1 > AlgúnValor @ Y Columna2 > @ SomeOtherValue
Es también una consulta amplia en 2D, y que no pueden ser fácilmente manipulados por un índice B-Tree.
No puede ser más alto consultas dimensiones también. Por ejemplo, las consultas 3D gama son comunes para los datos geoespaciales.
El problema de la
SQL Server utiliza índices B-Tree por todo, pero los datos espaciales. Un árbol B es unidimensional y no puede ser correctamente utilizados en las búsquedas por rangos 2D. Tomando una mirada más cercana a nuestra consulta de ejemplo:... DONDE @ fecha ENTRE DateStart Y DateEnd
Usted puede crear un índice o onDateStart DateEnd, o el índice compuesto (DateStart, DateEnd). Un índice de onDateStart solo permite la QO (optimizador de consultas) para reducir el rango de valores sólo para el rango de {} min_date hasta la fecha @. Las filas a continuación, se debe explorar para encontrar a los que satisfacen parte DateEnds de la consulta. Estadísticamente, este promedio a sólo la eliminación de la mitad de las filas. A menos que la fecha @ pasa a estar muy cerca de lo que el índice de estadísticas dice que es el valor más alto, el motor es por lo tanto mejor haciendo un escaneo completo de tabla. El mismo problema se aplica a un índice en DateEnd.¿Y el índice compuesto? Eso no ayuda, ya sea los valores de DateEnd se ordenan sólo dentro de cada valor específico de DateStart. Cada vez que el índice se mueve a un nuevo valor de DTStart, todo un nuevo B-Tree se construye para los valores DateEnd que corresponden a esa fecha específica de comienzo. Las ganancias índice compuesto que una pequeña cantidad, porque es cubrir la consulta - que contiene los argumentos de búsqueda, por lo que le ahorra algunos RID (identificador de fila) las búsquedas. Pero, en promedio, todavía tiene que escanear la mitad del índice completo para responder a la consulta. Si tiene otras columnas de la consulta, la QO mayfind su más barato que hacer una mesa de análisis completo de todos modos.
Nota: Los índices quadtree resolver este problema para el caso 2D, así como octrees funcionan bien para 3D. En la segunda mitad de este artículo, te diré cómo puedes utilizar estos índices en SQL Server para tipos de datos no espaciales, pero este artículo se centrará en mejorar el rendimiento de las búsquedas por rangos de mayor dimensión con un índice de la norma B-Tree .
¿Podemos mejorar el rendimiento?
En el caso completamente general, no, no podemos. Pero las probabilidades son si usted está haciendo un ENTRE en dos columnas distintas, las columnas no son totalmente independientes. Hay algún tipo de relación entre ellos, una relación que el desarrollador sabe, pero SQL Server no. La alimentación que la información a la QO puede producir resultados impresionantes.Example.Consider una tabla de eventos, cada fecha tiene un comienzo y un final. SQL Server no sabe todo lo que hacemos acerca de estas dos fechas. Por ejemplo, la fecha final debe ser siempre mayor que la fecha de inicio. Supongamos también que los eventos tienen una duración limitada: decir ningún caso tendrá una duración de más de 5 días. ¿Cómo podemos pasar esta información a la QO?
Aquí está nuestra pregunta inicial:
SELECCIONAR * DESDE Eventos
DONDE @ fecha ENTRE DateStart Y DateEnd
Debido a que esta es una consulta amplia en 2D, que será muy lenta. Pero ya sabemos que ningún caso puede durar más de cinco días, vamos a utilizar un nuevo predicado que encapsula lo siguiente:SELECCIONAR * DESDE Eventos
DONDE DateStart ENTRE DATEADD ( dd , - 5 , @ fecha ) y la fecha @
Esta nueva consulta es ahora una amplia 1D: se utilizará en su totalidad un índice B-Tree. El único problema es que, si bien, devuelve todas las filas de la consulta original, sino que también muchas devoluciones que no forman parte de esa consulta. Así que vamos a arreglar eso, sumándole el original predicado junto con el nuevo:SELECCIONAR * DESDE Eventos
DONDE DateStart ENTRE DATEADD ( dd , - 5 , @ fecha ) y la fecha @
Y @ fecha ENTRE DateStart Y DateEnd
Esto puede parecer un poco extraño, pero conceptualmente es fácil de entender. La ENTRE primero usa un índice de búsqueda rápida para limitar nuestra búsqueda. Los límites entre el segundo a continuación, los resultados exactamente a los que queremos.¿Cuánto puede mejorar este rendimiento? Hagamos un poco de benchmarking!
Datos de Prueba y Procedimiento
Si usted está interesado en una técnica como ésta, lo más probable es que haya algunas grandes tablas y consultas lentas. Así que no te metas con los conjuntos de datos pequeños. Vamos a crear una tabla con 30 millones de filas. Cada fila representa un acontecimiento, con una fecha de inicio al azar entre 30 años y hoy en día, y una fecha de finalización al azar dentro de los 5 días siguientes a la fecha de inicio. Un GUID único de 16 bytes también está presente en cada fila (esto no afecta significativamente a los resultados de las pruebas, sino que está presente sólo para mejorar las columnas del modelo no SARG en la fila de datos).CreateTable Eventos
(
Uniqueidentifer ID NOTNULL DEFECTO NEWID (),
DTStart DATETIME NOTNULL,
DTEND DATETIME NOTNULL
)
DECLARE @ i BIGINT
DECLARE @ offset_days INTEGER
DECLARE @ offset_min INTEGER
DECLARE @ INTEGER offset_end
DECLARE @ DTStart DateTime
JUEGO @ i = 0
MIENTRAS @ i < 30000000 EMPEZAR
JUEGO offset_days @ = 365 * 30 * RAND ( CHECKSUM ( NEWID ()))
JUEGO @ offset_min = 1440 * RAND ( CHECKSUM ( NEWID ()))
JUEGO @ offset_end = 5 * 1440 * RAND ( CHECKSUM ( NEWID ()))
JUEGO @ DTStart = DATEADD ( n , @ offset_min , DATEADD ( dd , - @ offset_days , GETDATE ()))
INSERTAR Eventos SELECCIONAR @ DTStart , DATEADD ( n , offset_end @ , @ DTStart ), NEWID ()
JUEGO @ i = @ i + 1
FIN
CreateIndex IX_EventInt SOBRE Eventos ( DTStart , DTEND )
La escritura de la prueba está por debajo de consulta (una consulta se ha comentado para cada conjunto de pistas)DECLARE @ STIME DATETIME
PUNTO DE CONTROL
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
JUEGO @ STIME = GETDATE ()
- Consulta original
SELECCIONAR * DESDE Eventos DONDE @ dt ENTRE DTStart Y DTEND
- Optimización de consultas
SELECCIONAR DTStart , DTEND DE Eventos DONDE DTStart ENTRE DATEADD ( dd , - 5 , @ dt ) Y dt @ Y @ dt ENTRE DTStart Y DTEND
IMPRIMIR DATEDIFF ( ms , @ STIME , GETDATE ())
Resultados de referencia
Las pruebas se realizaron en cinco ocasiones cada uno, con un PUNTO DE CONTROL y DBCC DROPCLEANBUFFERS entre cada prueba para limitar los efectos de almacenamiento en caché.La primera consulta tuvo un tiempo medio de ejecución de 19.400 m (19.4s).
La consulta optimizada tuvo un tiempo de ejecución promedio de 121ms. Eso es más de 150 veces más rápido que la consulta original. Este tipo de logros no son infrecuentes los casos en que una consulta amplia es la prevención de buen uso del índice.
Veamos otro ejemplo. Desea consultar una tabla de empleados para las personas que solicitó un puesto a partir del 1 de enero 2009, y fueron contratados en o antes del 01 de enero 2010. Su primer intento de escribir esta consulta sería algo como esto:
SELECCIONAR *
DESDE empleados
DONDE Apply_Date > = '20080101 ' Y Hire_date <= '20090101 '
Una vez más, tenemos una amplia consulta en 2D y un rendimiento deficiente. ¿Cómo podemos mejorar el rendimiento? La clave es construir de nuevo en la información de consulta sobre la relación entre las dos columnas de información actualizada, la QO no tiene. Por ejemplo, sabemos que alguien no puede ser contratado antes de que lo hayan solicitado. Con esto, podemos añadir un nuevo predicado a nuestra consulta:SELECCIONAR *
DESDE empleados
DONDE Hire_date ENTRE '20080101 ' Y '20090101 ' Y Apply_Date > = '20080101 '
Esto puede parecer un poco redundante, pero recuerde que el QO no sabe lo que sabemos. Sin el predicado adicional, se elegirá una de las dos posibilidades, dependiendo de las estadísticas de índice:Escanear índice en Apply_Date para todas las filas donde Apply_date entre 1 de enero 2009 y (max_date)
índice de exploración en Hire_date para todas las filas donde Hire_date entre (min_date) y 01 de enero 2009.
Estadísticas le permitirá hacer un poco mejor que la exploración de la mitad de la filas de la tabla, pero el motor sigue viento de exploración de varios años el valor de los datos. Con el predicado adicional, tenemos que examinar un año solamente.
No hay comentarios:
Publicar un comentario