TABLE SCAN en INSERTED

Buenas, paso a contarles mi problema a ver si pueden darme una mano.

Tengo una aplicación que trabaja con VB y Stored Procedures que tienen gran parte de las reglas de negocio.

Estuve revisando un Stored Procedure pues tengo un problema de performance, donde detecto que el mayor problema se encuentra en uno de los Triggers de una Tabla.

El tema es que el problema se da cuando hago un select de la tabla Inserted asignando los valores de algunos campos a variables. La forma del select es la siguiente

Select @a = campo1
@b = campo2
@3 = campo3
from Inserted

Alguna idea?
Gracias
Juan Manuel Caballero.

Luis:

He revisado exhaustivamente el plan de ejecución y estoy completamente seguro de que el problema está en el trigger.

El mayor costo que tiene este Trigger es el Select de los valores hacia las variables que lo hace de la tabla INSERTED (es un Trigger de UPDATE) y el mismo problema lo tengo de la tabla DELETED.

Estás seguro que puedo llevar esos select al Query Analizer?

Gracias y Saludos.

Luis:

Me parece que acá no estoy explicando bien mi problema.

Las tablas INSERTED Y DELETED solo existen dentro de los triggers ya que corresponden al registro que fue modificado, el DELETED corresponde a la versión anterior a la modificación y el INSERTED corresponde a como quedará el Registro luego de la modificación. (No es que crea que no lo sepas es que estoy haciendo mucho incapié en el problema)

Cuando hago un select de cada una de estas tablas (INSERTED O DELETED) el Query Plan me muestra que este select tiene un costo de 76 unidades mientras que en todo el Stored Procedure cualquier sentencia no alcanza el valor 1.

No entiendo por que me da un costo tan alto el hacer un select sobre el registro que está siendo actualizado.

Muchas Gracias y Saludos.

Luis. este es el código, el mayor problema lo tengo en los select que se hacen a las tablas INSERT y DELETE.

Son los dos primeros Select del Trigger.

Gracias y saludos.

CREATE TRIGGER SAR_TU_REC ON dbo.SAR_Reclamos
FOR UPDATE
AS
IF UPDATE(Rec_Estado) or UPDATE(Rec_ActSiguiente) or UPDATE(Rec_FechaResolucion)
BEGIN
Declare
@Estadochar(1),
@Numero varchar(14),
@Parte varchar(14),
@Obsvarchar(255),
@ActivUsrint,
@CodDesestint,
@Usuarioint,
@NumeroAsovarchar(14),
@FecResodatetime,
@ParteResovarchar(14),
@OldFecResodatetime,
@OldParteResovarchar(14),
@Asociadochar(1),
@ActAnteriorint,
@FecFinIntervdatetime,
@FecBajadatetime,
@RacId Int/* 05/07/00 */
/*
**
*/
Select@Usuario = Usr_IdUsuario
FromSAR_Usuario (nolock)
Wherertrim(Usr_Nombre) = lower(system_user)
/*
** OBTENER LOS VALORES NUEVOS DE LOS CAMPOS MODIFICADOS
*/
BEGIN -- Este SELECT TIENE COSTO 76
Select @Estado = Rec_Estado,
@Numero = Rec_Numero,
@Parte= Rec_ParteIntervencion,
@Obs= Rec_Observacion,
@ActivUsr= Rec_ActSiguiente,
@CodDesest= Rec_CodDesestima,
@FecReso= Rec_FechaResolucion,
@FecFinInterv= Rec_FechaIntervencion,
@ParteReso= Rec_ParteResolucion,
@Asociado = Rec_Asociado,
@ActAnterior= Rec_ActAnterior,
@FecBaja= Rec_FechaBaja,
@RacId = RAC_IdEnvRF
From Inserted

Select -- Este select tiene costo 76
@OldFecReso= Rec_FechaResolucion,
@OldParteReso= Rec_ParteResolucion
From Deleted

/*
** SOLO ENTRAR SI ES PADRE
*/
If @Asociado = 'P'
BEGIN
IF @Estado = 'F'
Begin
Update SAR_Reclamos Set
Rec_Estado = @Estado,
Rec_FechaBaja = @FecBaja,
Rec_Asignado = 0 ,
Rec_EstActActual = 5,
Rec_ActSiguiente = @ActivUsr,
Rec_FechaResolucion = Case when Rec_FechaResolucion Is null then @FecReso
when Rec_FechaResolucion = @OldFecReso then @FecReso
else Rec_FechaResolucion End,
Rec_ParteResolucion = Case when Rec_ParteResolucion is Null then @ParteReso
when Rec_ParteResolucion = @OldParteReso then @ParteReso
else Rec_ParteResolucion End,
Rec_ActAnterior = @ActAnterior,
RAC_IdEnvRF = @RacId
From
SAR_ReclamosAsociados (nolock)
Where
Asi_NumeroAso = Rec_Numero And
Asi_Numero = @Numero And
Asi_Accion = 'A' And
Asi_Activo = 'S'
End
IF @Estado = 'I'
Begin
Update SAR_Reclamos Set
Rec_Estado= @Estado,
Rec_FechaIntervencion= @FecFinInterv,
Rec_ParteIntervencion= @Parte,
Rec_Asignado= 0,
Rec_ActSiguiente= @ActivUsr,
Rec_FechaResolucion = Case when Rec_FechaResolucion Is null then @FecReso
when Rec_FechaResolucion = @OldFecReso then @FecReso
else Rec_FechaResolucion End,
Rec_ParteResolucion = Case when Rec_ParteResolucion is Null then @ParteReso
when Rec_ParteResolucion = @OldParteReso then @ParteReso
else Rec_ParteResolucion End,
Rec_ActAnterior= @ActAnterior
From
SAR_ReclamosAsociados (nolock)
Where

Asi_NumeroAso = Rec_Numero And
Asi_Numero = @Numero And
Asi_Accion = 'A' And
Asi_Activo = 'S'
End
IF @Estado = 'N'
Begin
Update SAR_Reclamos Set
Rec_Estado= @Estado,
Rec_FechaIntervencion= @FecFinInterv,
Rec_ParteIntervencion= @Parte,
Rec_Asignado= 0,
Rec_ActSiguiente= @ActivUsr,
Rec_FechaResolucion = Case when Rec_FechaResolucion Is null then @FecReso
when Rec_FechaResolucion = @OldFecReso then @FecReso
else Rec_FechaResolucion End,
Rec_ParteResolucion = Case when Rec_ParteResolucion is Null then @ParteReso
when Rec_ParteResolucion = @OldParteReso then @ParteReso
else Rec_ParteResolucion End,
Rec_ActAnterior= @ActAnterior
From
SAR_ReclamosAsociados (nolock)
Where
Asi_NumeroAso = Rec_Numero And
Asi_Numero = @Numero And
Asi_Accion = 'A' And
Asi_Activo = 'S'
End

If @Estado = 'B'
Begin
Update
SAR_Reclamos
SET
--Rec_Observacion = @Obs , C-2001-231
Rec_Observacion = '(ODT Desestimada) - ' + Rtrim(Rec_Observacion),
Rec_Estado = 'B' ,
Rec_FechaBaja = @FecBaja,
Rec_ActSiguiente= @ActivUsr,
Rec_EstActActual= 2,
Rec_Asignado = 0,
Rec_CodDesestima = @CodDesest,
Rec_FechaResolucion = Case when Rec_FechaResolucion Is null then @FecReso
when Rec_FechaResolucion = @OldFecReso then @FecReso
else Rec_FechaResolucion End,
Rec_ParteResolucion = Case when Rec_ParteResolucion is Null then @ParteReso
when Rec_ParteResolucion = @OldParteReso then @ParteReso
else Rec_ParteResolucion End,
Rec_ActAnterior = @ActAnterior,
RAC_IdEnvRF= @RacId
From
SAR_ReclamosAsociados (nolock)
Where
Asi_NumeroAso = Rec_Numero And
Asi_Numero = @Numero And
Asi_Accion = 'A' And
Asi_Activo = 'S'

/*
** INSERTAR EL REGISTRO DEL HIJO EN LA TABLA ASOCIACIONES
*/
Declare Tmp Cursor For
Select Asi_NumeroAso
From Sar_ReclamosAsociados (NOLOCK)
Where
Asi_Numero = @Numero And
Asi_Accion = 'A' And
Asi_Activo = 'S'
Open Tmp
Fetch Next From Tmp Into @NumeroAso
While (@@Fetch_Status <> -1 )
Begin
Insert Into SAR_ReclamosActividad
(Rac_Numero ,Rac_FechaActividad ,Rac_Actividad,
Rac_CodigoEstado, Rac_AccionObs , Rac_Operador , OEN_IDODS )
Values
(@NumeroAso , GetDate() , @ActivUsr, 2 ,
'Desestimado Por Asociacion' , @Usuario , null)

Fetch Next From Tmp Into @NumeroAso
End
Deallocate Tmp
End
End
END
END

Luis:

Este es el SP_helpindex de la Tabla


-----------
1701373 SON LOS REGISTROS QUE TIENE LA TABLA.

(1 row(s) affected)

Saludos.




index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_SAR_REC nonclustered, unique, primary key located on PRIMARY REC_Numero
IX_SAR_REC_01 nonclustered located on PRIMARY REC_Estado, REC_FechaIngreso
IX_SAR_REC_02 nonclustered located on PRIMARY REC_Calle, REC_Tipo, REC_Estado
IX_SAR_REC_03 nonclustered located on PRIMARY REC_Calle, REC_Altura, REC_FechaIngreso
IX_SAR_REC_04 nonclustered located on PRIMARY REC_Calle, REC_Calle_Int, REC_FechaIngreso
IX_SAR_REC_05 nonclustered located on PRIMARY REC_HisCodDistrito, REC_Estado
IX_SAR_REC_06 nonclustered located on PRIMARY REC_FechaBaja, REC_HisCodDistrito
IX_SAR_REC_08 nonclustered located on PRIMARY REC_Motivo, REC_Estado, REC_Localidad
IX_SAR_REC_09 nonclustered located on PRIMARY REC_ActSiguiente, REC_Estado, REC_EstActActual
IX_SAR_REC_10 nonclustered located on PRIMARY REC_Estado, REC_HisCodDistrito, REC_ActSiguiente, REC_EstActActual, REC_Asociado
IX_SAR_REC_11 nonclustered located on PRIMARY REC_CodSegmento, REC_CodSegmentoInt
IX_SAR_REC_12 nonclustered located on PRIMARY REC_Clase, REC_Tipo, REC_Motivo
IX_SAR_REC_13 nonclustered located on PRIMARY REC_Calle, REC_Altura, REC_Piso, REC_Dpto, REC_DatoAdic
IX_SAR_REC_14 nonclustered located on PRIMARY REC_HisCodDistrito, REC_Estado, REC_IdRelacionMotivo
IX_SAR_REC_15 nonclustered located on PRIMARY REC_HisCodPlanch, REC_CodManzanero

Luis:

Una de las cosas que quería aclararte es que el Update se ejecuta dentro de un Stored Procedure dentro de un cursor que hace el update puntual a un registro.

Te adjunto el Update.
UPDATE SAR_Reclamos SET

Rec_EstActActual = @EstadoActiv,
Rec_Actsiguiente = @Actividad,
Rec_EstOdtAct = 0,
Rec_ActAnterior = @ActividadAct,
Oen_IdOds = @IdOds
WHERE
Rec_numero = @NumeroODT

saludos
Juan Manuel

Spero di aver capito bene il problema, e che tu capisca l'italiano...
Da quello che capisco io, ci sono alcuni problemi con questo trigger.
la prima parte non prende in considerazione piu di una riga modificata. Questo vuol dire che nei parametri avrai sempre l'ultima riga dalla tabella deleted or inserted.
Ti consiglio invece di fare tutti questi IFs, di far girare diversi updates con diversi condizioni (o forse usare CASE, come l'hai fatto con Rec_FechaResolucion).
prendi per esempio il primo update

If @Asociado = 'P'
BEGIN
IF @Estado = 'F'...
puoi aggiungere un join con le tabelle inserted e deleted piu' le condizioni (WHERE @Asociado = 'P' and @Estado = 'F' oppure quello che ti serve). Ti servono anche indici sui campi nella WHERE (almeno Asi_NumeroAso, Asi_Numero), e i campi che partecipano nella join con inserted/deleted. Altrimenti viene fatto un table scan.

Anche il cursore non e necessario. cursore dentro un trigger e una cosa da evitare quando e possibile. Lo poui cambiare con un semplice insert
Insert Into SAR_ReclamosActividad
(Rac_Numero ,Rac_FechaActividad ,Rac_Actividad, Rac_CodigoEstado, Rac_AccionObs , Rac_Operador , OEN_IDODS )
SELECT (Asi_NumeroAso , GetDate() , @ActivUsr, 2 , 'Desestimado Por Asociacion' , @Usuario , null)
From Sar_ReclamosAsociados (NOLOCK)
inner join inserted on....
Where Asi_Numero = @Numero And
Asi_Accion = 'A' And
Asi_Activo = 'S'
Anche qui ti server un join con la tabella inserted.
Una domanda, quante rige vengano inserite nella tabella SAR_ReclamosActividad e qualli sono l'indici?

Bambola.

Luis:

Aparentemente acá teníamos un camión delante y no lo veíamos.
El dato de los índices clustered nos ha ayudado mucho pues es algo de lo que teníamos conocimiento pero evidentemente nos pasó por delante y no lo vimos.

Haremos unas pruebas modificando algúnos de los índices y ver si da resultado.

Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)


El HarWare donde está corriendo tiene
RAM 2048 MB
4 proceasdores PIII 400MHz


El Servidor se utiliza solamente para tener la base de datos EXCLUSIVAMENTE. Son 2 bases de datos de una de 219MB y otra de 11440 MB que es donde se ejecuta el trigger en cuestión.


Saludos y Gracias
Juan Manuel Caballero.

Ciao Bambola:

No se si está bien mi precario italiano. (I don't know if my italian is ok.)

Mi problema en el trigger es el costo que tienen las siguientes sentencias:
(My problem in the trigger is the cost in the following sentences.)

-- Este SELECT TIENE COSTO 76 (In Query Plan this had a COST of 76)
Select @Estado = Rec_Estado,
@Numero = Rec_Numero,
@Parte = Rec_ParteIntervencion,
@Obs = Rec_Observacion,
@ActivUsr = Rec_ActSiguiente,
@CodDesest = Rec_CodDesestima,
@FecReso = Rec_FechaResolucion,
@FecFinInterv = Rec_FechaIntervencion,
@ParteReso = Rec_ParteResolucion,
@Asociado = Rec_Asociado,
@ActAnterior = Rec_ActAnterior,
@FecBaja = Rec_FechaBaja,
@RacId = RAC_IdEnvRF
From Inserted

Select -- Este select tiene costo 76
@OldFecReso = Rec_FechaResolucion,
@OldParteReso = Rec_ParteResolucion
From Deleted


En el resto del trigger no tengo problemas. Se que lo que indicas de los Cursores es correcto pero quiero detectar por que cuesta tanto el select sobre las tablas INSERTED y Deleted.
(I only had problems with those sentences, I know that Cursors are not recommended but my goal is to know why those simple sentences have a cost of 76)

Gracias
Grazie ? Thank you

Ho capito <img src='/community/emoticons/emotion-1.gif' alt='' /><br />Secondo me mancano l'indici sulla tabella SAR_ReclamosAsociados. Prova ad aggiungere un indice sui seguenti campi: Asi_NumeroAso, Asi_Numero [opzionali- Asi_Accion, Asi_Activo]<br />e vedi se cambia qualcosa.<br />un'altra cosa, quando dici 76 intendi 76% or 76ms?<br /><br />Bambola.

Luis:

Dropeamos los Indices y Primary keys que no eran Clustered.
Luego los volvimos a Crear como clustered y los select que tenían problemas pasaron de 76 a 95 el costo.

En varias tablas de las que creamos es conveniente que así sea (pues el campo que conforma la PK es un Int), pero me queda la duda en una donde tengo que el campo que conforma la primary key es un campo Varchar de 14 que guarda el Número de Reclamo y tiene la siguiente forma.
R-2003-1
R-2003-331
I-2003-2
I-2003-4
X-2003-2
X-2003-4
C-2003-2
C-2003-4

Por lo que tener un clustered index implica que cada vez que inserto un nuevo registro tendrá que ubicarlo en forma ordenada lo que me preocupa.

Saludos
Juan Manuel Caballero.

Luis,

Con respecto al tema del Scan en el inserted, te comento que estuve probando en otro Servidor con otras características.
Si bién en el servidor que tenemos de desarrollo el table Scan en Inserted y Deleted tenía un costo de 76, probando en otro Servidor, con igual base de datos, (un poco mas de registros) el costo de la misma sentencia es de 0,00376 por lo que intuyo que debe ser un problema de configuración de SQL, HardWare y todo ese tipo de problemas que a veces los desarrolladores no manejamos a fondo.

Saludos
y gracias.
Juan Manuel Caballero.

En realidad uno de los Servidores está en Capital y accedo via red.
El otro si está en Rosario.

De todas maneras, para poder contratar a alguien tenemos que pasar a alerta roja (actualmente estamos en alerta amarilla) y lo único que puedo hacer por vos es recomendar al cliente que te llame. (pasame tus datos por mail y te tendremos en cuenta, sin prometerte nada pues es el cliente quien decidirá.)


La info de los servidores no la tengo commpleta y el RAID debo preguntar.

Saludos.


Server con Costo alto en Inserted.
HP- 2 procesadores - RAM 512 MB
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0





SERVER SIN PROBLEMAS EN INSERTED O DELETED

name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 1 1
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0