SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 DELETING IN BATCH DEGRADATION
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 03/28/2012 :  11:48:43  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/28/2012 :  11:59:22  Show Profile  Reply with Quote
why not break it up into smaller batches and do the deletion?

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

Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 03/28/2012 :  12:18:10  Show Profile  Reply with Quote
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 ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/28/2012 :  12:20:10  Show Profile  Reply with Quote
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/

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/28/2012 :  12:58:05  Show Profile  Reply with Quote
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/



Edited by - X002548 on 03/28/2012 12:58:49
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 03/28/2012 :  16:02:04  Show Profile  Reply with Quote
no, it has no foreign keys.

actuallly, from the table it has 45 miilions left to delet from 60 millions
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/28/2012 :  17:24:21  Show Profile  Reply with Quote
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/


Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 03/28/2012 :  22:56:02  Show Profile  Visit jezemine's Homepage  Reply with Quote
looks like OP doesn't want all rows deleted. only these:

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


elsasoft.org
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 03/28/2012 :  23:39:52  Show Profile  Reply with Quote
thats right, im no entendded to truncate the table, just deleting the records where DATE_PROCESS >= '20091231' and DATE_PROCESS <= '20100131'.

thanks
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/29/2012 :  10:38:39  Show Profile  Reply with Quote
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/



Edited by - X002548 on 03/29/2012 10:39:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/29/2012 :  12:45:23  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 03/29/2012 12:45:34
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 03/29/2012 :  14:24:35  Show Profile  Reply with Quote
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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/29/2012 :  15:31:05  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000