Cómo mejorar el rendimiento de SQL en las consultas sobre el rango de fechas

Solapas principales

¡Hola a tod@s!

¿Le parece que son demasiado lentas las consultas sobre el rango de las fechas?  ¿Le parece que el rendimiento de SQL es bajo?  ¡Tengo un truco curioso que podría ayudarle a solucionar estos problemas! (¡Los desarrolladores de SQL detestan que conozca estas cosas!)*

Si tiene una clase que almacena el historial de registro de la hora cuando se agregan datos, entonces esos datos se ordenarán con sus valores IDKEY, es decir, TimeStamp1 < TimeStamp2  si y solo si la condición ID1 < ID2 se cumple para todos los valores ID y TimeStamp en la tabla, entonces puede utilizar esta información para aumentar el rendimiento de las consultas en relación con los rangos de TimeStamp.  Examine la siguiente tabla:

Class User.TSOrder extends %Persistent 
{ 

Property TS as %TimeStamp;

Property Data as %String (MAXLEN=100, MINLEN=200);

Index TSIdx on TS;

Index Extent [type=bitmap, extent];

}

Si a esto le añade 30,000,000 filas aleatorias con las fechas de los últimos 30 días, obtendrá 1,000,000 filas por día.  Ahora, si quiere consultar la información de un día en específico escriba lo siguiente: 

SELECT ID, TS, Data 
FROM TSOrder
WHERE 
     TS >= '2016-07-01 00:00:00.00000' AND 
     TS <= '2016-07-01 23:59:59.999999'

Ciertamente, esta es una consulta razonable. Sin embargo, en mi sistema tomó 2,171,792 referencias globales y 7.2 segundos. Pero si sabemos que los ID y los TimeStamps están en el mismo orden, podemos utilizar los TimeStamps para obtener el rango de los ID. Examine la siguiente consulta:

SELECT ID, TS, Data
FROM TSOrder
WHERE 
     ID >= (SELECT TOP 1 ID FROM TSOrder WHERE TS >='2016-07-01 00:00:00.00000' ORDER BY TS ASC) AND 
     ID <= (SELECT TOP 1 ID FROM TSOrder WHERE TS <='2016-07-01 23:59:59.999999' ORDER BY TS DESC)

La nueva consulta se completa en 5.1 segundos, ¡y solo necesita 999,985 referencias globales**!  

Esta técnica puede aplicarse de una manera más práctica a las tablas con más campos indexados y a las consultas que tengan varias condicionales WHERE.  El rango del ID que se genera a partir de las subconsultas puede ponerse en un formato de mapa de bits, el cual genera una velocidad increíble cuando se obtiene una solución con varios índices.  La tabla Ens.MessageHeader es un excelente ejemplo donde podría aplicar este truco.

Seamos claros, este es el resultado de un EJEMPLO.  Si tiene muchas sentencias del condicional WHERE en la misma tabla (y estas están indexadas, ¡obvio!), ¡entonces esta técnica puede darle mucho MEJORES resultados! ¡Pruébela en sus consultas!  

* En realidad, los desarrolladores de SQL no detestan que conozca estas cosas, pero si algo nos enseñó Internet es que los anuncios llamativos atraen más tráfico.

** Cuando se prueban consultas que devuelven tantas filas, el SMP no puede administrarlas, y la mayor parte del tiempo se tardará en mostrar los datos.  La manera correcta de probarlas es con los métodos Embedded o Dynamic SQL, comprobando los resultados, pero sin emitirlos en función del tiempo, y usando SQL Shell para sus recuentos globales.  También puede utilizar las estadísticas de SQL para hacerlo.