Depuracion Optima

Pao,

Pudieras tratar steando "rowcount", pero recuerda que eso no funciona con
tablas remotas y con vistas locales o remotas. Es por eso que Microsoft
recomienda el uso de "delete ... from (select top ...)". Como esta tabla no
tiene clave primaria, tendras que usar "rowcount".

declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

set rowcount 10000

while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
begin
delete navy..nv_trama
where w_fecha < @w_fecha
end
go


AMB

"Pao" wrote:

Entonces como seria el caso optimo para borrarlo por batch y de una sola los
registros que cumplan la condicion???

"Alejandro Mesa" wrote:

> Pao,
>
> No puedes hacerlo de esa manera. La sentencia "delete" usando (select top
> ...) solo debe emplearse si la clave primaria es parte de la lista de la
> sentencia "select". De lo contrario pudiera haber mas de 10,000 que cumplan
> "where w_fecha < @w_fecha" y entonces todos serian borrados y por lo tanto
> habria un solo batch.
>
> Ejemplo:
>
> use northwind
> go
>
> create table t1(
> c1 int not null
> )
> go
>
> insert into t1 values(1)
> insert into t1 values(1)
> go
>
> while exists(select * from t1 where c1 = 1)
> begin
> delete t1
> from t1 inner join (select top 1 * from t1 where c1 = 1) as t2
> on t1.c1 = t2.c1
> print @@rowcount
> end
> go
>
> alter table t1
> add c2 int not null identity unique
> go
>
> insert into t1(c1) values(1)
> insert into t1(c1) values(1)
> go
>
> while exists(select * from t1 where c1 = 1)
> begin
> delete t1
> from t1 inner join (select top 1 * from t1 where c1 = 1) as t2
> on t1.c2 = t2.c2
> print @@rowcount
> end
> go
>
> drop table t1
> go
>
>
> AMB
>
> "Pao" wrote:
>
> > El hacer el query como me lo indicas me genera este plan de ejecuion:
> > StmtText
> >
> >
> > declare @w_fecha as datetime
> > select @w_fecha = dateadd(m,-3,getdate())
> >
> > while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
> >
> > (2 row(s) affected)
> >
> > StmtText
> > |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> > |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
> > |--Constant Scan
> > |--Clustered Index
> > Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
> >
> > (4 row(s) affected)
> >
> > StmtText
> >
> > begin
> > delete a
> > from
> > navy.dbo.nv_trama as a
> > inner join
> > (select top 10000 * from navy..nv_trama where w_fecha < @w_fecha) as b
> > on a.w_fecha = b.w_fecha
> >
> > (1 row(s) affected)
> >
> > StmtText
> > |--Clustered Index
> > Delete(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]))
> > |--Top(ROWCOUNT est 0)
> > |--Hash Match(Right Semi Join,
> > HASH:([nv_trama].[w_fecha])=([a].[w_fecha]),
> > RESIDUAL:([a].[w_fecha]=[nv_trama].[w_fecha]))
> > |--Top(10000)
> > | |--Clustered Index
> > Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
> > |--Clustered Index
> > Scan(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama] AS [a]))
> >
> > (6 row(s) affected)
> >
> > StmtText
> >
> > end
> >
> > (1 row(s) affected)
> >
> >
> >
> >
> > Hacerlo de la otra forma como indicialmente lo habia puesto te genera este
> > plan>
> > StmtText
> > use navy
> >
> > (1 row(s) affected)
> >
> > StmtText
> >
> > declare @w_fecha as datetime
> > select @w_fecha = dateadd(m,-3,getdate())
> >
> > set rowcount 10000
> >
> > while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> >
> > (3 row(s) affected)
> >
> > StmtText
> > |--Compute Scalar(DEFINE:([Expr1004]=If ([Expr1002]>0) then 1 else 0))
> > |--Nested Loops(Inner Join)
> > |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1011])))
> > | |--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))
> > | |--Clustered Index
> > Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
> > |--Constant Scan
> >
> > (6 row(s) affected)
> >
> > StmtText
> > begin
> > delete navy..nv_trama
> > where w_fecha < @w_fecha
> >
> > (1 row(s) affected)
> >
> > StmtText
> > |--Clustered Index
> > Delete(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > WHERE:([nv_trama].[w_fecha] < [@w_fecha]))
> >
> > (1 row(s) affected)
> >
> > StmtText
> >
> > end
> >
> > dump tran navy with no_log
> >
> > (1 row(s) affected)
> >
> >
> >
> > Sin embargo me da la impresion que la modficacion que hicistes lo hace mas
> > lento, por favor si me ayudas aclarando estos planes, ya que encuentro un
> > index scan...
> > Gracias por vuestra pronta ayuda
> >
> > "Alejandro Mesa" wrote:
> >
> > > Pao,
> > >
> > > No se a que te refieres cuando dices "depurar", pero en cuanto al scrip que
> > > posteastes, a pesar de ser una buena idea hacer el delete en batches, no es
> > > bueno que cuentes las filas como expresion logica del lazo.
> > >
> > > Pudieras decirnos cual es la clave primaria de esta tabla?
> > >
> > > declare @w_fecha as datetime
> > > select @w_fecha = dateadd(m,-3,getdate())
> > >
> > > while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
> > > begin
> > > delete a
> > > from
> > > navy.dbo.nv_trama as a
> > > inner join
> > > (select top 10000 * from navy..nv_trama where w_fecha < @w_fecha) as b
> > > on a.pk = b.pk
> > > end
> > > go
> > >
> > >
> > > AMB
> > >
> > > "Pao" wrote:
> > >
> > > > Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
> > > > El scripts es el sgte:
> > > > use navy
> > > > go
> > > >
> > > > declare @w_fecha as datetime
> > > > select @w_fecha = dateadd(m,-3,getdate())
> > > >
> > > > set rowcount 10000
> > > > while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> > > > begin
> > > > delete navy..nv_trama
> > > > where w_fecha < @w_fecha
> > > > end
> > > >
> > > > dump tran navy with no_log
> > > >
> > > >
> > > >
> > > > La estructura de mi tabla es:
> > > > Name Owner Type Created_datetime
> > > > nv_trama dbo user table 2003-09-06 08:24:59.153
> > > >
> > > >
> > > >
> > > > Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
> > > > w_fecha datetime no 8 no (n/a) (n/a)
> > > > w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
> > > > w_trama varchar no 2048 yes no no SQL_Latin1_General_CP1_CI_AS
> > > >
> > > >
> > > > Identity Seed Increment Not For Replication
> > > > No identity column defined.
> > > >
> > > >
> > > > RowGuidCol
> > > > No rowguidcol column defined.
> > > >
> > > >
> > > > Data_located_on_filegroup
> > > > PRIMARY
> > > >
> > > >
> > > > index_name index_description index_keys
> > > > IX_nv_trama clustered located on PRIMARY w_fecha
> > > >
> > > >
> > > > No constraints have been defined for this object.
> > > >
> > > > No foreign keys reference this table.
> > > > No views with schema binding reference this table.
> > > >
> > > >
> > > > Y la tabla tiene 220.000

hacerlo conforme a lo que indicas y de acuerdo al plan de ejecuion veo que es
mas sencillo y el i/o para el cluester index seek, es menor, entonces es
mejor usarlo de esta forma que me indicas que en vez de laprimera que postee.

/*

StmtText
declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

set rowcount 10000

while exists(select * from navy..nv_trama where w_fecha < @w_fecha)

(3 row(s) affected)

StmtText
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Clustered Index
Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)

(4 row(s) affected)

StmtText

begin
delete navy..nv_trama
where w_fecha < @w_fecha

(1 row(s) affected)

StmtText
|--Clustered Index
Delete(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
WHERE:([nv_trama].[w_fecha] < [@w_fecha]))

(1 row(s) affected)

StmtText

end

(1 row(s) affected)


*/
La primera forma
/*
StmtText
declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

set rowcount 10000

while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0

(3 row(s) affected)

StmtText
|--Compute Scalar(DEFINE:([Expr1004]=If ([Expr1002]>0) then 1 else 0))
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1011])))
| |--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))
| |--Clustered Index
Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
|--Constant Scan

(6 row(s) affected)

StmtText
begin
delete navy..nv_trama
where w_fecha < @w_fecha

(1 row(s) affected)

StmtText
|--Clustered Index
Delete(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
WHERE:([nv_trama].[w_fecha] < [@w_fecha]))

(1 row(s) affected)

StmtText

end

(1 row(s) affected)



*/
"Alejandro Mesa" wrote:

Pao,

Pudieras tratar steando "rowcount", pero recuerda que eso no funciona con
tablas remotas y con vistas locales o remotas. Es por eso que Microsoft
recomienda el uso de "delete ... from (select top ...)". Como esta tabla no
tiene clave primaria, tendras que usar "rowcount".

declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

set rowcount 10000

while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
begin
delete navy..nv_trama
where w_fecha < @w_fecha
end
go


AMB

"Pao" wrote:

> Entonces como seria el caso optimo para borrarlo por batch y de una sola los
> registros que cumplan la condicion???
>
> "Alejandro Mesa" wrote:
>
> > Pao,
> >
> > No puedes hacerlo de esa manera. La sentencia "delete" usando (select top
> > ...) solo debe emplearse si la clave primaria es parte de la lista de la
> > sentencia "select". De lo contrario pudiera haber mas de 10,000 que cumplan
> > "where w_fecha < @w_fecha" y entonces todos serian borrados y por lo tanto
> > habria un solo batch.
> >
> > Ejemplo:
> >
> > use northwind
> > go
> >
> > create table t1(
> > c1 int not null
> > )
> > go
> >
> > insert into t1 values(1)
> > insert into t1 values(1)
> > go
> >
> > while exists(select * from t1 where c1 = 1)
> > begin
> > delete t1
> > from t1 inner join (select top 1 * from t1 where c1 = 1) as t2
> > on t1.c1 = t2.c1
> > print @@rowcount
> > end
> > go
> >
> > alter table t1
> > add c2 int not null identity unique
> > go
> >
> > insert into t1(c1) values(1)
> > insert into t1(c1) values(1)
> > go
> >
> > while exists(select * from t1 where c1 = 1)
> > begin
> > delete t1
> > from t1 inner join (select top 1 * from t1 where c1 = 1) as t2
> > on t1.c2 = t2.c2
> > print @@rowcount
> > end
> > go
> >
> > drop table t1
> > go
> >
> >
> > AMB
> >
> > "Pao" wrote:
> >
> > > El hacer el query como me lo indicas me genera este plan de ejecuion:
> > > StmtText
> > >
> > >
> > > declare @w_fecha as datetime
> > > select @w_fecha = dateadd(m,-3,getdate())
> > >
> > > while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
> > >
> > > (2 row(s) affected)
> > >
> > > StmtText
> > > |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> > > |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
> > > |--Constant Scan
> > > |--Clustered Index
> > > Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > > SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
> > >
> > > (4 row(s) affected)
> > >
> > > StmtText
> > >
> > > begin
> > > delete a
> > > from
> > > navy.dbo.nv_trama as a
> > > inner join
> > > (select top 10000 * from navy..nv_trama where w_fecha < @w_fecha) as b
> > > on a.w_fecha = b.w_fecha
> > >
> > > (1 row(s) affected)
> > >
> > > StmtText
> > > |--Clustered Index
> > > Delete(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]))
> > > |--Top(ROWCOUNT est 0)
> > > |--Hash Match(Right Semi Join,
> > > HASH:([nv_trama].[w_fecha])=([a].[w_fecha]),
> > > RESIDUAL:([a].[w_fecha]=[nv_trama].[w_fecha]))
> > > |--Top(10000)
> > > | |--Clustered Index
> > > Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > > SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
> > > |--Clustered Index
> > > Scan(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama] AS [a]))
> > >
> > > (6 row(s) affected)
> > >
> > > StmtText
> > >
> > > end
> > >
> > > (1 row(s) affected)
> > >
> > >
> > >
> > >
> > > Hacerlo de la otra forma como indicialmente lo habia puesto te genera este
> > > plan>
> > > StmtText
> > > use navy
> > >
> > > (1 row(s) affected)
> > >
> > > StmtText
> > >
> > > declare @w_fecha as datetime
> > > select @w_fecha = dateadd(m,-3,getdate())
> > >
> > > set rowcount 10000
> > >
> > > while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> > >
> > > (3 row(s) affected)
> > >
> > > StmtText
> > > |--Compute Scalar(DEFINE:([Expr1004]=If ([Expr1002]>0) then 1 else 0))
> > > |--Nested Loops(Inner Join)
> > > |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1011])))
> > > | |--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))
> > > | |--Clustered Index
> > > Seek(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > > SEEK:([nv_trama].[w_fecha] < [@w_fecha]) ORDERED FORWARD)
> > > |--Constant Scan
> > >
> > > (6 row(s) affected)
> > >
> > > StmtText
> > > begin
> > > delete navy..nv_trama
> > > where w_fecha < @w_fecha
> > >
> > > (1 row(s) affected)
> > >
> > > StmtText
> > > |--Clustered Index
> > > Delete(OBJECT:([NAVY].[dbo].[nv_trama].[IX_nv_trama]),
> > > WHERE:([nv_trama].[w_fecha] < [@w_fecha]))
> > >
> > > (1 row(s) affected)
> > >
> > > StmtText
> > >
> > > end
> > >
> > > dump tran navy with no_log
> > >
> > > (1 row(s) affected)
> > >
> > >
> > >
> > > Sin embargo me da la impresion que la modficacion que hicistes lo hace mas
> > > lento, por favor si me ayudas aclarando estos planes, ya que encuentro un
> > > index scan...
> > > Gracias por vuestra pronta ayuda
> > >
> > > "Alejandro Mesa" wrote:
> > >
> > > > Pao,
> > > >
> > > > No se a que te refieres cuando dices "depurar", pero en cuanto al scrip que
> > > > posteastes, a pesar de ser una buena idea hacer el delete en batches, no es
> > > > bueno que cuentes las filas como expresion logica del lazo.
> > > >
> > > > Pudieras decirnos cual es la clave primaria de esta tabla?
> > > >
> > > > declare @w_fecha as datetime
> > > > select @w_fecha = dateadd(m,-3,getdate())
> > > >
> > > > while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
> > > > begin
> > > > delete a
> > > > from
> > > > navy.dbo.nv_trama as a
> > > > inner join
> > > > (select top 10000 * from navy..nv_trama where w_fecha < @w_fecha) as b
> > > > on a.pk = b.pk
> > > > end
> > > > go
> > > >
> > > >
> > > > AMB
> > > >
> > > > "Pao" wrote:
> > > >
> > > > > Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
> > > > > El scripts es el sgte:
> > > > > use navy
> > > > > go
> > > > >
> > > > > declare @w_fecha as datetime
> > > > > select @w_fecha = dateadd(m,-3,getdate())
> > > > >
> > > > > set rowcount 10000
> > > > > while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> > > > > begin
> > > > > delete navy..nv_trama
> > > > > where w_fecha < @w_fecha
> > > > > end
> > > > >
> > > > > dump tran navy with no_log
> > > > >
> > > > >
> > > > >
> > > > > La estructura de mi tabla es:
> > > > > Name Owner Type Created_datetime
> > > > > nv_trama dbo user table 2003-09-06 08:24:59.153
> > > > >
> > > > >
> > > > >
> > > > > Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
> > > > > w_fecha datetime no 8 no (n/a) (n/a)
> > > > > w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
> > > > > w_trama varchar no 2048 yes no no SQL_Latin1_General_CP1_CI_AS
> > > > >
> > > > >
> > > > > Identity Seed Increment Not For Replication
> > > > > No identity column defined.
> > > > >
> > > > >
> > > > > RowGuidCol
> > > > > No rowguidcol column defined.
> > > > >
> > > > >
> > > > > Data_located_on_filegroup
> > > > > PRIMARY
> > > > >
> > > > >
> > > > > index_name index_description index_keys
> > > > > IX_nv_trama clustered located on PRIMARY w_fecha
> > > > >
> > > > >
> > > > > No constraints have been defined for this object.
> > > > >
> > > > > No foreign keys reference this table.
> > > > > No views with schema binding reference this table.
> > > > >
> > > > >
> > > > > Y la tabla tiene 220.000