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

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
- Editado D. Velazquez viernes, 18 de agosto de 2017 15:15
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
Todas las respuestas
-
-
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
-
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 -
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, ',')))
- Editado D. Velazquez lunes, 21 de agosto de 2017 18:11
-
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