Sql-Server-2008-R2

複雜過程的超時過期 SqlException

  • April 15, 2021

我使用 SQL Server 2008 R2 和 ASP.NET 4.5。

有時，當我執行一個複雜的過程時，我會收到這個錯誤：

System.Data.SqlClient.SqlException

超時已過。操作完成前超時時間已過或伺服器無響應

有關故障排除或清單（改進 SQL 的步驟）的任何建議？

我的 .NET 程式碼：

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 過程：

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

這應該是設置SqlCommand.CommandTimeout屬性的簡單問題：

command.CommandTimeout = 300; // 5 minutes

這是在引發錯誤之前等待的秒數。預設值為 30 秒。

srutzky 的答案是“症狀”解決方案（即通過使 SQLCommand 超時更大來解決超時問題）。但是，如果此程式碼同步執行（某些使用者正在等待這些數據），我會考慮優化該過程。

1）據我所知，您使用了很多 LEFT JOIN 和內部查詢，因此您必須確保在外鍵上有正確的索引。

2)使用標量函式進行比較，例如CONVERT(DATETIME, C.[Date_Start], 103) >= CONVERT(DATETIME, @DateStart, 103))會破壞性能，因為它拒絕使用索引。最好將 DateTimes 儲存為DATETIME2。如果不可能，一個選項是添加一個持久計算列 ( AS CONVERT(DATETIME, C.[Date_Start], 103))

3）過濾是使用這樣的模式完成的：

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

在這裡，可以通過動態查詢並僅在提供過濾器時添加過濾器來完成潛在的優化。當使用者只為某些過濾器提供值時，它特別有效：

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）臨時表的使用

您的選擇非常複雜，其邏輯非常適合拆分。您可以通過將一些數據提取到臨時表中然後使用更新來填充一些列來拆分它：

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)

作為結論，非常建議避免這種複雜的查詢，原因有兩個：

1)性能——SQL 很難為非常複雜的查詢提供一個好的計劃

2)可讀性和可維護性- 逐步執行相當複雜查詢的程式碼更易於閱讀和維護

引用自：https://dba.stackexchange.com/questions/122773

相關問答

Sql-Server

計算標準差時填充空值

  • July 25, 2021
檢視問答
Sql-Server

優化：將變數聲明移動到過程的頂部

  • March 30, 2021
檢視問答
Sql-Server-2008-R2

如何確定儲存過程的輸入輸出值？

  • October 3, 2020
檢視問答
Sql-Server

sys.dm_exec_procedure_stats DMV 內容與 BOL 相矛盾

  • May 7, 2020
檢視問答
Sql-Server-2008-R2

儲存過程不顯示結果

  • May 7, 2020
檢視問答
Sql-Server

如何獲取儲存過程在執行時在該儲存過程中執行的數據庫的名稱？

  • March 10, 2020
檢視問答