deleting only 1000 rows at the time

Hi!
I have a huge table with about 40 mil of rows. I just discovered
that 10 mil can be safely deleted however, I do not want to delete all
in one shot. I would like to delete 10000 rows at the time.
This will start removing all 10 millions:
delete from apps where code = 'I'
how can I delete 10000 at the time?
Thank you,
T.On Sep 7, 10:42 am, tolcis <nytolly...@.gmail.com> wrote:
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
DELETE TOP(100) FROM ...|||What version of SQL Server are you running? With SQL Server 2005 the
TOP keyword is supported for DELETE, so it would look like:
DELETE TOP 100 FROM apps WHERE code = 'I'
SQL Server 2000 does not support TOP directly as part of DELETE so it
is a bit more complicated.
DELETE apps
FROM (SELECT TOP 1000 * FROM apps) as X
WHERE apps.appsKey = X.appsKey
Roy Harvey
Beacon Falls, CT
On Fri, 07 Sep 2007 08:42:54 -0700, tolcis <nytollydba@.gmail.com>
wrote:
>Hi!
>I have a huge table with about 40 mil of rows. I just discovered
>that 10 mil can be safely deleted however, I do not want to delete all
>in one shot. I would like to delete 10000 rows at the time.
>This will start removing all 10 millions:
>delete from apps where code = 'I'
>how can I delete 10000 at the time?
>Thank you,
>T.|||tolcis,
It depends on your SQL Server version, but this should work in 2000 and
2005.
DECLARE @.HowMany INT
SET ROWCOUNT 10000
SET @.HowMany = 1
WHILE @.HowMany > 0
BEGIN
delete from apps where code = 'I'
SELECT @.HowMany = @.@.Rowcount
END
SET ROWCOUNT 0
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1189179774.558864.201630@.o80g2000hse.googlegroups.com...
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
>|||After playing a bit, seems that a temp table reduce the system impact. On
the other hand, I have been logging in on Sundays to reduce my behemoth 120
million records... have it down to about 90 million at this time. I use the
wait for delay so that the system can move away from the big table to perform
other IO. The wait for delay can be any size. I chose five seconds below.
create table #deletedkeys(gianttablendx bigint)
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
drop table #deletedkeys
--
Regards,
Jamie
"tolcis" wrote:
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
>