Trigger Problem – From Spanish Forum | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Trigger Problem – From Spanish Forum

Juan have a performance problem with a trigger.
He run a sp with a trigger and find that trigger has 76% cost (from Execution plan) and no problem with rest of sp.
There is the trigger and I translate any question or solution. Thanks, 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
@Estado char(1),
@Numero varchar(14),
@Parte varchar(14),
@Obs varchar(255),
@ActivUsr int,
@CodDesest int,
@Usuario int,
@NumeroAso varchar(14),
@FecReso datetime,
@ParteReso varchar(14),
@OldFecReso datetime,
@OldParteReso varchar(14),
@Asociado char(1),
@ActAnterior int,
@FecFinInterv datetime,
@FecBaja datetime,
@RacId Int /* 05/07/00 */
/*
**
*/
Select @Usuario = Usr_IdUsuario
From SAR_Usuario (nolock)
Where rtrim(Usr_Nombre) = lower(system_user)
/*
** OBTENER LOS VALORES NUEVOS DE LOS CAMPOS MODIFICADOS
*/
BEGIN — Este SELECT TIENE COSTO 76 (this select has 76 cost).
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 (This select has 76 cost)
@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 Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
How many rows are affected with this trigger?
And hwo about indexes on those tables involved in trigger? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Este es el SP_helpindex de la Tabla (This is Sp_.. of table)
———–
1701373 SON LOS REGISTROS QUE TIENE LA TABLA. (Rows) (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 Satya, do you think a index by Rec_FechaResolucion,Rec_ParteResolucion would help?
Because DELETE part.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I can’t keep close watch on the SP & trigger, as a thumb rule generating proper index on involved columns would help increasing the performance. And I can see million rows are involved in the process, so expected bit performance hitch provided H/w is resource not enough. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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. (One of the thing I would like to said is Update is in Stored Procedure with cursor to update strictly one record)
UPDATE SAR_Reclamos SET Rec_EstActActual = @EstadoActiv,
Rec_Actsiguiente = @Actividad,
Rec_EstOdtAct = 0,
Rec_ActAnterior = @ActividadAct,
Oen_IdOds = @IdOds
WHERE
Rec_numero = @NumeroODT
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Is the clustered index is created/invovled? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I don’t see any cluster in information he send.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Are there any other process involved or running parallely with this SP execution? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The problem was no cluster index at all. Thanks Satya and Bambola.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Luis where is this spanish Forum you are talking about? Cheers!
Here it is http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=21 Bambola.
]]>