none
Optimizacion de Query

    Pregunta

  • Una pregunta, he revisado mis tiempos de ejecucion y no logro entender por que este case

    case 
    when edad_reg between 40 and 69 and (id_tipitem='D' and cod_item='Z123' and valor_lab='N') then 1
    when edad_reg between 40 and 69 and (id_tipitem='D' and cod_item='Z123' and valor_lab='A') then 2
    when edad_reg between 50 and 69 and (id_tipitem='D' and cod_item='77057' and valor_lab='N') then 3
    when edad_reg between 50 and 69 and (id_tipitem='D' and cod_item='77057' and valor_lab='A') then 4
    when edad_reg between 18 and 75 and (id_tipitem='D' and cod_item in ('77055','77056') and valor_lab='N') then 5
    when edad_reg between 18 and 75 and (id_tipitem='D' and cod_item in ('77055','77056') and valor_lab='A') then 6
    end,

    where id_tipedad_reg='A' and edad_reg between 20 and 70 and
    (id_tipitem='D' and valor_lab in('N','A') and cod_item in ('Z123','77057','77055','77056'))

    Es mas eficiente que este

    case 
    when edad_reg between 40 and 69 and cod_item='Z123' and valor_lab='N') then 1
    when edad_reg between 40 and 69 and cod_item='Z123' and valor_lab='A') then 2
    when edad_reg between 50 and 69 and cod_item='77057' and valor_lab='N') then 3
    when edad_reg between 50 and 69 and cod_item='77057' and valor_lab='A') then 4
    when edad_reg between 18 and 75 and cod_item in ('77055','77056') and valor_lab='N') then 5
    when edad_reg between 18 and 75 and cod_item in ('77055','77056') and valor_lab='A') then 6
    end, 

    where id_tipedad_reg='A' and edad_reg between 20 and 70 and
    (id_tipitem='D' and valor_lab in('N','A') and cod_item in ('Z123','77057','77055','77056'))

    Yo asumi que como en la clausula where ya estaba filtrado el id_tipitem, seria mejor quitarlo de la consulta y le quite los parentesis ya que son varios and pense que era lo mismo y me arroja el mismo resultado. Pero el tiempo es sustancialmente mayor (20 segudos), es lo que no entiendo.

    Según el plan de ejecución, en la primera consulta (optima) casi toda la consulta la jala de un RID LOOKUP, en cambio en la modificada toda la jala de un Index Seek (10) pero este esta filtrado y el filtrado es de 89%

    Por favor alguien que pueda ayudarme con esto para mejorar en la optimizacion de mis consultas.

    jueves, 17 de mayo de 2018 18:07

Todas las respuestas

  • Pudieras postear el plan de ejecucion?

    Aca un link muy util para compartir planes de ejecucion.

    https://www.brentozar.com/pastetheplan/


    AMB

    Some guidelines for posting questions...

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

    jueves, 17 de mayo de 2018 18:24

  • Gracias por tu pronta respuesta.

    El plan de ejecucion sin la modificacion es este

    https://www.brentozar.com/pastetheplan/?id=ryTmvLoCM

    Y luego de la modificacion es este

    https://www.brentozar.com/pastetheplan/?id=B1ppwUi0M

    Muchas Gracias, y por cierto, muy buena la web. Saludos


    jueves, 17 de mayo de 2018 19:34
  • Segun los planes de ejecucion, en el que referencias la columna en el WHERE y la sentencia CASE el optimizador fue capaz de empujar ambas expresiones CASE en el filtro que sigue al operador INDEX SEEK.

    Mientras que en la sentencia donde eliminas la referencia a la columna [id_tipitem] en la expresion CASE el optimizador filtra el flujo dos veces, una despues del INDEX SEEK y la otra despues del NESTED LOOP. Yo no puedo explicar por que el optimizador hace el cambio.

    Habria que preguntarle a un experto como Paul White, quien sabe hacer debug del codigo que usa el optimizador para generar el plan.

    De todas maneras, el indice usado podria mejorarse para evitar el [RID Lookup] y que no haya predicado residual en el INDEX SEEK.

    Trata creando este indice:

    CREATE NONCLUSTERED INDEX [idx_nc_dtsg_NIÑO_RPT_12_01] 
    ON [dbo].[TRAMAHIS_DTSG] (
    	[cod_item],
    	[id_tipedad_reg],
    	[id_tipitem],
    	[edad_reg],
    	[valor_lab]
    	)
    INCLUDE ( 
    	[id_cita],
    	renaes, 
    	periodo, 
    	fichafam, 
    	ubigeo, 
    	id_genero, 
    	id_financiador, 
    	id_etnia, 
    	id_profesional, 
    	id_persona,
    	)
    GO

    En la llave del indice trata de usar como primera columna la que mas valores distintos tenga entre las columnas que la conforman.

    	[cod_item],
    	[id_tipedad_reg],
    	[id_tipitem],
    	[edad_reg],
    	[valor_lab]


    AMB

    Some guidelines for posting questions...

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

    jueves, 17 de mayo de 2018 20:46
  • Sabes, intente hacer mejorar el indice como me comentabas y probe haciendo un indice filtrado, llego a mas del doble de tiempo (con index Seek)

    Pero lo que me causo duda es el plan de ejecucion me muestra que el index seek esta filtrado.

    Me sale un filter el cual es equivalente al 99% de costo del index seek.

    Creo que en ese caso debo optimizar el where cierto?

    En realidad soy nuevo y son muchas cosas, es muy complejo.

    Gracias hunchback

    jueves, 17 de mayo de 2018 21:49
  • Te adjunto el plan de ejecucion por que me esta pasando en 5 procedimientos almacenados y creo que si resuelvo esto optimizaria muchisimo.

    Modifique el indice, fue un indice filtrado que encaja en varios stores.

    create  INDEX [idx_nc_dtsg_NIÑO_RPT_12_01_ALEX] ON [dbo].[TRAMAHIS_DTSG]
    (
    [cod_item] ASC
    )
    INCLUDE ( [edad_reg],
    [id_tipedad_reg],
    [id_cita],
    [valor_lab],
    [id_tipitem]) 

    where
    id_tipedad_reg='A' and
    id_tipitem='D'and
    valor_lab in ('A','N') and
    cod_item in ('82270','Z125','84152','Z128')



    El codigo de Item es el valor que mas disperso es como me recomendaste (creo que es el mas disperso), hay algun contador para dispersión?

    Bueno siguiendo al tema, este indice logro un index seek pero filtrado,

    Este es el plan es de el procedimiento con el indice sin modificar

    https://www.brentozar.com/pastetheplan/?id=rJwzNKsRz

    Y este plan es cuando modifique el indice, le puse otro nombre para diferenciarlos y no estropear trabajo.

    https://www.brentozar.com/pastetheplan/?id=HkFdrKiAM

    Veo que usa un index seek y eso es positivo, pero en filter es lo que me ocaciona un gran problema ya que tarda casi lo mismo en algunos procedimientos demora un poco mas.

    Esto me serviria muchisimo ya que como te comente tengo varios procedimientos que se optimizarían con este indice.

    Gracias!
    jueves, 17 de mayo de 2018 22:46
  • >  Veo que usa un index seek y eso es positivo, pero en filter es lo que me ocaciona

    >  un gran problema ya que tarda casi lo mismo en algunos procedimientos demora

    >  un poco mas.

    En el plan de ejecucion, has click en el operador FILTER del que hablas y presiona F4 para habrir la ventana de propiedades.  En esa ventana busca la penultima fila, llamada Predicate y ve su contenido (el contenido del filtro).

    Me llama mucho la atencion que la columna [edad_reg] es convertida a integer constantemente.  Cual es su tipo de data?

    Probastes el ultimo indice que sugeri?



    AMB

    Some guidelines for posting questions...

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


    viernes, 18 de mayo de 2018 13:50
  • Lo siento, se me paso, es un varchar 30.

    Con respecto a tu indice tengo una duda. No lo use pues el dba me ha dicho que esta tabla siempre tiene que estar actualizada a estadística (cada hora) y me dijo que si creo un indice solo tiene que tener uno o dos campos en include y uno como llave, ya que si es muy grande el indice sera muy pesado para actualizar.

    Por eso use la llave que me recomendaste y en include puse las restricciones mas comunes en los procedimientos y lo filtre.

    Dime si eso es correcto ya que no se mucho de actualización y demás.

    Gracias!!!

     

    viernes, 18 de mayo de 2018 14:02
  • Lo siento, se me paso, es un varchar 30.

    Lo primero que deben hacer es usar el tipo apropiado.  Si vas a comparar el valor de esa columna contra un rango de entero entonces convierte esta a entero.  He ahi donde se esta consumiendo el tiempo.

    En cuanto al comentario del dba, lo dicho no es razon suficiente como para no crear dicho indice.

    Cuantas filas tiene esa tabla en este momento?

    Por que se le debe actualizar las estadisticas cada hora?

    Por que no tiene clave primaria?

    Por que no se le busca un indice agrupado adecuado?

    Esa son las cosas que debe buscar el dba.


    AMB

    Some guidelines for posting questions...

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

    viernes, 18 de mayo de 2018 14:29
  • En efecto, lo he pensado, yo se muy poco pero si el no las busca seria la oportunidad de poder buscarlas yo.

    Ya que acá las cosas no están muy bien quisiera poder mejorar, por eso estoy en este foro y también agradezco tu ayuda.

    Sabes hice algo muy tonto antes que me respondas pero ha funcionado mas que bien, las consultas con buffer borrado las saca muy rapido.

    Basicamente, cambie todos los " [edad_reg] between ( x and y)  en [edad_reg] in ('a','b','c'.....).

    Se lleno mi query pero es muy optimo para sql, en serio hunchback muchas gracias y de ahora en adelante creo que estaré muy activo por aquí, espero puedas seguir ayudándome.

    Saludos!!

    viernes, 18 de mayo de 2018 14:52
  • Mil disculpas, con la emocion olvide responderte jajaja

     >Si vas a comparar el valor de esa columna contra un rango de entero entonces convierte esta a entero

    Convertirlo a entero es mas optimo que poner todas las variables como ya lo hice?

    >En cuanto al comentario del dba, lo dicho no es razon suficiente como para no crear dicho indice.

    Es que supervisa todo lo que hago, quiero optimizar al maximo todo antes de crear indice (fue su recomendacion)

    >Cuantas filas tiene esa tabla en este momento?

    Hasta la fecha son 73 millones 

    >Por que se le debe actualizar las estadisticas cada hora?

    Trabajamos con un instituto de estadistica que es parte del gobierno (trabajo para el gobierno) y tienen que mantenero actualizado cada hora.

    >Por que no tiene clave primaria?

    Se lo pregunte y me dijo que es por que es dwh, solo afirme ya que como te comente soy nuevo en esto.

    >Por que no se le busca un indice agrupado adecuado?

    No lo se pero me gustaría hacerlo, solo hace pequeños indices.

    viernes, 18 de mayo de 2018 15:04