[SOLUCIONADO] Performance Fecha Where Between vs Equal

[Huasi]

Estimados como estan.
Les quiero consultar sobre una cuestión de performance.
Tengo una consulta que esta demorando más de 4hs, algunas veces mucho más y la idea es hacerla más eficiente.
Para ello se me ocurre hacer foco en la cláusula Where.
se trata de un job que se ejecuta para realizar ciertos procesos sobre los registros del día. Dentro del Where tengo un between sobre una fecha desde y una fecha hasta.
Al ser tan pesada la tabla y manejar tanta cantidad de registros la consulta se ejecuta a diario y se procesa un solo día, por lo tanto se me ocurre reemplazar:

Fecha Between @Desde And @Hasta
por
Fecha = @fecha

¿Creen que esa modificación puede optimizar los tiempos de ejecución?
O se trata de un mito como decían del >= vs Between (lo probe infinidad de veces y obtuve siempre resultados idénticos)

[Libras]

Depende del numero de registros que regreses con un where y con otro, ahora que manejador de bases de datos usas? cual es el plan de ejecucion de tu consulta?, tienes indices?

[Huasi]

Utilizo SQL Server.
Tuve que indizar la columna Fecha sobre la que aplico el filtro con el Where.
En este momento la estoy corriendo y lleva casi 3hs de ejecución.
La tabla tiene 167millones de registros.
Por día se escriben cerca de 7500 registros que son los que se levantan con la consulta.
El campo fecha es un datetime que escribe fecha hora, utilizo el formato 121.
Como es tan pesada la ejecución no se utiliza nunca para un rango de fechas, por lo cual da igual cambiar el between por equal en caso de que sirva para algo.
Actualmente esta asi:

Código SQL:

WHERE CONVERT(DATETIME,CONVERT(VARCHAR(10),Fecha, 121), 121) BETWEEN    
CONVERT(DATETIME,CONVERT(VARCHAR(10), @fechaDesde, 121), 121) AND 
CONVERT(DATETIME,CONVERT(VARCHAR(10), @fechaHasta, 121), 121)
 

La idea sería convertirlo a esto:

Código SQL:

WHERE
CONVERT(VARCHAR(10),Fecha, 121) = CONVERT(VARCHAR(10), @fechaDesde, 121)
 

Siempre y cuando el cambio permita optimizar los tiempos de ejecución.

[Libras]

porque haces una conversion en el where? ya revisaste tu plan de ejecucion? los rangos de fecha en el between cuales son? muestras los 167,000,000 en pantalla? Podrias poner tu query?

[Huasi]

porque haces una conversion en el where?
La conversión del parámetro es opcional, la conversión de la columna es porque realizo un agrupamiento entonces debo recortar la hora y dejar solamente la fecha.

los rangos de fecha en el between cuales son?
La consulta se ejecuta en mas de 150 bases de datos, todas idénticas.
Según el caso, hay bases de datos cuya tabla a la que apunta la consulta tiene 20mil registros y hay otros casos que la tabla tiene alrededor de 200millones de registros.
Para las tablas de poco volumen se puede establecer un rango de fechas y es por eso que se utiliza el between. Para las tablas grandes se corre el proceso únicamente para el día anterior y es ahí donde reemplazaría Between por =

muestras los 167,000,000 en pantalla?
Nunca. La consulta no muestra datos, lo que hace es procesar la información que se genera en el día y escribirla en otra tabla mediante un script con manejo de cursor. Puntualmente busca valores máximos por cada día, dispositivo y tarifa y los escribe en otra tabla.

Podrias poner tu query?
No tengo problema en hacerlo pero me no me deja, me manda a la pantalla de captcha, lo realizo y queda en blanco. Intento ponerla desglosada en partes y tampoco, sea pegada aca o en los diferentes highlight. Voy a intentar escribirla en un proximo mensaje

[Huasi]

Código SQL:

SET @query = '  DECLARE cLector CURSOR FOR
SELECT      P.numero, P.tarifa, fechaMaxPotencia.fechahora, P.Potencia
FROM        (SELECT L.numero, L.tarifa, CONVERT(DATETIME,CONVERT(VARCHAR(10), L.fechahora, 121),121) as fechahora, MAX(L.Potencia) AS Potencia
            FROM    Lector L WITH(NOLOCK)
            WHERE   CONVERT(DATETIME,CONVERT(VARCHAR(10),FechaHoraIncorporacion, 121), 121) BETWEEN 
                    CONVERT(DATETIME,CONVERT(VARCHAR(10), '''+@fechaDesde+''', 121), 121) AND 
                    CONVERT(DATETIME,CONVERT(VARCHAR(10), '''+@fechaHasta+''', 121), 121)
GROUP BY    L.numero, L.tarifa, CONVERT(DATETIME,CONVERT(VARCHAR(10), L.FechaHora, 121),121)) P
CROSS APPLY dbo.f_fecha_max_potencia(CONVERT(DATETIME,CONVERT(VARCHAR(10), P.fechahora, 121),121), P.numero, P.tarifa, P.potencia) as fechaMaxPotencia '
 

[Libras]

Código SQL:

SELECT L.numero, L.tarifa, CONVERT(DATETIME,CONVERT(VARCHAR(10), L.fechahora, 121),121) AS fechahora, MAX(L.Potencia) AS Potencia
            FROM    Lector L WITH(NOLOCK)
            WHERE   CONVERT(DATETIME,CONVERT(VARCHAR(10),FechaHoraIncorporacion, 121), 121) BETWEEN 
                    CONVERT(DATETIME,CONVERT(VARCHAR(10), '''+@fechaDesde+''', 121), 121) AND 
                    CONVERT(DATETIME,CONVERT(VARCHAR(10), '''+@fechaHasta+''', 121), 121)
GROUP BY    L.numero, L.tarifa, CONVERT(DATETIME,CONVERT(VARCHAR(10), L.FechaHora, 121),121)
 

Las conversiones que haces en el where afectan el rendimiento de esta consulta ya que al hacer el convert ya no usa los indices de la tabla


Haces un:

Código SQL:

CROSS APPLY dbo.f_fecha_max_potencia(CONVERT(DATETIME,CONVERT(VARCHAR(10), P.fechahora, 121),121)
 

Eso indica que por cada valor de la tabla va a mandar llamar a la funcion, ahora tienes un cursor, ese para que es?

[Huasi]

Hago las conversiones porque la tabla Lector guarda la fecha con la hora y yo necesito obtener los registros agrupados por numero, día y tarifa.
Como lo mencione antes, por cada día hay cerca de 7500 registros.
La función me devuelve la fecha con hora del momento en que se registra el valor máximo.
El resultado de convertir la fecha con varchar(10) es
2018-10-29 14:30:00.000 -> 2018-10-29
Me preocupa lo que me decís de que el índice queda obsoleto luego de la conversión, ya que estoy gastando mucho espacio físico en la creación del mismo y necesito que funcionen.
El cursor lo utilizo porque por cada dato de valor máximo que obtengo necesito realizar varias comprobaciones que siguen diferentes circuitos lógicos según el caso. El script tiene aprox. 200 líneas pero la query por la cual estoy consultando es la parte que más demora.
Algo así sería la tabla lector:

Nro -- fechahora ------------- Tarifa Potencia
15541 2018-10-29 14:30:00.000 2715 3468
15541 2018-10-29 14:15:00.000 2715 3048
15541 2018-10-29 14:00:00.000 2715 270
15541 2018-10-29 13:45:00.000 2715 222
15541 2018-10-29 13:30:00.000 2715 66
15541 2018-10-29 13:15:00.000 2715 144
15541 2018-10-29 13:00:00.000 2715 1890
15541 2018-10-29 12:45:00.000 2715 1380
15541 2018-10-29 12:30:00.000 2715 918
15541 2018-10-29 12:15:00.000 2715 822

[Libras]

y porque no usas fechas completas

Código SQL:

BETWEEN 01/01/2010 00:00 AND 01/02/2010 23:59
 

El indice va a ocupar espacio dependiendo el numero de renglones de tu tabla, nada mas tienes una columna en ese indice? ya revisaste el plan de ejcucion de ese query?

[Huasi]

Me fije en el plan de ejecución y tal cual, el índice no funcionaba.
Logré solucionarlo aplicando las siguientes modificaciones:

• Aplicando el índice tal cual me lo sugería el plan de ejecución
• Cambiando Between por =
• Utilizando CONVERT(DATE, @Fecha) y CONVERT(DATE, columnaFecha) en lugar de lo que hacía antes

Llego a la conclusión que Between demora mucho más que EQUAL.
Para el mismo rango Desde Hasta utilizar Between la consulta demora muchisimo más que hacerlo igualando.
Para el caso en cuestión, al ser un job que se ejecuta a diario ambas formas son válidas.

Gracias Libras me sirvió de mucho tu ayuda.