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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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, ',')))

Answer 5

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