DELETING IN BATCH DEGRADATION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DELETING IN BATCH DEGRADATION

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-03-28 : 11:48:43

Good Morning:

Here there is a script that delete records since a inicial date to an end_date. Its a huge table with aproximatly 900 millions of records, and even though in the WHERE Clause the field DAT_PROCESS has index, the script just erase 1 million every 3 hours, so it takes to much.

Th bottle line: there is no a better way to improve the best performance for this query, here it is:

SET NOCOUNT ON
set rowcount 1000000
while exists(select * from TABLE_MOVS
where DATE_PROCESS >= '20091231'
and DATE_PROCESS <= '20100131')
delete
from HECHOS_MOVTO_PREPAGO_ACTUACIONES
where DATE_PROCESS >= '20091231'
and DATE_PROCESS <= '20100131'

I appreciate your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 11:59:22

why not break it up into smaller batches and do the deletion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alejo46
Posting Yak Master

157 Posts

Posted - 2012-03-28 : 12:18:10

do you mean instead of erasing 1000000 records in a loop i should delete 10000 records (for example) in a loop ?

so the modest number to do the delete the bettet it is ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 12:20:10

yep..it will not cause log file to get filled up. Otherwise there's a chance of log file becoming full
Also did you check if index is getting used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-28 : 12:58:05

Does HECHOS_MOVTO_PREPAGO_ACTUACIONES have any Foreign Keys related to it?

how much data do you want to get rid of? all of it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

alejo46
Posting Yak Master

157 Posts

Posted - 2012-03-28 : 16:02:04

no, it has no foreign keys.

actuallly, from the table it has 45 miilions left to delet from 60 millions

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-28 : 17:24:21

Well, if there are no FK's, and you want to get rid of all the data, why don't you just do this?

TRUNCATE TABLE HECHOS_MOVTO_PREPAGO_ACTUACIONES

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-03-28 : 22:56:02

looks like OP doesn't want all rows deleted. only these:

where DATE_PROCESS >= '20091231' and DATE_PROCESS <= '20100131'

elsasoft.org

alejo46
Posting Yak Master

157 Posts

Posted - 2012-03-28 : 23:39:52

thats right, im no entendded to truncate the table, just deleting the records where DATE_PROCESS >= '20091231' and DATE_PROCESS <= '20100131'.

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 10:38:39

OK, how's about:

SELECT * INTO new_HECHOS_MOVTO_PREPAGO_ACTUACIONES FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE DATE_PROCESS >= '20091231' and DATE_PROCESS <= '20100131'
GO

TRUNCATE TABLE HECHOS_MOVTO_PREPAGO_ACTUACIONES
GO

EXEC sp_rename 'HECHOS_MOVTO_PREPAGO_ACTUACIONES', 'HECHOS_MOVTO_PREPAGO_ACTUACIONES_Empty'
GO

EXEC sp_rename 'new_HECHOS_MOVTO_PREPAGO_ACTUACIONES', 'HECHOS_MOVTO_PREPAGO_ACTUACIONES'
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 12:45:23

you might have to keep an eye of constraints existing on main table for the last suggestion. If it has any, make sure you script them out beforehand and recreate them after running the above script.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alejo46
Posting Yak Master

157 Posts

Posted - 2012-03-29 : 14:24:35

OK, THANKS FOR YOUR HELP the scrpt gives me a shed light and taking into account the constraint is crucial, but i'm intended is to get rid of the records where DATE_PROCESS >= '20091231' and DATE_PROCESS <= '20100131', so in the script ive got to change the comparison operator right ?
and the script would be:
SELECT * INTO new_HECHOS_MOVTO_PREPAGO_ACTUACIONES FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE DATE_PROCESS <= '20091231' and DATE_PROCESS >= '20100131'

2nd.How do you identify the table has constraints? sp_help table tells me:
constraint_type|constraint_name|delete_action|update_action|status_enabled|status_for_replication|constraint_keys
DEFAULT on column FEC_ACTUALIZACION|DF_HECHOS_MOVTO_PREPAGO_ACTUACIONES_FEC_ACTUALIZACION_1|(n/a)|(n/a)|(n/a)|(n/a)|(getdate())

3rd. you script them out using the tool in SSMS (SqlSErver Manager Studio, right ?
4th. what do you mean recreate them (scripts) - executing ?
5th. About Index as a critical part the DBA has to do something or is not necessary to recreate them ?

No foreign keys reference this table.

No views with schema binding reference this table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 15:31:05

have a look at sys.sysconstraints for getting details on constraints
you can script them out by expanding object explorer-> object-> constraints and choosing option script object as from sql management studio
you need to recreate them after recreating the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources