none
Como optimizar subconsultas RRS feed

  • Pregunta

  • Hola buenos días, 
    Me gustaría saber si se puede optimizar de alguna manera esta Subconsulta.

    select 'HU', '201810', 225,count(* ),getdate()
    from TH_Hospitalizacion WITH (NOLOCK)
    where cod_centro = 'HU' and year(fec_alta)*100+month(fec_alta) = 201810 
    and xti_excluido ='n' and xti_tipoProceso='H'
    AND cod_Episodio IN (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
    INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
    AND B.COD_INDICADOR IN ('5913',
    '10965',
    '48128')
    and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10'))


    Un saludo y gracias de antemano.
    viernes, 26 de octubre de 2018 8:34

Respuestas

  • Hola Cargar datos dependiendo del día del mes en SQL

    A priori parece que la subconsulta, es una inner join.

    SELECT 'HU',
           '201810',
           225,
           COUNT(*),
           GETDATE()
    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio
         INNER JOIN TH_ValorIndicadorTomaFormulario_2 B ON A.cod_TomaFormulario = B.cod_TomaFormulario
                                                           AND B.COD_INDICADOR IN('5913', '10965', '48128')
                                                           AND CAST(a.des_ValorIndicador AS INT) BETWEEN 0 AND 10
    WHERE cod_centro = 'HU'
          AND YEAR(fec_alta) * 100 + MONTH(fec_alta) = 201810
          AND xti_excluido = 'n'
          AND xti_tipoProceso = 'H';
        
     
    He quitado los bloqueos, para facilitarme la lectura nada más, y desconozco la tabla de desvalorindicador, que seguro que no es A. pero a lo mejor te ayuda.

    viernes, 26 de octubre de 2018 10:42
  • Hola

    Muchas gracias, por su respuesta pero me salta un error cuando se intenta convertir el valor de des_valorindicador al tipo INT .

    El campo des_valorindicador  corresponde a la tabla TH_ValorIndicadorTomaFormulario_2, es te es el error

    Mens. 245, Nivel 16, Estado 1, Línea 2
    Error de conversión al convertir el valor nvarchar 'No Procede' al tipo de datos int.

     en cambio si lo dejo de la siguiente manerafunciona correctamente

    b.des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10')

    Por que puede estar ocurriendo?

    Un saludo y gracias.

    viernes, 26 de octubre de 2018 10:57
  • Para corregir el error deja la expresion tal como en tu query original pero con el alias de tabla adecuado.

    B.des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10')

    Mi sugerencia es que no manipules, entiendase por manipular a aplicar expresiones y/o funciones, sobre columnas que participaen en un JOIN o WHERE.  Esto hace que el optimizador no use indices existentes sobre esas columnas para analizar cardinalidad.

    -->  year(fec_alta)*100+month(fec_alta) = 201810

    fech_alta >= '20181001' and fec_alta < '20181101'


    AMB

    Some guidelines for posting questions...

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


    viernes, 26 de octubre de 2018 11:40
  • Muchas gracias a ambos pero sigo con el problema que al modificar la consulta no cuadra el dato con la consulta anterior.

    --CONSULTA ANTIGUA

    select 'HU', 201810, 225,count(* ),getdate()
    from TH_Hospitalizacion WITH (NOLOCK)
    where cod_centro = 'HU' and year(fec_alta)*100+month(fec_alta) = 201810 
    and xti_excluido ='n' and xti_tipoProceso='H'
    AND cod_Episodio IN (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
    INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
    AND B.COD_INDICADOR IN ('5913',
    '10965',
    '48128')
    and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10'))

    Resultado consulta antigua

    HU 201810  225 918  2018-10-26 13:25:16.380

    EN cambio si ejecuto la consulta que me ha mandado el resultado es distinto.

    --CONSULTA MODIFICADA

    SELECT 'HU',
           '201810',
           225,
           COUNT(*),
           GETDATE()
    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio
         INNER JOIN TH_ValorIndicadorTomaFormulario_2 B ON A.cod_TomaFormulario = B.cod_TomaFormulario
                                                           AND B.COD_INDICADOR IN('5913', '10965', '48128')
                                                           AND B.des_ValorIndicador  IN ('0','1','2','3','4','5','6','7','8','9', '10')
    WHERE t.cod_centro = 'HU'
          AND YEAR(fec_alta) * 100 + MONTH(fec_alta) = 201810
          AND xti_excluido = 'n'
          AND xti_tipoProceso = 'H';
       

    Resultado consulta modificada

    HU 201810  225 15086  2018-10-26 13:28:07.373

    Y la verdad que no encuentro el porque.

    Un saludo y gracias.

    viernes, 26 de octubre de 2018 11:47
  • Pregunta:

    En tu query original, la subconsulta puede retornar valores repetidos de COD_EPISODIO?

    Lo pregunto porque cuando usamos el operador IN, solo se comprueba que exista y no cuantas veces existe.  Cuando usamos JOIN es diferente.  Esto explica porque el resultado del COUNT es diferente en ambas sentencias.

    DECLARE @T1 table (col1 INT NOT NULL PRIMARY KEY);
    DECLARE @T2 table (col1 INT NOT NULL);
    
    INSERT INTO @T1 VALUES (1), (2), (3);
    INSERT INTO @T2 VALUES (1), (1), (1), (3);
    
    SELECT [@T1].col1
    FROM @T1
    WHERE [@T1].col1 IN (SELECT [@T2].col1 FROM @T2);
    
    SELECT [@T1].col1
    FROM @T1 INNER JOIN @T2 ON [@T2].col1 = [@T1].col1;

      Para nosotros poder sugerir una optimizacion, es necesario saber la estructura de las tablas involucradas incluyendo restricciones e indices. Tambien es importante incluir el plan de ejecucion.


    AMB

    Some guidelines for posting questions...

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

    viernes, 26 de octubre de 2018 12:14
  • El nuevo query usa el predicado sgte:

    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio

    Cual es el tipo de relacion entre estas dos tablas?

    1 - 1

    1 - Muchos


    AMB

    Some guidelines for posting questions...

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

    viernes, 26 de octubre de 2018 12:22
  • la relacion es de 1-N ya que un cod_Episodio puede tener varios cod_tomaformulario
    viernes, 26 de octubre de 2018 12:24
  • Muchas gracias, pero podría ponerme el ejemplo sobre mi consulta para poder enterderlo mejor, la consulta original es la siguiente, lo agradeceria bastante.

    select 'HU', 201810, 225,count(* ),getdate()
    from TH_Hospitalizacion WITH (NOLOCK)
    where cod_centro = 'HU' and year(fec_alta)*100+month(fec_alta) = 201810 
    and xti_excluido ='n' and xti_tipoProceso='H'
    AND cod_Episodio IN (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
    INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
    AND B.COD_INDICADOR IN ('5913',
    '10965',
    '48128')
    and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10'))

    Un saludo y muchas gracias de antemano por toda la ayuda presta.

    viernes, 26 de octubre de 2018 12:46
  • En este punto es dificil sugerir algo si no tenemos la estructura de las tablas incluyendo restricciones e indices.

    - clave primaria de cada tabla

    - restricciones de clave foranea

    - indices existentes

    - tipo de relacion entre las tablas involucradas

    Lo primero que recomiendo es que cambies el filtro por [fec_alta] como indique en respuesta anterior.

    Segundo que te asegures que cuentas con indeces que soporten la union de las tablas 

    TH_TomaFormulario

    TH_ValorIndicadorTomaFormulario_2

    asi como para los filtros que usas en el subquery.


    AMB

    Some guidelines for posting questions...

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


    viernes, 26 de octubre de 2018 13:10

Todas las respuestas

  • Hola Cargar datos dependiendo del día del mes en SQL

    A priori parece que la subconsulta, es una inner join.

    SELECT 'HU',
           '201810',
           225,
           COUNT(*),
           GETDATE()
    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio
         INNER JOIN TH_ValorIndicadorTomaFormulario_2 B ON A.cod_TomaFormulario = B.cod_TomaFormulario
                                                           AND B.COD_INDICADOR IN('5913', '10965', '48128')
                                                           AND CAST(a.des_ValorIndicador AS INT) BETWEEN 0 AND 10
    WHERE cod_centro = 'HU'
          AND YEAR(fec_alta) * 100 + MONTH(fec_alta) = 201810
          AND xti_excluido = 'n'
          AND xti_tipoProceso = 'H';
        
     
    He quitado los bloqueos, para facilitarme la lectura nada más, y desconozco la tabla de desvalorindicador, que seguro que no es A. pero a lo mejor te ayuda.

    viernes, 26 de octubre de 2018 10:42
  • Hola

    Muchas gracias, por su respuesta pero me salta un error cuando se intenta convertir el valor de des_valorindicador al tipo INT .

    El campo des_valorindicador  corresponde a la tabla TH_ValorIndicadorTomaFormulario_2, es te es el error

    Mens. 245, Nivel 16, Estado 1, Línea 2
    Error de conversión al convertir el valor nvarchar 'No Procede' al tipo de datos int.

     en cambio si lo dejo de la siguiente manerafunciona correctamente

    b.des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10')

    Por que puede estar ocurriendo?

    Un saludo y gracias.

    viernes, 26 de octubre de 2018 10:57
  • me he dado cuenta de otra cosa al ejecutar la primera consulta que envío me da el siguiente resultado

    select 'HU', 201810, 225,count(* ),getdate()
    from TH_Hospitalizacion WITH (NOLOCK)
    where cod_centro = 'HU' and year(fec_alta)*100+month(fec_alta) = 201810 
    and xti_excluido ='n' and xti_tipoProceso='H'
    AND cod_Episodio IN (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
    INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
    AND B.COD_INDICADOR IN ('5913',
    '10965',
    '48128')
    and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10'))

    ME da este resultado

    HU 201810 225 918 2018-10-26 13:25:16.380

    EN cambio si ejecuto la consulta que me ha mandado el resultado es distinto.

    SELECT 'HU',
           '201810',
           225,
           COUNT(*),
           GETDATE()
    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio
         INNER JOIN TH_ValorIndicadorTomaFormulario_2 B ON A.cod_TomaFormulario = B.cod_TomaFormulario
                                                           AND B.COD_INDICADOR IN('5913', '10965', '48128')
                                                           AND B.des_ValorIndicador  IN ('0','1','2','3','4','5','6','7','8','9', '10')
    WHERE t.cod_centro = 'HU'
          AND YEAR(fec_alta) * 100 + MONTH(fec_alta) = 201810
          AND xti_excluido = 'n'
          AND xti_tipoProceso = 'H';
        

    HU 201810 225 15086 2018-10-26 13:28:07.373

    Y la verdad que no encuentro el porque.

    Un saludo y gracias.

    viernes, 26 de octubre de 2018 11:30
  • Para corregir el error deja la expresion tal como en tu query original pero con el alias de tabla adecuado.

    B.des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10')

    Mi sugerencia es que no manipules, entiendase por manipular a aplicar expresiones y/o funciones, sobre columnas que participaen en un JOIN o WHERE.  Esto hace que el optimizador no use indices existentes sobre esas columnas para analizar cardinalidad.

    -->  year(fec_alta)*100+month(fec_alta) = 201810

    fech_alta >= '20181001' and fec_alta < '20181101'


    AMB

    Some guidelines for posting questions...

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


    viernes, 26 de octubre de 2018 11:40
  • Hola:

    CAST(a.des_ValorIndicador AS INT) BETWEEN 0 AND 10

    necesita que des_ValorIndicador sea un numero. Si dispone de nulos, podría ser:

    CAST(isnull(a.des_ValorIndicador,0) AS INT) BETWEEN 0 AND 10

    Opciones, que te pueden ayudar, por ejemplo crear un índice por las claves foraneas, caso de que no existan.

    por ejemplo

    viernes, 26 de octubre de 2018 11:41
  • Muchas gracias a ambos pero sigo con el problema que al modificar la consulta no cuadra el dato con la consulta anterior.

    --CONSULTA ANTIGUA

    select 'HU', 201810, 225,count(* ),getdate()
    from TH_Hospitalizacion WITH (NOLOCK)
    where cod_centro = 'HU' and year(fec_alta)*100+month(fec_alta) = 201810 
    and xti_excluido ='n' and xti_tipoProceso='H'
    AND cod_Episodio IN (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
    INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
    AND B.COD_INDICADOR IN ('5913',
    '10965',
    '48128')
    and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10'))

    Resultado consulta antigua

    HU 201810  225 918  2018-10-26 13:25:16.380

    EN cambio si ejecuto la consulta que me ha mandado el resultado es distinto.

    --CONSULTA MODIFICADA

    SELECT 'HU',
           '201810',
           225,
           COUNT(*),
           GETDATE()
    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio
         INNER JOIN TH_ValorIndicadorTomaFormulario_2 B ON A.cod_TomaFormulario = B.cod_TomaFormulario
                                                           AND B.COD_INDICADOR IN('5913', '10965', '48128')
                                                           AND B.des_ValorIndicador  IN ('0','1','2','3','4','5','6','7','8','9', '10')
    WHERE t.cod_centro = 'HU'
          AND YEAR(fec_alta) * 100 + MONTH(fec_alta) = 201810
          AND xti_excluido = 'n'
          AND xti_tipoProceso = 'H';
       

    Resultado consulta modificada

    HU 201810  225 15086  2018-10-26 13:28:07.373

    Y la verdad que no encuentro el porque.

    Un saludo y gracias.

    viernes, 26 de octubre de 2018 11:47
  • Pregunta:

    En tu query original, la subconsulta puede retornar valores repetidos de COD_EPISODIO?

    Lo pregunto porque cuando usamos el operador IN, solo se comprueba que exista y no cuantas veces existe.  Cuando usamos JOIN es diferente.  Esto explica porque el resultado del COUNT es diferente en ambas sentencias.

    DECLARE @T1 table (col1 INT NOT NULL PRIMARY KEY);
    DECLARE @T2 table (col1 INT NOT NULL);
    
    INSERT INTO @T1 VALUES (1), (2), (3);
    INSERT INTO @T2 VALUES (1), (1), (1), (3);
    
    SELECT [@T1].col1
    FROM @T1
    WHERE [@T1].col1 IN (SELECT [@T2].col1 FROM @T2);
    
    SELECT [@T1].col1
    FROM @T1 INNER JOIN @T2 ON [@T2].col1 = [@T1].col1;

      Para nosotros poder sugerir una optimizacion, es necesario saber la estructura de las tablas involucradas incluyendo restricciones e indices. Tambien es importante incluir el plan de ejecucion.


    AMB

    Some guidelines for posting questions...

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

    viernes, 26 de octubre de 2018 12:14
  • Buenas Hunchback

    Respecto a tu pregunta En tu query original, la subconsulta puede retornar valores repetidos de COD_EPISODIO?

    la subconsulta no retorna volores repetidos del COD_Episodio.

    Un saludo.


    viernes, 26 de octubre de 2018 12:17
  • El nuevo query usa el predicado sgte:

    FROM TH_Hospitalizacion t
         INNER JOIN TH_TomaFormulario A ON t.cod_Episodio = A.cod_episodio

    Cual es el tipo de relacion entre estas dos tablas?

    1 - 1

    1 - Muchos


    AMB

    Some guidelines for posting questions...

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

    viernes, 26 de octubre de 2018 12:22
  • la relacion es de 1-N ya que un cod_Episodio puede tener varios cod_tomaformulario
    viernes, 26 de octubre de 2018 12:24
  • Como dije anteriormente, eso es lo que causa que el conteo final se incremente.  No puedes convertir la sentencia que usa el operador IN a otra sentencia que use INNER JOIN entre esas dos tablas y seguir contando COUNT(*).  Fijate en el ejemplo que puse.

    una cosa que puedes hacer es contar los distintos valores de la clave primaria en la tabla TH_Hospitalizacion si esta formada por una unica columna.  Advierto que el usi de COUNT(DISTINCT ...) suele incurrir en peor desempenio que COUNT(*) ya que la entrada debe estar organizada o sorteada y si no lo esta entonces se insertara el operador SORT en el plan de ejecucion.


    AMB

    Some guidelines for posting questions...

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


    viernes, 26 de octubre de 2018 12:40
  • Muchas gracias, pero podría ponerme el ejemplo sobre mi consulta para poder enterderlo mejor, la consulta original es la siguiente, lo agradeceria bastante.

    select 'HU', 201810, 225,count(* ),getdate()
    from TH_Hospitalizacion WITH (NOLOCK)
    where cod_centro = 'HU' and year(fec_alta)*100+month(fec_alta) = 201810 
    and xti_excluido ='n' and xti_tipoProceso='H'
    AND cod_Episodio IN (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
    INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
    AND B.COD_INDICADOR IN ('5913',
    '10965',
    '48128')
    and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10'))

    Un saludo y muchas gracias de antemano por toda la ayuda presta.

    viernes, 26 de octubre de 2018 12:46
  • En este punto es dificil sugerir algo si no tenemos la estructura de las tablas incluyendo restricciones e indices.

    - clave primaria de cada tabla

    - restricciones de clave foranea

    - indices existentes

    - tipo de relacion entre las tablas involucradas

    Lo primero que recomiendo es que cambies el filtro por [fec_alta] como indique en respuesta anterior.

    Segundo que te asegures que cuentas con indeces que soporten la union de las tablas 

    TH_TomaFormulario

    TH_ValorIndicadorTomaFormulario_2

    asi como para los filtros que usas en el subquery.


    AMB

    Some guidelines for posting questions...

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


    viernes, 26 de octubre de 2018 13:10