Deleting large amount of data on sql server 2000

Deleting large amount of data on sql server 2000

Post by haris » Sat, 05 Nov 2005 08:43:35


Hi
I am new to SQL server. Hence please help me.

I need to delete around 100 million records of a table in SQL Server
2000.
One delete statement is creating the transaction log issue.

1) Can I run a DELETE statement without recording in the transaction
log?

2) Can I run the delete in steps? If so how do I do it?

3) The table has a composite index on 4 columns A, B, C and D (in that
order). If the where clause in the index contains the conditions on B
and C coulmns alone. Will the index be used or not?

Thanks
Harish
 
 
 

Deleting large amount of data on sql server 2000

Post by TUw » Sat, 05 Nov 2005 09:02:02

> 2) Can I run the delete in steps? If so how do I do it?

Try something like this:
delete <table>
from <table>
inner join (
select top 1000 *
from <table>
order by <indexed_column>
) <alias>
on <alias>.<key_column> = <table>.<key_column>


ML

 
 
 

Deleting large amount of data on sql server 2000

Post by TUw » Sat, 05 Nov 2005 09:07:11

Oh, yeah - and you might want to wrap that one into a loop:

while @@rowcount > 0
begin

...delete statement here...

end


Test, re-test, double-test, and - just to make sure - test again.


ML
 
 
 

Deleting large amount of data on sql server 2000

Post by harish » Sat, 05 Nov 2005 10:21:42


while @@rowcount > 0
begin

..delete statement here...

end

Hi thanks
in this case should it be run as a stored procedure or a single sql
statement?

*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

Deleting large amount of data on sql server 2000

Post by Itzik Ben- » Sat, 05 Nov 2005 10:28:46

Harish,


No. Only TRUNCATE and DROP statements are minimally logged. But these you
use when you need to clean/drop the whole table.


Yes. e.g., steps of 5000 in each transaction:

SET ROW_COUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @@rowcount < 5000 BREAK;
END
SET ROW_COUNT 5000;


Not for an efficient seek operation. But the optimizer will consult
statistics to see if scanning the whole leaf level of the index and then
doing lookups (in case it's not a covering index) might help.

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Join us for the SQL Server 2005 launch at the SQL Week in Israel!
http://www.yqcomputer.com/
 
 
 

Deleting large amount of data on sql server 2000

Post by haris » Sat, 05 Nov 2005 10:41:02

Hii

Thanks a lot for the code.
i have another question.
Do I run this as a single SQl or as a stored procedure.

Thanks
Harish
 
 
 

Deleting large amount of data on sql server 2000

Post by Itzik Ben- » Sat, 05 Nov 2005 10:53:50

Either way is fine.

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Join us for the SQL Server 2005 launch at the SQL Week in Israel!
http://www.yqcomputer.com/
 
 
 

Deleting large amount of data on sql server 2000

Post by haris » Sat, 05 Nov 2005 10:57:57

Hey

I didnt understand how this @@rowcount works.Basically I didnt
understand how this loop will work.
Can you please explain?
 
 
 

Deleting large amount of data on sql server 2000

Post by haris » Sat, 05 Nov 2005 11:10:19

But is there no COMMIT statement required here?
 
 
 

Deleting large amount of data on sql server 2000

Post by haris » Sat, 05 Nov 2005 11:56:03

hey ben
can you pls explain the logic
 
 
 

Deleting large amount of data on sql server 2000

Post by Itzik Ben- » Sat, 05 Nov 2005 14:17:57

Sure,

The idea is to split the large transaction into smaller ones, like you
asked.

Remember that in SQL Server, unless within an explicit outer transaction,
each statement is its own transaction, as if encapsulated with BEGIN
TRAM/COMMIT TRAN.

The SET ROW_COUNT command changes the way your session behaves in the sense
that any statement would stop processing rows as once it processed the
number of rows specified in this option.
So, the following statement sets this value to 5000:
SET ROW_COUNT 5000;
From now on, any statement processing data will stop after 5000 rows are
processed.
Then the following endless loop fires a DELETE, which affects 5000 rows in
each iteration. And since it's an independent statement, it commits every
5000 rows. Once the DELETE affects less (you reached the last batch), the
loop breaks.
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @@rowcount < 5000 BREAK;
END

Once done, remove the SET ROW_COUNT limitation by setting it to 0:
SET ROW_COUNT 0;

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Join us for the SQL Server 2005 launch at the SQL Week in Israel!
http://www.yqcomputer.com/
 
 
 

Deleting large amount of data on sql server 2000

Post by Itzik Ben- » Sat, 05 Nov 2005 14:27:42

Correction:

Should be SET ROWCOUNT, and not SET ROW_COUNT.

Here's the full code again:

SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @@rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;

--
BG, SQL Server MVP
www.SolidQualityLearning.com

Join us for the SQL Server 2005 launch at the SQL Week in Israel!
http://www.yqcomputer.com/
 
 
 

Deleting large amount of data on sql server 2000

Post by Stephany Y » Sat, 05 Nov 2005 15:56:11

Have you considered working from the opposite direction to solve this issue?

You have not mentioned the number of existing rows in the table so I am
assuming that the number of rows remaining after the delete will be
relatively small compared to the 100,000,000 rows you are deleting.

Try (in a single batch) something like:

select <column list> into #temp from <table> where <reverse of where
clause for deletion>

truncate table <table>

insert into <table>(<column list) select (column list) from #temp

drop table #temp
 
 
 

Deleting large amount of data on sql server 2000

Post by TUw » Sat, 05 Nov 2005 17:21:11

If you plan on using it often, then put it in a procedure. Also look at
Itzik's posts - his solution may perform better.


ML
 
 
 

Deleting large amount of data on sql server 2000

Post by haris » Sun, 06 Nov 2005 00:14:06

Thanks Ben for the detailed explanation.

Thanks again
byee
harish