Timeout expired SqlException for complex Procedure

Question

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) <= CONVERT(DATETIME, @DateEnd, 103)) 
                OR 
                (
                    --AND (@DateEnd IS NULL OR C.[Date_End] IS NULL OR (
                    C.[Date_End] IS NOT NULL AND CONVERT(DATETIME, @DateEnd, 103) <= CONVERT(DATETIME, C.[Date_End], 103)
                )
            )
        )
    AND (@Area is null or C.[FK_ID_Area] = @Area)
    AND (@TypeAlert is null or C.[FK_ID_Type_Alert] = @TypeAlert)
    AND (@Subject is null or C.Subject LIKE '%' + @Subject + '%')
    AND (@Tomador is null or LTRIM(RTRIM((ISNULL(C.NOMBRETOMADOR,'') + ' ' + ISNULL(C.APELLIDOSTOMADOR,'')))) LIKE '%' + @Tomador + '%')
    AND (@CarteraUserQueQuery IS NULL 
        OR (
            ((SELECT COUNT(*) FROM [Calendar.AlertsCarteras] CA1 WHERE CA1.[FK_ID_Alert] = C.[ID_Alert]) = 0
                OR (SELECT COUNT(*) FROM [Calendar.AlertsCarteras] CA1 WHERE CA1.[FK_ID_Alert] = C.[ID_Alert] AND CA1.[CARTERA] = @CarteraUserQueQuery) > 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

Answer 1

This should be a simple matter of setting the SqlCommand.CommandTimeout property:

command.CommandTimeout = 300; // 5 minutes

This is the number of seconds to wait before throwing an error. The default is 30 seconds.

Answer 2

srutzky's answer is the "symptomatic" solution (i.e. solve timeout problem by making SQLCommand timeout bigger). However, if this code runs synchronously (some user is waiting for these data), I would consider optimizing the procedure.

1) From what I see, you are using a lot of LEFT JOINs and inner queries, so you must ensure you have the right indexes on foreign keys.

2) Using scalar functions for comparisons such as CONVERT(DATETIME, C.[Date_Start], 103) >= CONVERT(DATETIME, @DateStart, 103)) can kill the performance, as it denies indexes usage. It is best to have DateTimes stored as DATETIME2. If it is not possible, an option is to add a persisted computed column (AS CONVERT(DATETIME, C.[Date_Start], 103))

3) Filtering is done using pattern like this:

AND (@Area is null or C.[FK_ID_Area] = @Area)

Here a potential optimization can be done by having the query dynamic and adding filters only when filters are provided. It is particularly effective when users provide values only for some filters:

DECLARE @SQL NVARCHAR(4000)

@SQL = N'
    SELECT ... 
'

IF (@Area IS NOT NULL)
   @SQL = @SQL + N'C.[FK_ID_Area] = @Area'

....

EXEC sp_executesql @SQL, <params here>

4) temporary tables usage

Your select is quite complex and its logic is a good candidate for splitting. You can split it, by fetching some data into a temporary table and then using updates to fill some columns:

create table #tmp 
(
   [ID_Alert] int,
   [Empresa] NVARCHAR(100),
   [Subject] NVARCHAR(200),
   ....
)

-- have the result table as partially filled table
INSERT INTO #tmp
(ID_Alert, Empresa, Subject, ...)
SELECT C.[ID_Alert],C.[Empresa],C.[Subject], <easy to get columns>

-- perform updates for complex parts

UPDATE #tmp
SET MEDIADORES_Alert = (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 a conclusion, it is very recommended to avoid such complex queries for two reasons:

1) performance - it is very hard for the SQL to come with a good plan for very complex queries

2) readability and maintenance - a code that does rather complex queries step by step is easier to read and maintain