Timeout expired SqlException for complex Procedure
4
votes
2
answers
2671
views
I use SQL Server 2008 R2 and ASP.NET 4.5.
Sometimes, I get this error when I executed a complex procedure:
> System.Data.SqlClient.SqlException
>
> Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
Any suggestions for troubleshooting or checklist (steps for improvements SQL)?
My .NET code:
return Translate(SqlDbHelper.ExecuteProcedure("SP_CalendarQuery", pfechaAlerta, pfechaAlertaFin, pAsunto, pidArea, pidTipoAlerta, pTomador, pidUsuarioConsulta, pCartera));
public static DataTable ExecuteProcedure(string sql, params SqlParameter[] listParams)
{
var dt = new DataTable();
using (var conn = new SqlConnection(ConnectionString))
{
using (var command = new SqlCommand(sql, conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(listParams);
using (var dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(dt);
}
}
}
return dt;
}
SQL Server Procedure:
ALTER PROCEDURE [dbo].[SP_CalendarQuery]
@DateStart datetime = NULL,
@DateEnd datetime = NULL,
@Subject varchar(1000) = NULL,
@Area int = NULL,
@TypeAlert int = NULL,
@Tomador varchar(50) = NULL,
@UserQuery int = NULL,
@CarteraUserQueQuery int = null
AS
BEGIN
DECLARE @intTypeRol INT
DECLARE @intTypeMediador INT
DECLARE @intTypeAsociacion INT
IF @UserQuery IS NOT NULL
begin
SELECT
@intTypeRol = FK_ID_ROL_PORTAL,
@intTypeMediador = FK_ID_Type_MEDIADOR,
@intTypeAsociacion = ASOCIACION
FROM [AccessRoles.Users]
WHERE FK_ID_DATOS_PERSONALES = @UserQuery
END
ELSE
BEGIN
SET @intTypeRol = NULL
SET @intTypeMediador = NULL
SET @intTypeAsociacion = NULL
END
SELECT C.[ID_Alert]
,C.[Empresa]
,C.[Subject]
,C.[Date_Start]
,C.[Date_End]
,C.[Detalle]
,C.[Numero_Referencia]
,C.[Vinculo]
,C.[FK_ID_UserPortal_Alta]
,C.[FK_ID_Origen_Datos]
,O.NOMBREORIGENDATOS
,C.[FK_ID_Area]
,A.DESCRIPCION_AREA_ACTUACION
,C.[FK_ID_Type_Alert]
,TA.NOMBRETypeAlert
,C.[FK_ID_Type_Referencia]
,TR.NOMBRETypeREFERENCIA
,C.[Ramo]
,C.[NombreTomador]
,C.[ApellidosTomador]
,C.[Date1]
,C.[Date2]
,'' as NombreUserAlta
,(SELECT COUNT(*) FROM [Calendar.Alerts_Leidas]
WHERE FK_ID_Alert = C.ID_Alert AND CARTERA = ISNULL(@CarteraUserQueQuery,0)) AS LeidaUser
,(SELECT COUNT(ID_Adjunto) FROM [Calendar.Adjuntos] WHERE FK_ID_Alert = C.ID_Alert) AS NumeroAdjuntos
,(SELECT User FROM [Calendar.Alerts_Leidas]
LEFT OUTER JOIN [AccessRoles.Datos_Personales] ON ID_DATOS_PERSONALES = FK_ID_UserPORTAL
WHERE FK_ID_Alert = C.ID_Alert AND CARTERA = ISNULL(@CarteraUserQueQuery,0)) AS LeidaPor
,(SELECT Date_LECTURA FROM [Calendar.Alerts_Leidas]
LEFT OUTER JOIN [AccessRoles.Datos_Personales] ON ID_DATOS_PERSONALES = FK_ID_UserPORTAL
WHERE FK_ID_Alert = C.ID_Alert AND CARTERA = ISNULL(@CarteraUserQueQuery,0)) AS DateLectura
,(case ISNULL(@CarteraUserQueQuery,'')
when '' then
ISNULL(STUFF(
(SELECT CAST(',' AS varchar(MAX)) + CARTERA
FROM [Calendar.AlertsCarteras]
WHERE FK_ID_Alert = ID_Alert
FOR XML PATH('')
),
1, 1, ''), 'Todos')
else convert(varchar, @CarteraUserQueQuery) end) as MEDIADORES_Alert
,STUFF(
(SELECT CAST(',' AS varchar(MAX)) + Nombre
FROM [Calendar.Adjuntos]
WHERE FK_ID_Alert = ID_Alert
FOR XML PATH('')
),
1, 1, '') As NombreAdjuntos
,C.Texto
,C.Texto2
,C.SmsCliente
,C.InfoPoliza
,C.InfoMatricula
,C.InfoRecibo
,C.InfoSiniestro
,C.InfoNumeroLiquidacion
,C.Tomador
FROM [dbo].[Calendar.Alerts] C
LEFT OUTER JOIN [Calendar.Origen_Datos] O ON O.ID_ORIGEN_DATOS = C.FK_ID_Origen_Datos
LEFT OUTER JOIN [AccessRoles.Area_Actuacion] A ON A.ID_AREA_ACTUACION = C.[FK_ID_Area]
LEFT OUTER JOIN [Calendar.Type_Alert] TA ON TA.ID_Type_Alert = C.[FK_ID_Type_Alert]
LEFT OUTER JOIN [Calendar.Type_Referencia] TR ON TR.ID_Type_REFERENCIA = C.[FK_ID_Type_Referencia]
where
--(@DateStart is null or convert(varchar,C.[Date_Start], 103) = convert(varchar,@DateStart,103))
(@DateStart IS NULL OR(C.[Date_End] IS NULL AND CONVERT(DATETIME, C.[Date_Start], 103) >= CONVERT(DATETIME, @DateStart, 103))
OR (C.[Date_End] IS NOT NULL AND CONVERT(DATETIME, @DateStart, 103) BETWEEN CONVERT(DATETIME, C.[Date_Start], 103) AND CONVERT(DATETIME, C.[Date_End], 103))
)
AND (
@DateEnd IS NULL OR
(
(C.[Date_End] IS NULL AND CONVERT(DATETIME,CONVERT(VARCHAR, C.[Date_Start],103),103) 0)
--las Alerts no contienen la restricción de rol portal o contienen su rol portal
AND (@intTypeRol IS NULL OR
((SELECT COUNT(*) FROM [Calendar.AlertsRoles] ROL WHERE ROL.FK_ID_Alert = C.[ID_Alert] AND ROL.FK_TypeROLPORTAL IS NOT NULL) = 0
OR (SELECT COUNT(*) FROM [Calendar.AlertsRoles] ROL WHERE ROL.FK_ID_Alert = C.[ID_Alert] AND ROL.FK_TypeROLPORTAL = @intTypeRol) > 0))
----las Alerts no contienen la restricción de Type mediador o contienen su Type mediador
AND (@intTypeMediador IS NULL OR
((SELECT COUNT(*) FROM [Calendar.AlertsRoles] ROL WHERE ROL.FK_ID_Alert = C.[ID_Alert] AND ROL.FK_TypeMEDIADOR IS NOT NULL) = 0
OR (SELECT COUNT(*) FROM [Calendar.AlertsRoles] ROL WHERE ROL.FK_ID_Alert = C.[ID_Alert] AND ROL.FK_TypeMEDIADOR = @intTypeMediador) > 0
)
)
--------las Alerts no contienen la restricción de Type asociacion o contienen su Type asociacion
AND ((@intTypeAsociacion IS NULL OR
(SELECT COUNT(*) FROM [Calendar.AlertsRoles] ROL WHERE ROL.FK_ID_Alert = C.[ID_Alert] AND ROL.FK_TypeASOCIACION IS NOT NULL) = 0
OR (SELECT COUNT(*) FROM [Calendar.AlertsRoles] ROL WHERE ROL.FK_ID_Alert = C.[ID_Alert] AND ROL.FK_TypeASOCIACION = @intTypeAsociacion) > 0))
)
)
ORDER BY C.[Date_Start] DESC
END
Asked by Kiquenet
(155 rep)
Dec 3, 2015, 10:32 AM
Last activity: Apr 15, 2021, 12:31 PM
Last activity: Apr 15, 2021, 12:31 PM