optimizacion de update PARA MAXY

[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=1))

maximiliano aka te mando el plan de ejecucion

StmtText



Update Comprobante
Set Montocancelado=Isnull((Case A.Monedaid
When 1 Then (Select Sum(Isnull(Z.monpag,0)) From TMP_PAGOS Z
Where Z.numdco=A.numero
and a.tipocomprobanteid=Z.tipdoc and z.serdco=a.serieid AND
Z.TIPMON=1 )

When 2 Then (Select Sum(Isnull(Z.monpad,0)) From TMP_PAGOS Z
Where Z.numdco=A.numero
and a.tipocomprobanteid=Z.tipdoc and z.serdco=a.serieid AND
Z.TIPMON=2 )
End),0)

From
Comprobante A

(1 filas afectadas)

StmtText

|--Table Update(OBJECT:([SISCOM].[dbo].[Comprobante]),
SET:([Comprobante].[MontoCancelado]=RaiseIfNull([Expr1009])))
|--Compute Scalar(DEFINE:([Expr1009]=Convert(isnull(If
([A].[MonedaId]=1) then [Expr1004] else If ([A].[MonedaId]=2) then
[Expr1007] else NULL, 0.00000))))
|--Nested Loops(Inner Join, WHERE:([A].[MonedaId]=1 OR
IsFalseOrNull([A].[MonedaId]=2))OUTER REFERENCES:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]))
|--Nested Loops(Inner Join,
WHERE:(IsFalseOrNull([A].[MonedaId]=1))OUTER REFERENCES:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]))
| |--Top(ROWCOUNT est 0)
| | |--Table
Scan(OBJECT:([SISCOM].[dbo].[Comprobante] AS [A]))
| |--Hash Match(Cache, HASH:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]),
RESIDUAL:(([A].[SerieId]=[A].[SerieId] AND
[A].[TipoComprobanteId]=[A].[TipoComprobanteId]) AND
[A].[Numero]=[A].[Numero]))
| |--Compute Scalar(DEFINE:([Expr1004]=If
([Expr1018]=0) then NULL else [Expr1019]))
| |--Stream
Aggregate(DEFINE:([Expr1018]=COUNT_BIG(isnull([Z].[Monpag], 0.00000)),
[Expr1019]=SUM(isnull([Z].[Monpag], 0.00000))))
| |--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero] AND
[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=1))
| |--Index
Spool(SEEK:([Z].[Serdco]=[A].[SerieId]))
| |--Table
Scan(OBJECT:([SISCOM].[dbo].[Tmp_pagos] AS [Z]))
|--Hash Match(Cache, HASH:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]),
RESIDUAL:(([A].[SerieId]=[A].[SerieId] AND
[A].[TipoComprobanteId]=[A].[TipoComprobanteId]) AND
[A].[Numero]=[A].[Numero]))
|--Compute Scalar(DEFINE:([Expr1007]=If
([Expr1020]=0) then NULL else [Expr1021]))
|--Stream
Aggregate(DEFINE:([Expr1020]=COUNT_BIG(isnull([Z].[Monpad], 0.00000)),
[Expr1021]=SUM(isnull([Z].[Monpad], 0.00000))))

|--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero] AND
[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=2))
|--Index
Spool(SEEK:([Z].[Serdco]=[A].[SerieId]))
|--Table
Scan(OBJECT:([SISCOM].[dbo].[Tmp_pagos] AS [Z]))

(18 filas afectadas)

Hola, bueno aca hay varias cosas interesantes ;-)

|--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero] AND

[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=1))

Son el mismo tipo de datos Z.NUMDCO con A.numero? que tipo de datos es
Tipmon?


[Microsoft MVP SQL SERVER]
Culminis SQL-Server Speakers (http://latam.culminis.com)

Maxi - Buenos Aires - Argentina
Msn_messager:
mail: Maxi.da[arroba]gmail.com

"cali" <nada> escribió en el mensaje
news:

maximiliano aka te mando el plan de ejecucion

StmtText



Update Comprobante
Set Montocancelado=Isnull((Case A.Monedaid
When 1 Then (Select Sum(Isnull(Z.monpag,0)) From TMP_PAGOS Z
Where Z.numdco=A.numero
and a.tipocomprobanteid=Z.tipdoc and z.serdco=a.serieid AND
Z.TIPMON=1 )

When 2 Then (Select Sum(Isnull(Z.monpad,0)) From TMP_PAGOS Z
Where Z.numdco=A.numero
and a.tipocomprobanteid=Z.tipdoc and z.serdco=a.serieid AND
Z.TIPMON=2 )
End),0)

From
Comprobante A

(1 filas afectadas)

StmtText

|--Table Update(OBJECT:([SISCOM].[dbo].[Comprobante]),
SET:([Comprobante].[MontoCancelado]=RaiseIfNull([Expr1009])))
|--Compute Scalar(DEFINE:([Expr1009]=Convert(isnull(If
([A].[MonedaId]=1) then [Expr1004] else If ([A].[MonedaId]=2) then
[Expr1007] else NULL, 0.00000))))
|--Nested Loops(Inner Join, WHERE:([A].[MonedaId]=1 OR
IsFalseOrNull([A].[MonedaId]=2))OUTER REFERENCES:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]))
|--Nested Loops(Inner Join,
WHERE:(IsFalseOrNull([A].[MonedaId]=1))OUTER REFERENCES:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]))
| |--Top(ROWCOUNT est 0)
| | |--Table
Scan(OBJECT:([SISCOM].[dbo].[Comprobante] AS [A]))
| |--Hash Match(Cache, HASH:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]),
RESIDUAL:(([A].[SerieId]=[A].[SerieId] AND
[A].[TipoComprobanteId]=[A].[TipoComprobanteId]) AND
[A].[Numero]=[A].[Numero]))
| |--Compute Scalar(DEFINE:([Expr1004]=If
([Expr1018]=0) then NULL else [Expr1019]))
| |--Stream
Aggregate(DEFINE:([Expr1018]=COUNT_BIG(isnull([Z].[Monpag], 0.00000)),
[Expr1019]=SUM(isnull([Z].[Monpag], 0.00000))))
| |--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero]
AND [A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=1))
| |--Index
Spool(SEEK:([Z].[Serdco]=[A].[SerieId]))
| |--Table
Scan(OBJECT:([SISCOM].[dbo].[Tmp_pagos] AS [Z]))
|--Hash Match(Cache, HASH:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]),
RESIDUAL:(([A].[SerieId]=[A].[SerieId] AND
[A].[TipoComprobanteId]=[A].[TipoComprobanteId]) AND
[A].[Numero]=[A].[Numero]))
|--Compute Scalar(DEFINE:([Expr1007]=If
([Expr1020]=0) then NULL else [Expr1021]))
|--Stream
Aggregate(DEFINE:([Expr1020]=COUNT_BIG(isnull([Z].[Monpad], 0.00000)),
[Expr1021]=SUM(isnull([Z].[Monpad], 0.00000))))

|--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero] AND
[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=2))
|--Index
Spool(SEEK:([Z].[Serdco]=[A].[SerieId]))
|--Table
Scan(OBJECT:([SISCOM].[dbo].[Tmp_pagos] AS [Z]))

(18 filas afectadas)

tenias razon maximiliano.,,,eran por los tipos de datosuna vez
uniformizados la actualizacion demora 3 o 5 segundos

____________
Daniel Franco
99505203

"Maxi [MVP SQL Server]" escribió en el
mensaje news:

Hola, bueno aca hay varias cosas interesantes ;-)

|--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero] AND

[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=1))

Son el mismo tipo de datos Z.NUMDCO con A.numero? que tipo de datos es
Tipmon?


[Microsoft MVP SQL SERVER]
Culminis SQL-Server Speakers (http://latam.culminis.com)

Maxi - Buenos Aires - Argentina
Msn_messager:
mail: Maxi.da[arroba]gmail.com

"cali" <nada> escribió en el mensaje
news:

maximiliano aka te mando el plan de ejecucion

StmtText



Update Comprobante
Set Montocancelado=Isnull((Case A.Monedaid
When 1 Then (Select Sum(Isnull(Z.monpag,0)) From TMP_PAGOS Z
Where Z.numdco=A.numero
and a.tipocomprobanteid=Z.tipdoc and z.serdco=a.serieid AND
Z.TIPMON=1 )

When 2 Then (Select Sum(Isnull(Z.monpad,0)) From TMP_PAGOS Z
Where Z.numdco=A.numero
and a.tipocomprobanteid=Z.tipdoc and z.serdco=a.serieid AND
Z.TIPMON=2 )
End),0)

From
Comprobante A

(1 filas afectadas)

StmtText

|--Table Update(OBJECT:([SISCOM].[dbo].[Comprobante]),
SET:([Comprobante].[MontoCancelado]=RaiseIfNull([Expr1009])))
|--Compute Scalar(DEFINE:([Expr1009]=Convert(isnull(If
([A].[MonedaId]=1) then [Expr1004] else If ([A].[MonedaId]=2) then
[Expr1007] else NULL, 0.00000))))
|--Nested Loops(Inner Join, WHERE:([A].[MonedaId]=1 OR
IsFalseOrNull([A].[MonedaId]=2))OUTER REFERENCES:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]))
|--Nested Loops(Inner Join,
WHERE:(IsFalseOrNull([A].[MonedaId]=1))OUTER REFERENCES:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]))
| |--Top(ROWCOUNT est 0)
| | |--Table
Scan(OBJECT:([SISCOM].[dbo].[Comprobante] AS [A]))
| |--Hash Match(Cache, HASH:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]),
RESIDUAL:(([A].[SerieId]=[A].[SerieId] AND
[A].[TipoComprobanteId]=[A].[TipoComprobanteId]) AND
[A].[Numero]=[A].[Numero]))
| |--Compute Scalar(DEFINE:([Expr1004]=If
([Expr1018]=0) then NULL else [Expr1019]))
| |--Stream
Aggregate(DEFINE:([Expr1018]=COUNT_BIG(isnull([Z].[Monpag], 0.00000)),
[Expr1019]=SUM(isnull([Z].[Monpag], 0.00000))))
| |--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero]
AND [A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=1))
| |--Index
Spool(SEEK:([Z].[Serdco]=[A].[SerieId]))
| |--Table
Scan(OBJECT:([SISCOM].[dbo].[Tmp_pagos] AS [Z]))
|--Hash Match(Cache, HASH:([A].[SerieId],
[A].[TipoComprobanteId], [A].[Numero]),
RESIDUAL:(([A].[SerieId]=[A].[SerieId] AND
[A].[TipoComprobanteId]=[A].[TipoComprobanteId]) AND
[A].[Numero]=[A].[Numero]))
|--Compute Scalar(DEFINE:([Expr1007]=If
([Expr1020]=0) then NULL else [Expr1021]))
|--Stream
Aggregate(DEFINE:([Expr1020]=COUNT_BIG(isnull([Z].[Monpad], 0.00000)),
[Expr1021]=SUM(isnull([Z].[Monpad], 0.00000))))

|--Filter(WHERE:((Convert([Z].[Numdco])=[A].[Numero] AND
[A].[TipoComprobanteId]=Convert([Z].[Tipdoc])) AND
Convert([Z].[Tipmon])=2))
|--Index
Spool(SEEK:([Z].[Serdco]=[A].[SerieId]))
|--Table
Scan(OBJECT:([SISCOM].[dbo].[Tmp_pagos] AS [Z]))

(18 filas afectadas)