El siempre elusivo Query Timeout.
 

Estimados delphineros, buen día/tarde/noche, según aplique.

Les planteo mi problema:

Tengo éste código SQL que ejecuto dentro de mi aplicación.

Código SQL [-]
SELECT DISTINCT E2_CTRL_PAC
FROM   T4_CON_CITA
WHERE  T4_FECHA_CITA > 40537
AND    T4_FECHA_CITA < 40903
AND    T4_ASISTIO = 1
AND    T4_FALTO = 0
AND    C21_DX_CIE > -1
AND    NOT C22_DX_CIE IS NULL
AND    E2_CTRL_PAC NOT IN (SELECT DISTINCT E2_CTRL_PAC
FROM   T6_ESPECIALES
WHERE  T6_FECHA_CITA > 40537
AND    T6_FECHA_CITA < 40903
AND    T6_ASISTIO = 1
AND    T6_FALTO = 0
AND    C21_DX_CIE > -1
AND    NOT C22_DX_CIE IS NULL)
UNION ALL(
SELECT DISTINCT E2_CTRL_PAC
FROM   T6_ESPECIALES
WHERE  T6_FECHA_CITA > 40537
AND    T6_FECHA_CITA < 40903
AND    T6_ASISTIO = 1
AND    T6_FALTO = 0
AND    C21_DX_CIE > -1
AND    NOT C22_DX_CIE IS NULL
AND    E2_CTRL_PAC NOT IN (SELECT DISTINCT E2_CTRL_PAC
FROM   T4_CON_CITA
WHERE  T4_FECHA_CITA > 40537
AND    T4_FECHA_CITA < 40903
AND    T4_ASISTIO = 1
AND    T4_FALTO = 0
AND    C21_DX_CIE > -1
AND    NOT C22_DX_CIE IS NULL)
)
ORDER BY E2_CTRL_PAC

Para mayor abundamiento, tengo éste parámetro definido en "hard-code" dentro de mi aplicación:

Código Delphi [-]
      qryPacientes.CommandTimeout  := 360;
      qrySSAuxiliar.CommandTimeout := 360;
      qrySISRep.CommandTimeout     := 360;
      qryActualizar.CommandTimeout := 360;
      qrySumatoria.CommandTimeout  := 360;

Ahora, el problema es que sucede 1 de cada 3 veces un "cuelgue" del TADOQuery que contiene el SQL anterior, el cual en Microsoft SQL Server Management Studio tarda NO MÁS DE 1 MINUTO en arrojar los 80 y algo mil registros requeridos, y ésto después de ejecutar por casi 3 minutos.

¿Alguna idea, corrección, locura o maldición que me permita continuar mi desarrollo? :p

me parece que puedes optimizar un poco las consultas
no se si es mas rapido usar para la comparacion de fechas between que el > y <

tambien podrias hacer un left join con la tabla T6_especiales y capturar solo aquellos que tb_especiales.E2_ctrl_pac = null
lo mismo con la segunda consulta

Esto es por convención dentro de la empresa. Como el código también lo hace y revisa mi patrón, detesta el uso de BETWEEN...

Buena jornada,

Un par de ideas vagas:

1. En la respuesta de ADO components CommandTimeout recomiendan poner el valor de la propiedad CursorLocation a clUseServer.

2. Si la relación entre las dos tablas se da únicamente por el campo E2_CTRL_PAC (aunque sospecho que no), y si entiendo bien lo que propone [oscarac], sería algo así:

Código SQL [-]
SELECT DISTINCT E2_CTRL_PAC
FROM T4_CON_CITA
LEFT OUTER JOIN T6_ESPECIALES
  ON T4_CON_CITA.E2_CTRL_PAC = T6_ESPECIALES.E2_CTRL_PAC 
WHERE T4_CON_CITA.T4_FECHA_CITA > 40537 
  AND T4_CON_CITA.T4_FECHA_CITA < 40903
  AND T4_CON_CITA.T4_ASISTIO = 1 
  AND T4_CON_CITA.T4_FALTO = 0
  AND T4_CON_CITA.C21_DX_CIE > -1 
  AND NOT T4_CON_CITA.C22_DX_CIE IS NULL
  AND T6_ESPECIALES.E2_CTRL_PAC IS NULL 
UNION  
SELECT DISTINCT E2_CTRL_PAC
FROM T6_ESPECIALES
LEFT OUTER JOIN T4_CON_CITA
  ON T6_ESPECIALES.E2_CTRL_PAC = T4_CON_CITA.E2_CTRL_PAC 
WHERE T6_ESPECIALES.T6_FECHA_CITA > 40537 
  AND T6_ESPECIALES.T6_FECHA_CITA < 40903
  AND T6_ESPECIALES.T6_ASISTIO = 1 
  AND T6_ESPECIALES.T6_FALTO = 0
  AND T6_ESPECIALES.C21_DX_CIE > -1 
  AND NOT T6_ESPECIALES.C22_DX_CIE IS NULL
  AND T4_CON_CITA.E2_CTRL_PAC IS NULL

Pero, adicionalmente, le propongo el uso de FULL OUTER JOIN. Algo así:

Código SQL [-]
SELECT DISTINCT E2_CTRL_PAC
FROM T4_CON_CITA
FULL OUTER JOIN T6_ESPECIALES
  ON T4_CON_CITA.E2_CTRL_PAC = T6_ESPECIALES.E2_CTRL_PAC 
WHERE T4_CON_CITA.T4_FECHA_CITA > 40537 
  AND T4_CON_CITA.T4_FECHA_CITA < 40903
  AND T4_CON_CITA.T4_ASISTIO = 1 
  AND T4_CON_CITA.T4_FALTO = 0
  AND T4_CON_CITA.C21_DX_CIE > -1 
  AND NOT T4_CON_CITA.C22_DX_CIE IS NULL
UNION  
SELECT DISTINCT E2_CTRL_PAC
FROM T6_ESPECIALES
FULL OUTER JOIN T4_CON_CITA
  ON T6_ESPECIALES.E2_CTRL_PAC = T4_CON_CITA.E2_CTRL_PAC 
WHERE T6_ESPECIALES.T6_FECHA_CITA > 40537 
  AND T6_ESPECIALES.T6_FECHA_CITA < 40903
  AND T6_ESPECIALES.T6_ASISTIO = 1 
  AND T6_ESPECIALES.T6_FALTO = 0
  AND T6_ESPECIALES.C21_DX_CIE > -1 
  AND NOT T6_ESPECIALES.C22_DX_CIE IS NULL

Si está de acuerdo, puede comparar el desempeño que le dé la ejecución de cada propuesta.
-

Puedes aumentar el timeout (que no es qryPacientes.CommandTimeout ) desde la conexión.

También revisar el plan de ejecución y ver donde esta el problema de desempeño y ajustar los indices acordemente.

Gracias a todos por sus ideas (muy buenas, en proceso de aplicarlas) para el mejoramiento de las sentencias SQL que tengo..., solamente que mi problema no es ello (o al menos, no creo que esté basado en ésto) sino el comportamiento errático del Query Timeout.

Como anoté primeramente, NO SE PRESENTA DE MANERA CONSTANTE, tanto así que con EL MISMO JUEGO DE DATOS, arroja el error aleatoriamente el 33.3% de las ocasiones en que es ejecutado por la aplicación que estoy desarrollando. Lo que me preocupa es que el mismo código SQL con exactamente los mismos parámetros de ejecución tiene dos o tres comportamientos diferentes, desde ejecutarse rápida y exitosamente, tardarse una "cantidad razonable de tiempo" (poco menos de un minuto) hasta arrojar una excepción, y todo con el mismo código, las mismas condiciones y sin carga en el servidor (prácticamente, yo acaparo el servidor al no estar mi patrón presente).

Una vez terminado el proceso de optimización de las sentencias SQL les comentaré el resultado, pero esencialmente el problema que me ocupa es ésta elusiva excepción durante la ejecución de un TADOQuery.

has revisado los indices de las tablas?

Las condiciones de filtrado que programé se hacen sobre los índices, así que de entrada, descarto plenamente los índices..., pero de todas maneras, los revisé y están más que bien.

Si hay un parte de esa consulta que hace una gran demanda de CPU/RAM/IO entonces puede ser que el motor se esta peleando con otros procesos del sistema por esos recursos...

Si, puede ser...

Revisando con mis compañeros de oficina el uso del servidor, resulta que yo lo acaparo casi al 80%, así que posiblemente por ahí vayan los tiros...