none
Demora en consulta cuando se agregan condiciones en WHERE RRS feed

  • Pregunta

  • Hola a todos

    Tengo el siguiente escenario de ejemplo, la tabla real tiene 153893 registros

    DECLARE @t1 TABLE (id int,c1 datetime, codunico int )
    INSERT INTO @t1 VALUES(1,'20161219 06:59:59',1)
    ,(1,'20161219 06:59:59',1)
    ,(1,'20161219 06:59:59',2)
    ,(1,'20161219 07:00:00',1)
    ,(1,'20161219 07:00:00',2)
    ,(2,'20161219 07:00:01',1)
    ,(2,'20161219 07:00:01',2)
    ,(1,'20161219 07:00:00',1)
    ,(1,'20161219 07:00:00',2)
    ,(2,'20161219 07:00:01',1)
    ,(2,'20161219 07:00:01',2)

    Si ejecuto la consulta siguiente obtengo registros segun el WHERE en apenas 2 segundos

    SELECT CAST(c1 AS time(0)) HORA FROM @t1
    WHERE CONVERT(varchar(10),c1,120)=CONVERT(varchar(10),getdate(),120) AND 
    codunico NOT IN(SELECT min(codunico) FROM @t1
     GROUP BY id) 
     
    id HORA codunico
    1 06:59:59 2
    1 07:00:00 2
    2 07:00:01 2
    1 07:00:00 2
    2 07:00:01 2
     
    pero quiero obtener el COUNT() por rangos de hora por ejemplo de 06:01 hasta las 07:00 de forma que si ejecuto la consulta por ejemplo a las 7:10 solo obtenga los registros antes o hasta las 07:00 en punto, la consulta me queda así:
     
     
    SELECT CONVERT(varchar(2),HORA,108),COUNT(1) FROM
    ( SELECT CAST(c1 AS time(0)) HORA FROM @t1
    WHERE CONVERT(varchar(10),c1,120)=CONVERT(varchar(10),getdate(),120) AND 
    codunico NOT IN(SELECT min(codunico) FROM @t1
     GROUP BY id) ) AS T WHERE CAST(HORA AS time(0)) <= CAST('16:00' AS time(0))  GROUP BY (CONVERT(varchar(2),HORA,108) ) 

    la consulta devuelve los registros esperados pero se demora un minuto y a veces más. He hecho varias variantes como las que expongo a continuacion 


    ;WITH  cte0 AS (SELECT min(codunico) codunico FROM @t1 
    GROUP BY id )
    ,cte1 AS (
    SELECT CAST(c1 AS time(0)) HORA,codunico FROM @t1 WHERE (convert(varchar(10),c1,120)=convert(varchar(10),getdate(),120)
    AND cASt(c1 AS time(0) ) <= cASt('07:00' AS time(0))) 
    )
    SELECT HORA,COUNT(1) FROM cte1  /* este */ WHERE codunico NOT IN(SELECT codunico FROM cte0)
    GROUP BY HORA


    ;WITH  cte0 AS (
    SELECT CAST(c1 AS time(0) ) HORA FROM @t1
    WHERE convert(varchar(10),c1,120)=convert(varchar(10),getdate(),120) AND 
    codunico NOT IN(SELECT min(codunico) FROM @t1 
    GROUP BY id) )
    ,cte1 AS (
    SELECT HORA,cASt('07:00' AS time(0)) AS t FROM cte0 
    )
    SELECT HORA,COUNT(1) FROM cte1 /* este */ WHERE HORA <= t GROUP BY HORA   

    pero igual, en cuanto le agrego el ultimo WHERE se demora, ¿ Alguna sugerencia ?


    Saludos


    lunes, 19 de diciembre de 2016 17:32

Respuestas

  • Raimundo,

    Vamos por parte, pues comprederas que tratar de entender lo que tratas de hacer solo leyendo el query es un poco engorroso.

    La primera recomendacion que se te hico fue no manipular las columnas que participan en las clausulas JOIN / WHERE. Existe una excepcion y es convertir una columna datetime al tipo date donde todavia el optimizador puede hacer uso de las estadisticas (principalmente el histograma) en caso de existir algun indice por ella.

    ...
    WHERE (convert(varchar(10),c1,120)=convert(varchar(10),getdate(),120)AND cASt(c1 AS time(0) ) <= cASt('07:00' AS time(0))) 

    La primera expresion trata de comparar solo las fechas, correcto?

    Para eso puedes usar:

    ...
    WHERE cast(c1 as date) = cast(getdate() as date) AND cast(c1 AS time(0) ) <= cast('07:00' AS time(0))

    Si tienes un indice donde [c1] es la columna lider entonces este indice sera un buen candidato a usar para estimar cardinalidad.

    Lo otro que no comprendo bien es que al calcular el minimo [codunico] agrupas por [id] sin embargo esta columna no es parte de la salida de ese subquery pudiendo aparecer el mismo valor multiples veces segun sea el valor del [id].

    select min(codunico) min_cu from @T group by id

    Quizas pudieras usar:

    select distinct min_cu
    from (select min(codunico) as min_cu from @T group by id) as R
    where min_cu is not null

    Pero ademas pudieras probar volcar ese resultado hacia una tabla temporal con indice unico y agrupado por ese valor para luego usarla en el query posterior (muchas veces resulta mas facil para el optimizador estimar cardinalidad de esta forma ya que el query se hace mas simple).

    create table #T2(codunico int not null primary key);

    insert into #T (codunico)
    select distinct min_cu
    from (select min(codunico) as min_cu from @T group by id) as R
    where min_cu is not null;

    Ahora puedes usar NOT IN o LEFT OUTER JOIN ... WHERE ? is null, pero recuerda convertir la columna [c1] al tipo date en vez de usar CONVERT(varchar...).

    cte1 AS (
    SELECT
        CAST(c1 AS time(0)) HORA,
        codunico
    FROM
        @t1
    WHERE
        cast(c1 as date) = cast(getdate() as date)

        AND cast(c1 AS time(0) ) <= cast('07:00' AS time(0))
    )
    SELECT HORA,COUNT(1)
    FROM cte1  /* este */
    WHERE codunico NOT IN (SELECT codunico FROM #T)

    GROUP BY HORA;

    Si es posible postea los planes de ejecucion para compararlos (*.sqlplan).


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Editado HunchbackMVP martes, 20 de diciembre de 2016 14:31
    • Marcado como respuesta Raimundo Ferrer martes, 20 de diciembre de 2016 22:05
    martes, 20 de diciembre de 2016 14:27

Todas las respuestas

  • Raimundo Ferrer,

    Debes evitar operar con cualquier función la columna [c1], entiendo debería estar indexada o debería ser candidata a ser indexada (junto con la columna codunico):

    DECLARE @t1 TABLE (id int, c1 datetime, codunico int)
    INSERT INTO @t1 VALUES(1,'20161219 06:59:59',1)
    ,(1,'20161219 06:59:59',1)
    ,(1,'20161219 06:59:59',2)
    ,(1,'20161219 07:00:00',1)
    ,(1,'20161219 07:00:00',2)
    ,(2,'20161219 07:00:01',1)
    ,(2,'20161219 07:00:01',2)
    ,(1,'20161219 07:00:00',1)
    ,(1,'20161219 07:00:00',2)
    ,(2,'20161219 07:00:01',1)
    ,(2,'20161219 07:00:01',2)
    
    DECLARE @MinCodUnico int = (SELECT MIN(codunico) FROM @t1);
    
    /*Retorna los registros realizados el día actual*/
    SELECT 
        *
    FROM 
        @t1 t1
    WHERE   
        (t1.c1 >= CONVERT(date, GETDATE()) AND t1.c1 < DATEADD(DAY, 1, GETDATE()))    
        AND (t1.codunico > @MinCodUnico );
    
    
    /*Retorna la cuenta de registros realizados en un rango de horas*/
    DECLARE @FechaInicio datetime ='19000101 06:01:00'
    DECLARE @FechaFin datetime ='19000101 07:00:00'
    SELECT 
        DATEADD(DAY, DATEDIFF(DAY, @FechaInicio, GETDATE()), @FechaInicio) AS [DESDE],
        DATEADD(DAY, DATEDIFF(DAY, @FechaFin, GETDATE()), @FechaFin) AS [HASTA],
        COUNT(*) AS [CUENTA]
    FROM 
        @t1 t1
    WHERE         
        (t1.c1 BETWEEN DATEADD(DAY, DATEDIFF(DAY, @FechaInicio, GETDATE()), @FechaInicio)
    	   AND DATEADD(DAY, DATEDIFF(DAY, @FechaFin, GETDATE()), @FechaFin))
        AND (t1.codunico > @MinCodUnico );
    GO

    En caso no tengas un índice para las columnas [c1] y [codunico]

    CREATE NONCLUSTERED INDEX IX_NombreIndice ON dbo.NombreTabla (c1, codunico);
    GO


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    lunes, 19 de diciembre de 2016 18:20
  • La sugerencia hecha por Willams de no manocear las columnas que participan en las clausulas JOIN / WHERE es una buena practica para mejor uso de las estadisticas de los indices, aunque verdad que el problema no es tan facil.

    Si te entendi correctamente, podrias calcular un identificador de grupo dado por la substraccion de un segundo y luego calcular la hora. El resto seria agrupar por esta columna calculada y contar.

    DECLARE @t1 TABLE (
    id int,
    c1 datetime, 
    codunico int
    );
    
    INSERT INTO @t1 VALUES(1,'20161219 06:59:59',1)
    ,(1,'20161219 06:59:59',1)
    ,(1,'20161219 06:59:59',2)
    ,(1,'20161219 07:00:00',1)
    ,(1,'20161219 07:00:00',2)
    ,(2,'20161219 07:00:01',1)
    ,(2,'20161219 07:00:01',2)
    ,(1,'20161219 07:00:00',1)
    ,(1,'20161219 07:00:00',2)
    ,(2,'20161219 07:00:01',1)
    ,(2,'20161219 07:00:01',2)
    ,(2,'20161219 08:00:00',2)
    ,(2,'20161219 08:00:01',2)
    ;
    
    DECLARE @min_codunico int;
    
    SET @min_codunico = (SELECT MIN(codunico) FROM @t1);
    
    SELECT
    	CAST(MIN(DATEADD(HOUR, T2.grp_helper, '00:00:01')) AS time(0)) AS c1_from,
    	CAST(MIN(DATEADD(HOUR, T2.grp_helper + 1, '00:00:00')) AS time(0)) AS c1_to,
    	COUNT(*) AS cnt
    FROM
    	@t1 AS T1
    	CROSS APPLY
        (VALUES (DATEPART(HOUR, DATEADD(SECOND, -1, CAST(c1 AS time(0)))))) AS T2(grp_helper)
    WHERE
    	T1.codunico > @min_codunico
    	AND T2.grp_helper < DATEPART(HOUR, DATEADD(SECOND, -1, CAST(CURRENT_TIMESTAMP AS time(0))))
    GROUP BY
    	T2.grp_helper
    OPTION (RECOMPILE);
    GO

    Como trabajas con tiempo entonces nunca podrias ver que paso a partir de las '23:01' en adelante aunque uses el sgte filtro:

    SELECT
    	CAST(MIN(DATEADD(HOUR, T2.grp_helper, '00:00:01')) AS time(0)) AS c1_from,
    	CAST(MIN(DATEADD(HOUR, T2.grp_helper + 1, '00:00:00')) AS time(0)) AS c1_to,
    	COUNT(*) AS cnt
    FROM
    	@t1 AS T1
    	CROSS APPLY
        (VALUES (DATEPART(HOUR, DATEADD(SECOND, -1, CAST(c1 AS time(0)))))) AS T2(grp_helper)
    WHERE
    	T1.codunico > @min_codunico
    	AND CAST(T1.c1 AS time(0)) < CAST(CURRENT_TIMESTAMP AS time(0))
    GROUP BY
    	T2.grp_helper
    OPTION (RECOMPILE);
    GO

    No se si jugando con '00' horas equivalente a '24' y usar expresiones CASE para tomar en cuenta el orden.

    Otra posibilidad seria no poner restriccion por hora sino solo el dia y seguir agrupando por hora.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    lunes, 19 de diciembre de 2016 20:20
  • Hola

    Primero que todo agradezco mucho vuestras respuestas, pero seguía sin quedarme claro por qué la demora y no era exactamente lo que quería obtener, el ejemplo solo incluía 1 segundo pasada la hora pero en la realidad no es así, por ejemplo un registro de las 07:00:59 ya no debía de estar en el rango de las 7 horas; perdonen entonces no haber explicado bien el asunto. Me remito a mi primera variante con CTE para que entendais un poco más mi duda.

    Mi tabla @t1 real, que es física por supuesto, tiene en este instante 154,840 registros y "SELECT min(codunico) codunico FROM @t1 GROUP BY id" me devuelve 67,538 que estarían en cte0 y por eso no entiendo bien la demora, igual y una "tabla común" no debe de tener tantos registros. En el código


    ;WITH  cte0 AS (SELECT min(codunico) codunico FROM @t1 
    GROUP BY id )
    ,cte1 AS (
    SELECT CAST(c1 AS time(0)) HORA,codunico FROM @t1 WHERE (convert(varchar(10),c1,120)=convert(varchar(10),getdate(),120)
    AND cASt(c1 AS time(0) ) <= cASt('07:00' AS time(0))) 
    )
    SELECT HORA,COUNT(1) FROM cte1  /* este */ WHERE codunico NOT IN(SELECT codunico FROM cte0)
    GROUP BY HORA

    si no agrego el WHERE se ejecuta en segundos y manipulo fechas con funciones pero nada mas agregar el NOT IN se demora, y mucho.

    Seguí buscando soluciones y encontré una que estaba en mis narices y no veía, usar LEFT JOIN y despreciar los registros que en la derecha el valor del ON fuera NULL, por tanto mi consulta queda así y no llega al segundo ¿ que cosas eh ?. Aprovecho el tema para que si alguíen sabe porque los dos comportamientos son tan diferentes para obtener lo mismo lo comparta porque en realidad me tiene muy desconcertado.


    SELECT DATEPART(hour,hora),COUNT(1) from (
    SELECT CAST(c1 AS time(0)) HORA FROM @t1 h
    LEFT JOIN ( SELECT MIN(codunico) codunico FROM @t1 GROUP BY id) AS t on t.codunico = h.codunico 
    WHERE (CONVERT(varchar(10),c1,120)='2016-12-19' AND cASt(c1 AS time(0) ) <= '07:00' and t.codunico is null)
    ) AS T GROUP BY DATEPART(hour,hora)



    Gracias una vez más y saludos a todos


    martes, 20 de diciembre de 2016 13:04
  • Raimundo,

    Vamos por parte, pues comprederas que tratar de entender lo que tratas de hacer solo leyendo el query es un poco engorroso.

    La primera recomendacion que se te hico fue no manipular las columnas que participan en las clausulas JOIN / WHERE. Existe una excepcion y es convertir una columna datetime al tipo date donde todavia el optimizador puede hacer uso de las estadisticas (principalmente el histograma) en caso de existir algun indice por ella.

    ...
    WHERE (convert(varchar(10),c1,120)=convert(varchar(10),getdate(),120)AND cASt(c1 AS time(0) ) <= cASt('07:00' AS time(0))) 

    La primera expresion trata de comparar solo las fechas, correcto?

    Para eso puedes usar:

    ...
    WHERE cast(c1 as date) = cast(getdate() as date) AND cast(c1 AS time(0) ) <= cast('07:00' AS time(0))

    Si tienes un indice donde [c1] es la columna lider entonces este indice sera un buen candidato a usar para estimar cardinalidad.

    Lo otro que no comprendo bien es que al calcular el minimo [codunico] agrupas por [id] sin embargo esta columna no es parte de la salida de ese subquery pudiendo aparecer el mismo valor multiples veces segun sea el valor del [id].

    select min(codunico) min_cu from @T group by id

    Quizas pudieras usar:

    select distinct min_cu
    from (select min(codunico) as min_cu from @T group by id) as R
    where min_cu is not null

    Pero ademas pudieras probar volcar ese resultado hacia una tabla temporal con indice unico y agrupado por ese valor para luego usarla en el query posterior (muchas veces resulta mas facil para el optimizador estimar cardinalidad de esta forma ya que el query se hace mas simple).

    create table #T2(codunico int not null primary key);

    insert into #T (codunico)
    select distinct min_cu
    from (select min(codunico) as min_cu from @T group by id) as R
    where min_cu is not null;

    Ahora puedes usar NOT IN o LEFT OUTER JOIN ... WHERE ? is null, pero recuerda convertir la columna [c1] al tipo date en vez de usar CONVERT(varchar...).

    cte1 AS (
    SELECT
        CAST(c1 AS time(0)) HORA,
        codunico
    FROM
        @t1
    WHERE
        cast(c1 as date) = cast(getdate() as date)

        AND cast(c1 AS time(0) ) <= cast('07:00' AS time(0))
    )
    SELECT HORA,COUNT(1)
    FROM cte1  /* este */
    WHERE codunico NOT IN (SELECT codunico FROM #T)

    GROUP BY HORA;

    Si es posible postea los planes de ejecucion para compararlos (*.sqlplan).


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Editado HunchbackMVP martes, 20 de diciembre de 2016 14:31
    • Marcado como respuesta Raimundo Ferrer martes, 20 de diciembre de 2016 22:05
    martes, 20 de diciembre de 2016 14:27
  • Raimundo,

    Vamos por parte, pues comprederas que tratar de entender lo que tratas de hacer solo leyendo el query es un poco engorroso.

    La primera recomendacion que se te hico fue no manipular las columnas que participan en las clausulas JOIN / WHERE. Existe una excepcion y es convertir una columna datetime al tipo date donde todavia el optimizador puede hacer uso de las estadisticas (principalmente el histograma) en caso de existir algun indice por ella.

    ...
    WHERE (convert(varchar(10),c1,120)=convert(varchar(10),getdate(),120)AND cASt(c1 AS time(0) ) <= cASt('07:00' AS time(0))) 

    La primera expresion trata de comparar solo las fechas, correcto?

    Para eso puedes usar:

    ...
    WHERE cast(c1 as date) = cast(getdate() as date) AND cast(c1 AS time(0) ) <= cast('07:00' AS time(0))

    Si tienes un indice donde [c1] es la columna lider entonces este indice sera un buen candidato a usar para estimar cardinalidad.

    Lo otro que no comprendo bien es que al calcular el minimo [codunico] agrupas por [id] sin embargo esta columna no es parte de la salida de ese subquery pudiendo aparecer el mismo valor multiples veces segun sea el valor del [id].

    select min(codunico) min_cu from @T group by id

    Quizas pudieras usar:

    select distinct min_cu
    from (select min(codunico) as min_cu from @T group by id) as R
    where min_cu is not null

    Pero ademas pudieras probar volcar ese resultado hacia una tabla temporal con indice unico y agrupado por ese valor para luego usarla en el query posterior (muchas veces resulta mas facil para el optimizador estimar cardinalidad de esta forma ya que el query se hace mas simple).

    create table #T2(codunico int not null primary key);

    insert into #T (codunico)
    select distinct min_cu
    from (select min(codunico) as min_cu from @T group by id) as R
    where min_cu is not null;

    Ahora puedes usar NOT IN o LEFT OUTER JOIN ... WHERE ? is null, pero recuerda convertir la columna [c1] al tipo date en vez de usar CONVERT(varchar...).

    cte1 AS (
    SELECT
        CAST(c1 AS time(0)) HORA,
        codunico
    FROM
        @t1
    WHERE
        cast(c1 as date) = cast(getdate() as date)

        AND cast(c1 AS time(0) ) <= cast('07:00' AS time(0))
    )
    SELECT HORA,COUNT(1)
    FROM cte1  /* este */
    WHERE codunico NOT IN (SELECT codunico FROM #T)

    GROUP BY HORA;

    Si es posible postea los planes de ejecucion para compararlos (*.sqlplan).


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    Hola Hunchback

    Gracias por responder, tus consideraciones han sido muy esclarecedoras, sobre todo lo de hacer comparaciones sin convertir a VARCHAR si puedo usar CAST y lo de crear tablas temporales con índices. Lo del codunico te lo explico con un ejemplo, imagina que tienes un almacén y en algun momento te entra un item nuevo, trae un código de producto (id), una cantidad, un precio y un código que es único en la tabla para todos los productos, y aleatorio, que representa cada entrada específica, algo así

    id,cantidad,precio,fechaentrada,codunico
    ---------------------------------------------------
    1, 1, 1.0,'19/12/2016 08:00',5
    1, 2, 1.5,'19/12/2016 09:00',7
    1, 3, 2.0,'20/12/2016 10:00',10

    y se quiere, bajo ciertas circuntancias, saber las "evoluciones" de los productos sin tener en cuenta la primera vez que entró. Es un ejemplo muy sencillo con quizás otra solución.

    Finalmentee he mirado en el SSMS los planes y son iguales, en el caso donde se usa el CAST me sugiere que cree el indice:

    "CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[TablaReal] ([ALTA_F]) INCLUDE ([CODUNICO])"


    Reitero mi agradecimiento a ti y a William, si os parece doy por cerrado el tema. Saludos cordiales.



    martes, 20 de diciembre de 2016 16:04
  • Hola Raimundo,

    Puedes dejar el tema por cerrado. Lo importante es que comprendas las sugerencias dadas y te sean util en lo adelante.

    El problema parece interesante y estoy seguro que si posteas DDL, incluyendo data de ejemplo en forma de sentencias INSERT, asi como el resultado esperado, alguien en el foro dara sugerencias de como resolverlo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 20 de diciembre de 2016 18:51
  • Hola Raimundo,

    Puedes dejar el tema por cerrado. Lo importante es que comprendas las sugerencias dadas y te sean util en lo adelante.

    El problema parece interesante y estoy seguro que si posteas DDL, incluyendo data de ejemplo en forma de sentencias INSERT, asi como el resultado esperado, alguien en el foro dara sugerencias de como resolverlo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Hola

    Si, me ha servido de mucho, de hecho probé con lo que me sugeriste de la tabla temporal y usar el CAST  y se ejecuta todo muy rápido y obtengo lo que quiero. Saludos

    • Marcado como respuesta Raimundo Ferrer martes, 20 de diciembre de 2016 22:05
    • Desmarcado como respuesta Joyce_ACModerator miércoles, 21 de diciembre de 2016 19:12
    martes, 20 de diciembre de 2016 22:05