none
Problemas de tiempo(ejecución) en procedimiento almacenado SQL Server RRS feed

  • Pregunta

  • Buen día, tengo la siguiente consulta con un procedimiento almacenado:

    al cual le paso 3 paramentos @fecha , @estatus y @buscar , por lo general solo le mando la fecha (012017,052017) para obtener la información de esos meses, utilizo 3 tablas para mostrar la info de (200, 90,000 Y 1,000 registros) y por lo regular el máximo de filas que muestra como resultado son 190 el problema es que mi procedimiento tarda mucho, quisiera alguna sugerencia para agilizar lo, ya que lo cargo en un GridView dentro de un Formulario web y este se vuelve aun mas tardado, dejo el código completo de mi procedimiento espero contar con sus sugerencias, gracias. 

    USE [Reporte]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Reporte_mes]
    
    (
    @Fechas as varchar (max),
    @Estatus as varchar(max) = null,
    @Buscar as Varchar (max) = null
    )
    WITH RECOMPILE
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    	declare @total int = 0
    	 
    	SELECT  a.sFolio, C.sNombre, C.sClave, C.sEstatus,  sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)) as NPInicio, 
    	(count(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end) + COUNT(case when a.sRegimen like'B%' then 1 else null end)))/COUNT(DISTINCT B.nMes) as EEE,
    	
    	(COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) as NPEET, 
    	 COUNT(*)/COUNT(DISTINCT B.nMes) as NPEEECONRYT,
    	(sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)))+ COUNT(*)/COUNT(DISTINCT B.nMes)-
    	 COUNT(case when b.nMes=convert(varchar, month(a.dfechapago))and b.nAnio=convert(varchar, year(a.dfechapago)) then 1 else null end)
    	 - (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) AS FACTURA
    	 from Reporte.dbo.RegEEEP a
    	 JOIN Reporte.dbo.RegEduc b ON a.sFolio= b.sFolio AND  convert(varchar, month(dfechapago))+convert(varchar, year(dfechapago))IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ',')) and (convert(varchar, nMes)+convert(varchar,nAnio) IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ',')) )
    	 LEFT JOIN Reporte.dbo.Clientes c ON A.sFolio = c.sFolio 
    	 where  ((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null) or (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    	 group by  a.sFolio, c.sNombre, c.sEstatus, c.sClave
    
     	UNION ALL
    	select  b.sFolio, C.sNombre, C.sClave, C.sEstatus, sum(b.aNPInicio), 
    	@total as EEE, @total as NPEET, @total as NPEEECONRYT, sum(b.aNPInicio) as FACTURA from Reporte.dbo.RegEEEP a
    	right JOIN Reporte.dbo.RegEduc b ON a.sFolio= b.sFolio and convert(varchar, month(dfechapago))+convert(varchar, year(dfechapago))IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ','))
    	left JOIN Reporte.DBO.Clientes C ON b.sFolio = c.sFolio 
    	where a.sFolio is null and (convert(varchar, nMes)+convert(varchar,nAnio) IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ',')) ) and ((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null) or (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    	group by  b.sFolio, c.sNombre, C.sClave, C.sEstatus
    	
    	UNION ALL 
    	select  a.sFolio, C.sNombre, C.sClave, C.sEstatus, @total, 
    	count(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end)) as EEE,
    	COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end) as NPEET,
    	COUNT(*) as NPEEECONRYT,
    	COUNT(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end)) as FACTURA
    	from Reporte.dbo.RegEEEP a
    	left JOIN Reporte.dbo.RegEduc b ON a.sFolio= b.sFolio and (convert(varchar, nMes)+convert(varchar,nAnio) IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ',')))
    	left JOIN Reporte.dbo.Clientes C ON a.sFolio = c.sFolio 
    	where convert(varchar, month(dfechapago))+convert(varchar, year(dfechapago))IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ','))  and b.sFolio is null
    	and ((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null) or (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    	group by a.sFolio, c.sNombre, C.sClave, C.sEstatus
    
    	
    	UNION ALL 
    	select C.sFolio, C.sNombre, C.sClave, C.sEstatus, @total, @total as EEE, @total as NPEET,
    	@total as ASNPEEECONRYT, @total as FACTURA from Reporte.dbo.RegEduc b
    	RIGHT JOIN Reporte.dbo.Clientes C ON b.sFolio = c.sFolio  and (convert(varchar, nMes)+convert(varchar,nAnio) IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ','))) 
    	left JOIN Reporte.dbo.RegEEEP a ON a.sFolio = c.sFolio and  convert(varchar, month(dfechapago))+convert(varchar, year(dfechapago))IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ','))
    	where b.sFolio is null and a.sFolio is null and ((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null) or (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    	group by a.sFolio, C.sFolio, c.sNombre, C.sClave, C.sEstatus
    	
    	order by c.sNombre asc
    
    END



    viernes, 18 de agosto de 2017 15:14

Respuestas

  • Pudieras crear una tabla temporal o variable tipo tabla donde guardar el mes y año de cada elemento de la lista asi como su fecha de inicio y final del mes.

    declare @T table (
    anio smallint not null,
    mes tinyint not null,
    bofm date not null,
    eofm date not null,
    primary key (anio, mes),
    unique (bofm, eofm),
    unique (eofm, bofm)
    );

    insert into @T (anio, mes, bofm, eofm)
    select
        right(Item, 4) as anio,
        left(Item, 2) as mes,
        cast(right(Item, 4) + left(Item, 2) + '01' as date) as bofm,
        dateadd(month, datediff(month, '18991231', cast(right(Item, 4) + left(Item, 2) + '01' as date)), '18991231') as eofm
    from
        dbo.SplitString(@Fechas, ',') as T;

    Ahora puedes usar la variable tipo tabla en tu query sin tener que desmembrar la cadena una y otra vez, como por ejemplo:

    SELECT
    	a.sFolio, c.sNombre, c.sClave, c.sEstatus,  
    	SUM(b.aNPInicio)/ (COUNT(a.sFolio)/COUNT(DISTINCT b.nMes)) AS NPInicio, 
    	(COUNT(*) - (COUNT(CASE WHEN a.sRegimen  LIKE'B1%' THEN 1 ELSE NULL END) + COUNT(CASE WHEN a.sRegimen LIKE'B%' THEN 1 ELSE NULL END))) / COUNT(DISTINCT b.nMes) AS EEE,
    	(COUNT(CASE WHEN a.sRegimen  LIKE'B1%' THEN 1 ELSE NULL END)+ COUNT(CASE WHEN a.sRegimen LIKE'B%' THEN 1 ELSE NULL END))/COUNT(DISTINCT b.nMes) AS NPEET, 
    	COUNT(*)/COUNT(DISTINCT b.nMes) AS NPEEECONRYT,
    	(SUM(b.aNPInicio)/ (COUNT(a.sFolio)/COUNT(DISTINCT b.nMes)))+ COUNT(*)/COUNT(DISTINCT b.nMes)-
    	 COUNT(CASE WHEN b.nMes=CONVERT(varchar, MONTH(a.dfechapago))AND b.nAnio=CONVERT(varchar, YEAR(a.dfechapago)) THEN 1 ELSE NULL END)
    	 - (COUNT(CASE WHEN a.sRegimen  LIKE'B1%' THEN 1 ELSE NULL END)+ COUNT(CASE WHEN a.sRegimen LIKE'B%' THEN 1 ELSE NULL END))/COUNT(DISTINCT b.nMes) AS FACTURA
    FROM
    	Reporte.dbo.RegEEEP a
    	JOIN 
    	Reporte.dbo.RegEduc b 
    	ON a.sFolio = b.sFolio 
    	AND EXISTS (SELECT * FROM @T AS T WHERE CAST(a.dfechapago AS date) BETWEEN T.bofm AND T.eofm) 
    	AND EXISTS (SELECT * FROM @T AS T WHERE T.anio = b.nAnio AND T.mes = b.nMes)
    	LEFT JOIN 
    	Reporte.dbo.Clientes c 
    	ON a.sFolio = c.sFolio 
    WHERE
    	((@Estatus IS NULL) OR (c.sEstatus = @Estatus)) AND ((@Buscar IS NULL) OR (c.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    GROUP BY
    	a.sFolio, c.sNombre, c.sEstatus, c.sClave;


    AMB

    Some guidelines for posting questions...

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


    • Editado HunchbackMVP lunes, 21 de agosto de 2017 19:53
    • Marcado como respuesta D. Velazquez lunes, 21 de agosto de 2017 20:34
    lunes, 21 de agosto de 2017 19:52

Todas las respuestas

  • Hola que tal, te recomiendo que corras cada fragmento de codigo y ejecutes el plan de ejecucion, para poder ver como poder mejorarlo, nos comentas por favor.

    Saludos,

    Jorge Muchaypiña G.


    Business Intelligence Analyst

    viernes, 18 de agosto de 2017 15:28
  • Hola,

    Podrias mostrarme la arquitectura de las tablas, para saber observar los campos

    Lo mas recomendable, es quitar los "UNION ALL" y colocar Inner Join ( para ello debe de estar referenciado por una ID ), por dicho motivo nececito ver la arquitectura de las tablas

    Saludos

    viernes, 18 de agosto de 2017 16:29
  • D. Velazquez,

    Sin tener mas informacion sobre las tablas, incluyendo restricciones, indices, etc. lo primero que mencionaria es que en los queries estas usando redicados de union y/o filtros que no se consideran adecuados para sacar el mejor rendimiento.  Entre ellos tenemos manipular columnas (aplicar funciones sobre ellas) que participan en una union o filtro.

    ...
    FROM
        Reporte.dbo.RegEEEP AS a
        JOIN
        Reporte.dbo.RegEduc AS b
        ON a.sFolio = b.sFolio
           AND  CONVERT(varchar, MONTH(dfechapago)) + CONVERT(varchar, YEAR(dfechapago))IN (
                SELECT  CAST(Item AS integer)
                FROM
                    dbo.SplitString(@Fechas, ',')
                )
           AND  (CONVERT(varchar, nMes) + CONVERT(varchar, nAnio)IN (
                 SELECT CAST(Item AS integer)
                 FROM
                        dbo.SplitString(@Fechas, ',')
                 )
           )
    ...

    Lo primero es que no tenemos idea alguna de cual tabla proviene la columna [dfechapago], por lo que te recomiendo que si declaras alias de tablas que las uses para delimitar las columnas sobre todo si multiples tablas participan en el query.

    En cuanto a no manipular las columnas en una union (JOIN) o en el filtro, recuerda que existe algun indice por esa columna entonces al aplicar la funcion este indice queda desqualificado para usar sus estadisticas sobre la distribucion de valores en esta columna.  En este caso pudieras adicionar una columna que contenga dicho valor y crear indice que soporte esta busqueda.

    Otra cosa que debe estar afectando es cuando usas comparaciones donde los comodines (wildcards) se usan como prefijos:

    AND (
        (@Buscar IS NULL)
        OR  (
        c.sFolio LIKE'%' + @Buscar + '%'
        OR  c.sNombre LIKE '%' + @Buscar + '%'
        )
        )

    Cuando se usan de esta forma tambien tienden a causar el mismo efecto que el anterior explicado.

    Por ultimo, nos pudieras decir que tipo de funcion es dbo.SplitString.  Es esta tipo tabla en linea o es multi-sentencias?


    AMB

    Some guidelines for posting questions...

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

    viernes, 18 de agosto de 2017 17:54
  • Gracias por responder, estuve haciendo algunas consultas por bloques a mi código y noté que lo que más toma tiempo es con los JOIN Y SplitString, la verdad soy nueva en esto y no se en este caso cual seria lo mejor para mi consulta sin tanta demora, agradezco sus comentarios.

    nota: dbo.SplitString es multi sentencia ya que en ocaciones requiero solo info de (012017,052017) es decir abril y mayo

     (convert(varchar, nMes)+convert(varchar,nAnio) IN  (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ',')))

    lunes, 21 de agosto de 2017 18:05
  • Pudieras crear una tabla temporal o variable tipo tabla donde guardar el mes y año de cada elemento de la lista asi como su fecha de inicio y final del mes.

    declare @T table (
    anio smallint not null,
    mes tinyint not null,
    bofm date not null,
    eofm date not null,
    primary key (anio, mes),
    unique (bofm, eofm),
    unique (eofm, bofm)
    );

    insert into @T (anio, mes, bofm, eofm)
    select
        right(Item, 4) as anio,
        left(Item, 2) as mes,
        cast(right(Item, 4) + left(Item, 2) + '01' as date) as bofm,
        dateadd(month, datediff(month, '18991231', cast(right(Item, 4) + left(Item, 2) + '01' as date)), '18991231') as eofm
    from
        dbo.SplitString(@Fechas, ',') as T;

    Ahora puedes usar la variable tipo tabla en tu query sin tener que desmembrar la cadena una y otra vez, como por ejemplo:

    SELECT
    	a.sFolio, c.sNombre, c.sClave, c.sEstatus,  
    	SUM(b.aNPInicio)/ (COUNT(a.sFolio)/COUNT(DISTINCT b.nMes)) AS NPInicio, 
    	(COUNT(*) - (COUNT(CASE WHEN a.sRegimen  LIKE'B1%' THEN 1 ELSE NULL END) + COUNT(CASE WHEN a.sRegimen LIKE'B%' THEN 1 ELSE NULL END))) / COUNT(DISTINCT b.nMes) AS EEE,
    	(COUNT(CASE WHEN a.sRegimen  LIKE'B1%' THEN 1 ELSE NULL END)+ COUNT(CASE WHEN a.sRegimen LIKE'B%' THEN 1 ELSE NULL END))/COUNT(DISTINCT b.nMes) AS NPEET, 
    	COUNT(*)/COUNT(DISTINCT b.nMes) AS NPEEECONRYT,
    	(SUM(b.aNPInicio)/ (COUNT(a.sFolio)/COUNT(DISTINCT b.nMes)))+ COUNT(*)/COUNT(DISTINCT b.nMes)-
    	 COUNT(CASE WHEN b.nMes=CONVERT(varchar, MONTH(a.dfechapago))AND b.nAnio=CONVERT(varchar, YEAR(a.dfechapago)) THEN 1 ELSE NULL END)
    	 - (COUNT(CASE WHEN a.sRegimen  LIKE'B1%' THEN 1 ELSE NULL END)+ COUNT(CASE WHEN a.sRegimen LIKE'B%' THEN 1 ELSE NULL END))/COUNT(DISTINCT b.nMes) AS FACTURA
    FROM
    	Reporte.dbo.RegEEEP a
    	JOIN 
    	Reporte.dbo.RegEduc b 
    	ON a.sFolio = b.sFolio 
    	AND EXISTS (SELECT * FROM @T AS T WHERE CAST(a.dfechapago AS date) BETWEEN T.bofm AND T.eofm) 
    	AND EXISTS (SELECT * FROM @T AS T WHERE T.anio = b.nAnio AND T.mes = b.nMes)
    	LEFT JOIN 
    	Reporte.dbo.Clientes c 
    	ON a.sFolio = c.sFolio 
    WHERE
    	((@Estatus IS NULL) OR (c.sEstatus = @Estatus)) AND ((@Buscar IS NULL) OR (c.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    GROUP BY
    	a.sFolio, c.sNombre, c.sEstatus, c.sClave;


    AMB

    Some guidelines for posting questions...

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


    • Editado HunchbackMVP lunes, 21 de agosto de 2017 19:53
    • Marcado como respuesta D. Velazquez lunes, 21 de agosto de 2017 20:34
    lunes, 21 de agosto de 2017 19:52