none
Como hacer Select pasando los valores de una columna como nombres de columnas? RRS feed

  • Pregunta

  • Hola amigos, nuevamente pidiendoles ayuda

    tengo una tabla:

     

    Tabla Historico

    IDHIST RUT MES AÑO DEPTO

    1          1-1   1    2010   1

    2          5-7   1    2010   2

    3          5-7   1    2010   3

    4          8-1   1    2010   2

     

    Otra Tabla:

     

    Tabla Personal

    RUT NOMBRE EDAD

    1-1  PABLO   18

    5-7  RENE     25  

    8-1  MARIA   17

     

     

    Otra tabla:

     

    Tabla Departamento

    IDDEPTO NOMBRE

    1            INFOR

    2            VENTA

    3            INVENTARIO


     

    Otra tabla:

    Tabla Asignaciones

    IDASIG NOMBRE

    1          SUELDO

    2          HRS_EXTRAS

    3          BONOS

     

    y Otra:

     

    Tabla DetalleAsignaciones

    IDHIST IDASIG MONTO

    1              1      15000

    1              2      25000

    1              3      35000

    2              1      27000

    2              2      45000

    2              3      80000

    3              1      12000

    3              2      87000

    3              3      2000

    4              1      45000

    4              2      19000

    4              3      78000

     


    Y necesito una query q me permita guardar en una tabla temporal solo que se cree cuando se haga la consulta de la siguiente manera, el relacionar los Id no tengo problemas, lo que no se es como detallar el nombre de la tabla "asignaciones" con sus respectivos valores, para que me queden asi los registros?:

     

    Tabla Consulta_Temporal

    RUT NOMBRE EDAD MES AÑO DEPTO SUELDO HRS_EXTRAS BONOS

    1-1  PABLO   18         1    2010 INFOR  15000     25000          35000

    5-7  RENE     25         1    2010 VENTA  27000     45000          80000

    5-7  RENE     25         1    2010 INVEN   12000     87000          2000

    8-1  MARIA   17         1    2010 VENTA   45000     19000         78000

     

    Espero se entienda, estare atento a sus comentarios, muchas gracias

    domingo, 18 de julio de 2010 5:21

Respuestas

  • usa pivot, yo estoy usando sql server 2008 pero esa función esta desde sql 2005

    select rut, nombre, edad, mes, año, nombre, [SUELDO], [HRS_EXTRAS], [BONOS] FROM
    (select p.RUT, p.NOMBRE NOMPERSONAL, p.EDAD, h.MES, h.AÑO, d.NOMBRE, a.NOMBRE ASIG, da.MONTO
     from Personal p inner join Historico h
    	on p.RUT = h.RUT inner join Departamento D
    	on h.DEPTO = d.IDDEPTO inner join DetalleAsignaciones da
    	on h.IDHIST = da.IDHIST inner join Asignaciones a
    	on da.IDASIG = a.IDASIG) p
     pivot
     (sum(MONTO)
     for asig in ([SUELDO],[HRS_EXTRAS],[BONOS])) AS PVT
    
    domingo, 18 de julio de 2010 5:56
  • Hola.

    Entonces no es ese el problema, se deberá a otra razón. En cuanto a la reducción de lo del punto 2, es curioso, la tabla "centro_responsabilidad" debería tener como índice clustered el campo codcenresponsabilidad, pero no parece ser así. Crea un índice tal que así:

    create nonclustered index IX_centro_responsabilidad_A1 on centro_responsabilidad (codcenresponsabilidad) include (nombreresponsabilidad)

    En cualquier caso, parece que hemos llegado al final del camino, al menos en lo que parece ser esta consulta, con los datos existentes y desde la distancia. Haz este último cambio (si así lo estimas), y vemos la respuesta.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    • Marcado como respuesta QuickSeiken martes, 17 de agosto de 2010 7:53
    lunes, 16 de agosto de 2010 18:26
    Moderador

Todas las respuestas

  • usa pivot, yo estoy usando sql server 2008 pero esa función esta desde sql 2005

    select rut, nombre, edad, mes, año, nombre, [SUELDO], [HRS_EXTRAS], [BONOS] FROM
    (select p.RUT, p.NOMBRE NOMPERSONAL, p.EDAD, h.MES, h.AÑO, d.NOMBRE, a.NOMBRE ASIG, da.MONTO
     from Personal p inner join Historico h
    	on p.RUT = h.RUT inner join Departamento D
    	on h.DEPTO = d.IDDEPTO inner join DetalleAsignaciones da
    	on h.IDHIST = da.IDHIST inner join Asignaciones a
    	on da.IDASIG = a.IDASIG) p
     pivot
     (sum(MONTO)
     for asig in ([SUELDO],[HRS_EXTRAS],[BONOS])) AS PVT
    
    domingo, 18 de julio de 2010 5:56
  • Hola estoy probando el codigo, pero me asalta una duda, es una variable lo que pusiste despues del FOR en la ultima linea de codigo "ASIG" ? o es la tabla asignaciones?
    domingo, 18 de julio de 2010 7:12
  • Hola estoy probando el codigo, pero me asalta una duda, es una variable lo que pusiste despues del FOR en la ultima linea de codigo "ASIG" ? o es la tabla asignaciones?

    Me resulto perfecto y ya resolvi el punto citado, pero me surge otra duda necesito mostrar el total de registros, incluyendo los repetidos, sin la sumatoria final, 

    domingo, 18 de julio de 2010 7:21
  • Hola estoy probando el codigo, pero me asalta una duda, es una variable lo que pusiste despues del FOR en la ultima linea de codigo "ASIG" ? o es la tabla asignaciones?

    Me resulto perfecto y ya resolvi el punto citado, pero me surge otra duda necesito mostrar el total de registros, incluyendo los repetidos, sin la sumatoria final, 

    Hola muchachos nuevamente, luego de ejercitar el pivot, ya encontre la solucion, y es que el pivot, generara registros distintos, segun los datos que solicitemos en el select, es por esto que en mi ejemplo, si quiero que cada uno de los registros de la tabla "Historico" se seleccionen por separado, solo agrego el campo ID de dicha tabla, quedandome cada registro independiente sean las mismas personas.

     

    Muchas gracias a Rafael, siempre habia escuchado de PIVOT, pero nunca habia sabido para que servia, me ayudara mucho en mis querys

    Saludos

    • Marcado como respuesta QuickSeiken domingo, 18 de julio de 2010 8:08
    • Desmarcado como respuesta QuickSeiken martes, 10 de agosto de 2010 14:53
    • Marcado como respuesta QuickSeiken martes, 10 de agosto de 2010 14:56
    • Desmarcado como respuesta QuickSeiken jueves, 12 de agosto de 2010 4:02
    domingo, 18 de julio de 2010 8:07
  • que bien q te haya servido, saludos :D
    domingo, 18 de julio de 2010 19:57
  • Hola Nuevamente, el sistema de Pivot trabaja muy bien en mi aplicacion, el problema es que mediante mas registro ingreso a las tablas mas se demora en construir la consulta;

     

    Entonces mi consulta es: Existe alguna tecnica, para que esto lo haga mas rapido, o algun truco que uds. suelan ocupar, de ante mano muchas gracias !!!

    martes, 10 de agosto de 2010 14:57
  • Hola.

    En general, el uso de pivot no es muy eficiente desde el punto de vista del rendimiento, si bien puede ser muy elegante como solución. Prueba a resolver el problema de forma "clásica", es decir, con un "sum (case when ...". Puede que así tengas un rendimiento mayor. Otra cosa a tener en cuenta, limita en la medida en que puedas el número de registros que se devuelvan en la sentencia a pivotar (filtrando por los valores a rotar, por ejemplo). Y luego, bueno, si viéramos el plan de ejecución o la tabla de la que se hacen más lecturas, es posible que podamos darte alguna otra sugerencia, quizá falte un índice o haya que retocar algo de la consulta.

    Pásanos la sentencia tal cual la estás ejecutando, a ver si podemos darte alguna sugerencia (será difícil dar con una solución al problema).


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    martes, 10 de agosto de 2010 15:13
    Moderador
  • Gracias por tu respuesta, mi sentencia es un poco complicada y creo no se entendera bien, pero aqui va a ver si me pueden sugerir algo

    Muchas gracias de antemano:

     

    ALTER procedure [dbo].[SP_ConsultaMasiva18834]

    AS

    Begin

    SELECT Idhist, mes, año, nombreproceso, Rut, Dv, nombre, fecnacimiento, sexo, cantcargafamiliar, cantcargafamiliarduplo, 

    tramocargafamiliar, cantbieniotrienio, tipocalidad, nombreplanta, cantidad, nombrecentrocosto, nombrecargo, nombreresponsabilidad, 

    nombreunidad, nombreley, contrcorto, correlativo, afectoturno, derechobonifturno, nivelexperienciacalidificada, diastrabajados, 

    [Sueldo Base], [Asignacion de Antiguedad], [Ley 18717 Art 4], [Asignacion Profesional], [Decreto Ley 3501], [Bonificacion Ley 19429 Art 21], 

    [Bonificacion Ley 19649 Art 27], [Ley 18566 Art 3], [Ley 18675 Art 10], [Ley 18675 Art 11], [Asignacion Perdida de Caja], 

    [Horas Extraordinarias], [Ley 19185], [Ley 15386 Art 19], [Asignacion Familiar], [Asignacion Familiar al Duplo], [Asignacion de Urgencia], 

    [Ley 19699 Art 1], [Ley 19699 Art 11], [Asignaciones Varias], [Funciones Criticas], [Alta Direccion Publica], [Asignacion 3er Turno 70%], 

    [Bonificacion Asignacion de Turno], [Asignacion 4to Turno 38%], [Asignacion Responsabilidad en Gestion], [Ley 20209 Art 4], 

    totalhaberes, aportebtar, ley19404, sis, acctrabajo, totalhaberesconaportes from

    (SELECT historico.Idhist, historico.mes, historico.año, Procesos.nombreproceso, personal.Rut, personal.Dv, personal.nombre, 

    personal.fecnacimiento, personal.sexo, personal.cantcargafamiliar, personal.cantcargafamiliarduplo, personal.tramocargafamiliar, 

    personal.cantbieniotrienio, calidad_juridica.tipocalidad, plantas.nombreplanta, horas_grados.cantidad, centrocosto.nombrecentrocosto, 

    cargos.nombrecargo, centro_responsabilidad.nombreresponsabilidad, unidad.nombreunidad, leyes.nombreley, historico.contrcorto, 

    historico.correlativo, historico.afectoturno, historico.derechobonifturno, historico.nivelexperienciacalidificada, 

    historico.diastrabajados, haberes.Nombre_Para_Muestra, detalle_haberes.monto, historico.totalhaberes, historico.aportebtar, 

    historico.ley19404, historico.sis, historico.acctrabajo, historico.totalhaberesconaportes FROM historico 

    INNER JOIN personal ON historico.rut = personal.Rut 

    INNER JOIN detalle_haberes ON historico.idhist = detalle_haberes.codhistorico 

    INNER JOIN haberes ON detalle_haberes.codhaberes = haberes.codhaberes 

    INNER JOIN calidad_juridica ON historico.codcalidad = calidad_juridica.codcalidad 

    INNER JOIN plantas ON historico.codplanta = plantas.idplanta 

    INNER JOIN Procesos ON historico.codproceso = Procesos.codproceso 

    INNER JOIN horas_grados ON historico.horas_grados = horas_grados.idhrs_grados 

    INNER JOIN centrocosto ON historico.centrocosto = centrocosto.idcentrocosto 

    INNER JOIN cargos ON historico.codcargos = cargos.idcargo 

    INNER JOIN centro_responsabilidad ON historico.codcenresponsabilidad = centro_responsabilidad.codcenresponsabilidad 

    INNER JOIN unidad ON historico.codunidad = unidad.idunidad 

    INNER JOIN leyes ON historico.codley = leyes.codley) historico

    pivot (sum(monto)

    for Nombre_Para_Muestra in ([Sueldo Base], [Asignacion de Antiguedad], [Ley 18717 Art 4], [Asignacion Profesional], [Decreto Ley 3501], 

    [Bonificacion Ley 19429 Art 21], [Bonificacion Ley 19649 Art 27], [Ley 18566 Art 3], [Ley 18675 Art 10], 

    [Ley 18675 Art 11], [Asignacion Perdida de Caja], [Horas Extraordinarias], [Ley 19185], [Ley 15386 Art 19], 

    [Asignacion Familiar], [Asignacion Familiar al Duplo], [Asignacion de Urgencia], [Ley 19699 Art 1], 

    [Ley 19699 Art 11], [Asignaciones Varias], [Funciones Criticas], [Alta Direccion Publica], 

    [Asignacion 3er Turno 70%], [Bonificacion Asignacion de Turno], [Asignacion 4to Turno 38%], 

    [Asignacion Responsabilidad en Gestion], [Ley 20209 Art 4])) 

    as PVT

    where nombreley='LEY 18.834'

    End


    jueves, 12 de agosto de 2010 4:04
  • Hola.

    Efectivamente, demasiado complejo para que un pivot rinda. Como recomendación general, casi siempre es mejor hacer 3 ó 4 consultas sencillas que una grande. Y la que tú expones (si encima es una simplificación) es complicado que rinda, aunque sólo sea por el enorme tamaño de la consulta.

    Vamos a ver si podemos transformar la parte del pivot en una cte con un case, sólo con las tablas afectadas, en sustitución de la tabla "haberes" y así todo lo demás no se ve implicado.

     

    Sería más o menos así:

    ;with Cte_Haberes as (
    select h.codhaberes,
    [Sueldo Base] = isnull(sum(case when Nombre_Para_Muestra = 'Sueldo Base' then D.monto else 0 end), 0,
    [Asignacion de Antiguedad] = isnull(sum(case when Nombre_Para_Muestra = 'Asignacion de Antiguedad' then D.monto else 0 end), 0),
    [Ley 18717 Art 4]=...
    from 
     detalle_haberes D inner join 
     haberes H on D.codhaberes = H.codhaberes
    where nombreley = 'LEY 18.834'
    group by h.codhaberes)
    

     

    Esto lo pones al inicio y en lugar de la tabla "haberes" usas "Cte_Haberes" en tu select:

    ;with Cte_Haberes as (...)

    select ....

    from historico ... inner join detalle_haberes on... inner join Cte_Haberes on detalle_haberes.codhaberes = Cte_Haberes.codhaberes ...

    Tu select ya no tendría que pivotar. Ahora bien, el campo "nombreley" está en la tabla leyes. En tu procedimiento almacenado, carga en una variable el valor de codley, con una consulta simple:

     

    declare @codley int
    
    select @codley = codley from leyes where nombreley='LEY 18.834'
    

     

    Y la relación de este único filtro con la Cte requiere que incluyas la tabla historico en la misma:

     

    ;with Cte_Haberes as (
    select h.codhaberes,
    [Sueldo Base] = isnull(sum(case when Nombre_Para_Muestra = 'Sueldo Base' then D.monto else 0 end), 0,
    [Asignacion de Antiguedad] = isnull(sum(case when Nombre_Para_Muestra = 'Asignacion de Antiguedad' then D.monto else 0 end), 0),
    [Ley 18717 Art 4]=...
    from 
     detalle_haberes D inner join 
     haberes H on D.codhaberes = H.codhaberes inner join
     historico I on H.idhist = I.idhist
    where I.codley = @codley
    group by h.codhaberes)
    

     

    Toma esto siguiente con ciertas reservas, ya que no tengo información suficiente como para asegurarlo con certeza. La consulta de la Cte, muy posiblemente, se vería optimizada con un índice en la tabla en la historico por el campo "codley" (el campo idhist supongo que será clave primaria e irá en el índice clustered). Luego, en la tabla detalle_haberes un índice por los campos idhist, codhaberes y que incluyera el campo monto:

     

    create nonclustered index IX_historico_A1 on historico (codley)
    create nonclustered index IX_detalle_haberes_A1 on detalle_haberes (idhist, codhaberes) include (monto)
    

     

    Sé que son muchas cosas. Mira a ver si consigues que se optimice y si no es así o te surgen dudas, nos dices.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    jueves, 12 de agosto de 2010 7:24
    Moderador
  • Hola, estoy probando poco a poco lo q me señalas, empece con 2 campos solamente de la tabla cod.haberes

    como comentario, no es necesario sumar, yo lo coloque en el pivot por que PIVOT necesita una instruccion para realizar, pero en mi consulta necesito mostrar todos los registros; y aqui lo que estoy probando

     

    ALTER procedure [dbo].[SP_PRUEBA_ConsultaMasiva18834]

    AS

    Begin

    declare @codley int

    select @codley = codley from leyes where nombreley='LEY 18.834'

    ;with 

    Cte_Haberes as (select h.codhaberes,

    [Sueldo Base] = isnull(sum(case when Nombre_Para_Muestra = 'Sueldo Base' then D.monto else 0 end), 0),

    [Asignacion de Antiguedad] = isnull(sum(case when Nombre_Para_Muestra = 'Asignacion de Antiguedad' then D.monto else 0 end), 0)

    from 

     detalle_haberes D inner join 

     haberes H on D.codhaberes = H.codhaberes inner join

     historico I on H.idhist = I.idhist

    where I.codley = @codley

    group by h.codhaberes)

    End

     

    en estas sentencias me arroja este error:

    Msg 156, Level 15, State 1, Procedure SP_PRUEBA_ConsultaMasiva18834, Line 16

    Incorrect syntax near the keyword 'End'.


    jueves, 12 de agosto de 2010 18:09
  • Hola.

    La CTE tienes que consultarla. Para tus pruebas, pon un select * form Cte_Haberes antes del "end".


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    jueves, 12 de agosto de 2010 18:25
    Moderador
  • Hola:

     

     estoy siguiendo paso a paso lo que me indicas, y pido disculpas por mi falta de training, hice lo que me dijiste:

    ALTER procedure [dbo].[SP_PRUEBA_ConsultaMasiva18834]

    AS

    Begin

    declare @codley int

    select @codley = codley from leyes where nombreley='LEY 18.834'

    ;with 

    Cte_Haberes as (select h.codhaberes,

    [Sueldo Base] = isnull(sum(case when Nombre_Para_Muestra = 'Sueldo Base' then D.monto else 0 end), 0),

    [Asignacion de Antiguedad] = isnull(sum(case when Nombre_Para_Muestra = 'Asignacion de Antiguedad' then D.monto else 0 end), 0)

    from 

     detalle_haberes D inner join 

     haberes H on D.codhaberes = H.codhaberes inner join

     historico I on H.idhist = I.idhist

    where I.codley = @codley

    group by h.codhaberes)

    select * from Cte_Haberes

    End

     

    y se me presento el siguiente error:

    Msg 207, Level 16, State 1, Procedure SP_PRUEBA_ConsultaMasiva18834, Line 13

    Invalid column name 'idhist'.

    Msg 207, Level 16, State 1, Procedure SP_PRUEBA_ConsultaMasiva18834, Line 13

    Invalid column name 'idhist'.

    Msg 207, Level 16, State 1, Procedure SP_PRUEBA_ConsultaMasiva18834, Line 8

    Invalid column name 'Nombre_Para_Muestra'.

    Msg 207, Level 16, State 1, Procedure SP_PRUEBA_ConsultaMasiva18834, Line 9

    Invalid column name 'Nombre_Para_Muestra'.


     

    jueves, 12 de agosto de 2010 23:03
  • Hola.

    El script lo hice a mano alzada mirando los nombres de los campos que indicaste en la consulta inicial. Me confundí con el campo detalle_haberes.idhist, que realmente se llama detalle_haberes.codhistorico. El campo haberes.Nombre_Para_Muestra sí parece llamarse así, según esa misma consulta. Pero revísalo, tú conoces mejor que yo cómo se llaman los campos de tus tablas.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 13 de agosto de 2010 7:22
    Moderador
  • Hola, al parecer mi logica ya esta reapareciendo nuevamente

    mira te cuento

    estoy tratando de entender un poco el ejemplo que me has mandado, y logro resolver lo siguiente

    al hacer esto

    select

    h.codhaberes,

    [Sueldo Base] = isnull(sum(case when h.Nombre_Para_Muestra = 'Sueldo Base' then D.monto else 0 end), 0),

    [Asignacion de Antiguedad] = isnull(sum(case when h.Nombre_Para_Muestra = 'Asignacion de Antiguedad' then D.monto else 0 end), 0)

    from 

     detalle_haberes D inner join 

     haberes H on D.codhaberes = H.codhaberes inner join

     historico I on D.codhistorico = I.idhist

    group by h.codhaberes

     

    la consulta que entrega es mas menos lo que ando buscando, sin embargo me me suma todas las cantidades, yo necesito que me arroje los resultados individuales sin sumar, es por este motivo que al quitarle los "SUM" a la consulta o bien quitandole el "GROUP BY" me reclama que debo dejarles esas instrucciones,

     

    por  otro lado tal cual esta la consulta agregandole el "WITH CTA" me desconoce las columnas 

     

    a que se podria deber esto?

    viernes, 13 de agosto de 2010 10:54
  • Hola.

    Si ésta es la consulta que buscas, olvídate de la CTE. Si no necesitas sumar ni agrupar, no lo indiques (quita los sum y el group by). No olvides que no estás indicando el filtro para que saque una única ley.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 13 de agosto de 2010 11:12
    Moderador
  • si claro , por el momento, lo esto haciendo sin filtro para ir viendo la consulta poco a poco, revisare esto primero (lo de sacar  los sum y el group by) y te cuento como me va, mucha gracias por tu tiempo
    viernes, 13 de agosto de 2010 11:20
  • Hola

    ok, algo en limpio saco de todo esto

    al hacer esto

    select h.codhaberes,

    [Sueldo Base] = isnull(case when h.codhaberes = 1 then D.monto else 0 end, 0),

    [Asignacion de Antiguedad] = isnull(case when h.codhaberes = 22 then D.monto else 0 end, 0)

    from 

     detalle_haberes D inner join 

     haberes H on D.codhaberes = H.codhaberes inner join

     historico I on D.codhistorico = I.idhist

     

    me genera la consulta como yo la necesito, pero necesito agruparla por el "codhistorico" de la tabla "detalle_haberes" para que quede algo asi

    codhistorico  Sueldo Base Asignacion de antiguedad

    1                 100000          30000

    2                 150000          17000

     

    Ya que solamente hace esto:

    codhistorico  Sueldo Base Asignacion de antiguedad

    1                 100000          0

    2                 150000          0

    1                 0                   30000

    2                 0                   17000

     

    Como lo podria solucionar?

     

     

    viernes, 13 de agosto de 2010 12:09
  • Hola.

    Agrupa por ese campo y suma los otros campos. Es muy similar a la agrupación anterior, la que iba agrupada por codhaberes. Sustituye ese campo por el de histórico y listo.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 13 de agosto de 2010 19:15
    Moderador
  • Hola realmente me ha servido mucho tu ayuda y paciencia en enseñarme esto de posicionar los datos, te cuento que el tiempo de respuesta de mi consulta con PIVOT es aprox. de 1:50 y sin PIVOT (como lo he hecho ahora ultimo) es aprox 1:20.

    al final mi consulta ha quedado de la siguiente manera (ayudado por el editor de querys de SQL SERVER)

    SELECT  

    Procesos.nombreproceso, historico.mes, historico.año,

    personal.nombre, personal.Rut, personal.Dv, personal.fecnacimiento, personal.sexo, personal.cantcargafamiliar, 

    personal.cantcargafamiliarduplo, personal.tramocargafamiliar, personal.cantbieniotrienio,

    historico.correlativo, historico.contrcorto, historico.horas_grados, historico.centrocosto, historico.afectoturno, 

    historico.derechobonifturno, historico.nivelexperienciacalidificada, 

    leyes.nombreley, calidad_juridica.tipocalidad, plantas.nombreplanta, cargos.nombrecargo, centro_responsabilidad.nombreresponsabilidad, 

    unidad.nombreunidad, historico.diastrabajados,

    ISNULL(SUM(CASE WHEN haberes.codhaberes = 1 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Sueldo Base], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 22 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion de Antiguedad], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 11 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 18717 Art 4], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 24 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion Profesional], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 25 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Decreto Ley 3501], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 26 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Bonificacion Ley 19429 Art 21], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 27 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Bonificacion Ley 19649 Art 27], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 28 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 18566 Art 3], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 13 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 18675 Art 10], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 29 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 18675 Art 11], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 30 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion Perdida de Caja], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 31 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Horas Extraordinarias], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 32 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 19185], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 33 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 15386 Art 19], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 34 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion Familiar], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 35 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion Familiar al Duplo], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 36 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion de Urgencia], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 37 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 19699 Art 1], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 38 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 19699 Art 11], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 39 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignaciones Varias], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 40 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Funciones Criticas], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 41 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Alta Direccion Publica], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 42 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion 3er Turno 70%], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 43 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Bonificacion Asignacion de Turno], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 44 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion 4to Turno 38%], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 45 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Asignacion Responsabilidad en Gestion], 

            ISNULL(SUM(CASE WHEN haberes.codhaberes = 46 THEN detalle_haberes.monto ELSE 0 END), 0) AS [Ley 20209 Art 4], 

    historico.totalhaberes, historico.aportebtar, historico.ley19404, historico.sis, historico.acctrabajo, historico.totalhaberesconaportes 

    FROM haberes INNER JOIN

            detalle_haberes ON haberes.codhaberes = detalle_haberes.codhaberes INNER JOIN

            historico ON detalle_haberes.codhistorico = historico.idhist INNER JOIN

            leyes ON historico.codley = leyes.codley INNER JOIN

            calidad_juridica ON historico.codcalidad = calidad_juridica.codcalidad INNER JOIN

            plantas ON historico.codplanta = plantas.idplanta INNER JOIN

            cargos ON historico.codcargos = cargos.idcargo INNER JOIN

            Procesos ON historico.codproceso = Procesos.codproceso INNER JOIN

            centro_responsabilidad ON historico.codcenresponsabilidad = centro_responsabilidad.codcenresponsabilidad INNER JOIN

            unidad ON historico.codunidad = unidad.idunidad INNER JOIN

            personal ON historico.rut = personal.Rut

    WHERE (leyes.nombreley = 'LEY 18.834')

    GROUP BY historico.idhist, historico.contrcorto, historico.correlativo, historico.horas_grados, historico.centrocosto, historico.afectoturno,

    historico.derechobonifturno, historico.nivelexperienciacalidificada, historico.diastrabajados, historico.totalhaberes, historico.aportebtar, 

            historico.ley19404, historico.sis, historico.acctrabajo, historico.totalhaberesconaportes, leyes.nombreley, calidad_juridica.tipocalidad, 

            plantas.nombreplanta, cargos.nombrecargo, Procesos.nombreproceso, centro_responsabilidad.nombreresponsabilidad, unidad.nombreunidad, 

            personal.Rut, personal.Dv, personal.nombre, personal.fecnacimiento, personal.sexo, personal.cantcargafamiliar, 

    personal.cantcargafamiliarduplo, personal.tramocargafamiliar, personal.cantbieniotrienio, historico.mes, historico.año

    order by personal.nombre

     

    si bien el tiempo de diferencia entre uno y otro no es de consideracion, imagino que se notara mas a medida que las tablas tengan mas datos, y teniendo en cuenta lo compleja de la consulta en si (relacionando varias tablas).

     

    Asi que nuevamente te doy las gracias por tu tiempo, y si tienes algun otro "TIPS" que me puedas indicar, estare atento a tus comentarios

     

     

    sábado, 14 de agosto de 2010 9:18
  • Hola.

    Sigue siendo un rendimiento bastante pobre. Si te parece bien, ejecuta "set statistics io on" y luego lanzas la consulta. Verás que, además del recordset, en la pestaña en la que salen los errores y número de registros afectados, aparecerán una serie de líneas con el número de lecturas realizadas de cada tabla. Pásanoslo, a ver de qué forma es posible mejorar la consulta.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    sábado, 14 de agosto de 2010 9:38
    Moderador
  • Hola, aqui esta :

    (3019 row(s) affected)

    Table 'calidad_juridica'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Procesos'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'centro_responsabilidad'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'cargos'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'haberes'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'unidad'. Scan count 81513, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'plantas'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'personal'. Scan count 0, logical reads 168129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'detalle_haberes'. Scan count 1, logical reads 225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'historico'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'leyes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    sábado, 14 de agosto de 2010 13:32
  • Hola.

    Existen varios problemas aquí, a la luz de estas lecturas. Los cruces se hacen con nested loop cuando hay muchos registros implicados en la tabla de entrada (historico) y el cruce con la tabla "haberes" no es nada bueno. También se devuelven muchísimos registros (más de 3000 para una consulta agrupada). ¿No es posible realizar algún tipo de paginación?

    Adicionalmente, si no lo creaste antes, crea el índice en la tabla detalle_haberes:

    create nonclustered index IX_detalle_haberes_A1 on detalle_haberes (idhist, codhaberes) include (monto)

    Otro detalle, no necesitas la tabla Haberes en la consulta. Quítala, el único campo que se usa ya está en la tabla detalle_haberes (codhaberes).

    Haz estos cambios, ejecuta la consulta y pásanos los tiempos y las lecturas.

     

     


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    sábado, 14 de agosto de 2010 14:17
    Moderador
  • Hola, 

    Una vez creado el indice, hago la misma consulta?, o ahora la consulta seria de otra manera?

    sábado, 14 de agosto de 2010 16:06
  • Hola.

    La misma consulta, tal cual.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    sábado, 14 de agosto de 2010 18:01
    Moderador
  • Hola, 

    Aqui traigo las novedades:

    1.- Cree el indice, pero no se como visualizarlo, se aloja en algun path especifico? (asi como los SP que se alojan en la carpeta SP)

    2.- Creo q el indice si esta funcionando, esto lo comento por que el tiempo de respuesta alcanza aprox. 1:10 

    3.- Intente sacar la tabla haberes de la consulta, pero se me desarma la query por no encontrar los codigos en los case

    4.- aqui te envio lo que me habias pedido la primera vez, a ver si se puede sacar algo en claro

    (3019 row(s) affected)

    Table 'calidad_juridica'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Procesos'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'centro_responsabilidad'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'cargos'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'haberes'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'unidad'. Scan count 81513, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'plantas'. Scan count 0, logical reads 163026, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'personal'. Scan count 0, logical reads 168129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'detalle_haberes'. Scan count 3019, logical reads 6310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'historico'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'leyes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



    Atento a tus comentarios

     

    domingo, 15 de agosto de 2010 5:22
  • Hola.

    Puedes ver los índices dentro de la tabla (navegando el árbol de objetos del management studio). El impacto es poco significativo en cualquier caso. Sobre quitar la tabla haberes, además de quitar la tabla has de sustituir las referencias a haberes.codhaberes por detalle_haberes.codhaberes. Saca la tabla de la consulta y dime qué lecturas obtienes.

    Lo siguiente sería conocer el plan de ejecución, aunque con las lecturas me hago una idea bastante precisa de lo que hay. Sin embargo, viendo el plan de ejecución podríamos saber qué cruce penaliza más y así atacar ese en primer lugar. Para mostrar el plan de ejecución en forma gráfica, pulsa Ctrl + M en Management Studio. Hazlo y dinos qué paso se lleva más porcentaje.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    domingo, 15 de agosto de 2010 13:33
    Moderador
  • Ya quite la tabla haberes de la consulta, el tiempo me ha bajado a 1:00

    y con respecto al plan de ejecucion, esto es lo que mas me de arroja en porcentaje:

     

    1.- Clustered Index Scan historico.pk_historico_1 Cost: 32%

    2.- Hash Match (Inner Join) Cost: 26% [historico.codley=leyes.codley]

    3.- Hash Match (Inner Join) Cost: 12% [historico.codcenresponsabilidad=Expr1114]

     

    domingo, 15 de agosto de 2010 19:47
  • Hola.

    El otro índice que te propuse (en la tabla historico), ¿lo creaste?

    create nonclustered index IX_historico_A1 on historico (codley)

    En caso negativo, créalo y nos pasas datos de plan de ejecución y lecturas.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    lunes, 16 de agosto de 2010 7:34
    Moderador
  • Hola

     

    Cree el indice que me indicaste

    y bueno aqui los resultados

    1.- el tiempo de respuesta es de aprox. 1:00

    2.- aqui las lecturas:

    (3019 row(s) affected)

    Table 'detalle_haberes'. Scan count 3019, logical reads 6310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'personal'. Scan count 0, logical reads 6038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'unidad'. Scan count 3019, logical reads 6038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'cargos'. Scan count 0, logical reads 6038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'centro_responsabilidad'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'plantas'. Scan count 0, logical reads 6038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Procesos'. Scan count 0, logical reads 6038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'calidad_juridica'. Scan count 0, logical reads 6038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'historico'. Scan count 1, logical reads 6046, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'leyes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

    3.- y en el plan de ejecucion tambien han bajado considerablemente, este es el  proceso que mas porcentaje tiene: Hash Match (Inner Join) Cost: 26%, los demas no superan el 7%

    Atento a tus comentarios, muchas gracias

     

    lunes, 16 de agosto de 2010 7:56
  • Hola.

    Aunque habría que ver qué cruce es ese hash join, pero lo que ya no vamos a a bajar mucho más el número de lecturas (que las hemos reducido un montón, fíjate en el post inicial en el que incluías las lecturas originales). Ahora tenemos 2 lecturas por registro de cada tabla y se podría reducir, pero el índice en la tabla historico sería bastante grande. Aunque un minuto sigue siendo mucho tiempo, quizá ya estemos en ese tiempo que es debido a la cantidad de información a obtener y en pintarlo en el cliente.

    Para probarlo, en lugar de lanzar la consulta como la tienes, haz un select into. Para ello, sustituye " FROM " por " into #tmp FROM ". Lánzalo y dinos lo que tarda.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    lunes, 16 de agosto de 2010 9:05
    Moderador
  • Hola

    Con el "into #tmp FROM " el tiempo de respuesta sigue siendo 1:00

     

    y en la ejecucion;

    1.- Table Insert [#tmp] Cost: 13%

    2.- Hash Match (Inner Join) Cost: 23% [historico.codcenresponsabilidad]=[Exp1117]

    3.- Sort Cost: 14%

    lunes, 16 de agosto de 2010 11:49
  • Hola.

    Entonces no es ese el problema, se deberá a otra razón. En cuanto a la reducción de lo del punto 2, es curioso, la tabla "centro_responsabilidad" debería tener como índice clustered el campo codcenresponsabilidad, pero no parece ser así. Crea un índice tal que así:

    create nonclustered index IX_centro_responsabilidad_A1 on centro_responsabilidad (codcenresponsabilidad) include (nombreresponsabilidad)

    En cualquier caso, parece que hemos llegado al final del camino, al menos en lo que parece ser esta consulta, con los datos existentes y desde la distancia. Haz este último cambio (si así lo estimas), y vemos la respuesta.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    • Marcado como respuesta QuickSeiken martes, 17 de agosto de 2010 7:53
    lunes, 16 de agosto de 2010 18:26
    Moderador
  • Hola, cree el indice que me indicaste, sin embargo se me presenta una duda; tu me mencionas "que deberia tener como indice CLUSTERED el campo codcenresponsabilidad" pero en el CREATE INDEX, aparece el NONCLUSTERED, esto esta bien? lo pregunto por que si bien el tiempo de respuesta de la consulta sigue siendo aprox. 1:00, sin embargo lo que te mencione en el punto 2 de la lectura, aparece :

    Hash Match (Inner Join) Cost: 27% [historico.codcenresponsabilidad]=[Exp1117]

    y con respecto al final del camino, pues no me queda mas que agradecer tu tiempo y dedicacion, has sido muy gentil en ayudarme.

     

    Espero y nos veamos en otro Post !!

    Saludos

    martes, 17 de agosto de 2010 8:01
  • Hola, nuevamente se me presenta un problema con esta query que con SQL SERVER 2005 me resulta bien, pero que al llamar el procedimiento almacenado que almacena dicha QUERY desde ASP.NET, me reclama lo siguiente:

     

    There was an error executing the query. Please check the syntax  of the  command and if present, the types and values of the parameters an ensure they are correct

     

    Failed to enable constraints. One or more rows contain values violating non-null, unique or foreign-key constraints.

     

    Alguna idea de lo q puede ser? 

    llamando el procedimiento desde SQL SERVER me arroja la Query sin ningun problema, Pero desde ASP.NET no.

    viernes, 20 de agosto de 2010 20:01
  • Hola.

    Serán permisos. Pero eso es secundario. Revisa el procedimiento no sea que entre todo lo que hay se te haya quedado dentro la creación de alguna constraint o algún índice, alguna creación de tabla que no querías dejar ahí, etc.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 20 de agosto de 2010 20:39
    Moderador