querys comparison to delete faster in batch mode

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)

querys comparison to delete faster in batch mode

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2013-06-23 : 22:52:52

Good evening
ive got 3 simple queries that do the same task (deletes) and I'd like
to if there is a more efficient one that deletes faster, or enhanced one of them:

here are:

--1st delete
set rowcount 1000
while exists(select * from HECHOS_MOVTO_PREPAGO_ACTUACIONES
where HOR_PROCESO >= '20101231'
and HOR_PROCESO <= '20110131')
delete
from HECHOS_MOVTO_PREPAGO_ACTUACIONES
where HOR_PROCESO >= '20101231'
and HOR_PROCESO <= '20110131'

2nd

WHILE EXISTS ( SELECT * FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES WHERE HOR_PROCESO >= '20101231'
and HOR_PROCESO <= '20110131')
BEGIN
SET ROWCOUNT 1000
DELETE HECHOS_MOVTO_PREPAGO_ACTUACIONES -- whats the differenve sithnthos sentence ?
WHERE HOR_PROCESO >= '20101231'
and HOR_PROCESO <= '20110131'
SET ROWCOUNT 0
ENd

--
3rd
SET ROWCOUNT 1000;
DELETE hmpa
FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES hmpa
WHERE HOR_PROCESO >= '20101231'
and HOR_PROCESO <= '20110131'
obviously this query seems to be the winner but i want this query to be automatic
Thanks in advanced

Cordialmente

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-24 : 00:23:22

That should be like this..... (No need of alias name in the case of single table)
--3rd
SET ROWCOUNT 1000;
DELETE FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20101231' and HOR_PROCESO <= '20110131'

>>this query to be automatic ?
In which way the query has to be automatic process?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 00:42:56

The way you've written the deletes it just removed 1000 "random" rows from the table. Is this the real intention? if your attempt is to remove oldest 1000 rows satisfying the condition, it should be something like


DELETE t
FROM (SELECT ROW_NUMBER() OVER (ORDER BY PKcol) AS Seq
      FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES 
      WHERE HOR_PROCESO >= '20101231' and HOR_PROCESO <= '20110131' 
     )t
WHERE Seq <=1000

PKCol is the primary key of the table (or unqiue valued column)
If you want to make it automatic you need to explain us the basis for selecting the date range ('20101231' and '20110131')

I'm assuming its always from year-3 dec to year -2 jan
that being case you can rewrite it as below to take date values automatically. If not, please let us know your rules for determining dates


DELETE t
FROM (SELECT ROW_NUMBER() OVER (ORDER BY PKcol) AS Seq
      FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES 
      WHERE HOR_PROCESO >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-3,'19001231') and HOR_PROCESO <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,'19000131') 
     )t
WHERE Seq <=1000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-24 : 01:06:12

@Visakh, OP wants query in MSSQL 2000...
ROW_NUMBER() OVER() will not available in MSSQL 2K

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 01:12:43

quote:
Originally posted by bandi

@Visakh, OP wants query in MSSQL 2000...
ROW_NUMBER() OVER() will not available in MSSQL 2K

--
Chandu

I know that
I was explaining on his requirement
by the way he has written it just removes 1000 random rows

Anyways if he want a 2000 complaint solution with guaranteed order
here it is


SELECT PKCol
INTO #Temp
      FROM (
SELECT TOP 1000 PKCol 
FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES 
      WHERE HOR_PROCESO >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-3,'19001231') and HOR_PROCESO <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,'19000131') 
ORDER BY PKCOl
)t
 
CREATE CLUSTERED INDEX IDX_Clust ON #Temp(PKCol)

DELETE t
FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES t
WHERE EXISTS (SELECT 1 FROM #Temp WHERE PKCol = t.PKCol)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-24 : 03:01:03

I believe the intention is to delete ALL rows satisfying the criteria and doing this in batches to lessen the log file strain.

SET ROWCOUNT 1000

WHILE @@ROWCOUNT > 0
	DELETE
	FROM	dbo.HECHOS_MOVTO_PREPAGO_ACTUACIONES
	WHERE	HOR_PROCESO >= '20101231'
		AND HOR_PROCESO <= '20110131'

SET ROWCOUNT 0

N 56°04'39.26"
E 12°55'05.63"

alejo46
Posting Yak Master

157 Posts

Posted - 2013-06-24 : 16:15:08

Thanks very much to all of you for your help and support
As Swepeso said the intention is to delete ALL rows satisfying the criteria and doing this in batches to lessen the log file strainm and all those choices are very interesting and i think the last one is affordable for me however according to your answers a couple of questions came up to me

1.ROW_NUMBER() OVER() is available in MSSQL 2008 ?
2.On the query that creates the #Temp table, this Temp table is dropped automatically once the query is finiished ?
3. Would you explain to me why In the date functions the 19001231 parameter is given ?

Thanls in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 00:56:13

quote:
Originally posted by alejo46

Thanks very much to all of you for your help and support
As Swepeso said the intention is to delete ALL rows satisfying the criteria and doing this in batches to lessen the log file strainm and all those choices are very interesting and i think the last one is affordable for me however according to your answers a couple of questions came up to me

1.ROW_NUMBER() OVER() is available in MSSQL 2008 ?
Its available in SQL 2008
2.On the query that creates the #Temp table, this Temp table is dropped automatically once the query is finiished ?
Nope, you've to explicitly drop it using DROP TABLE statement. If you're doing this inside a procedure it will automatically get dropped unpon executing the procedure
3. Would you explain to me why In the date functions the 19001231 parameter is given ?
19000101 is the base date for sql server. I set the day month part as 1231 as you wanted to start from 31st Dec in your query
The full expression DATEADD(yy,DATEDIFF(yy,0,GETDATE())-3,'19001231')causes date to automatically set to 31st dec 3 years before given any date value

Thanls in advanced

Responses inline above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-26 : 11:39:00

You don't want to use ROW_NUMBER() in this case.
Why add unneccesary complexity to a simple solution?

N 56°04'39.26"
E 12°55'05.63"

alejo46
Posting Yak Master

157 Posts

Posted - 2013-06-26 : 17:44:20

OK you're right, its just a matter of knowledge
But I.ve got a problem
I run the query and just working fine, but i canceled the execution to increase the rowcount to 200000, after that i run the query again is not longer working, it yields the message:The command(s) completed successfully.

SET ROWCOUNT 200000
WHILE @@ROWCOUNT > 0
DELETE
FROM dbo.HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'

SET ROWCOUNT 0

Thanks in advanced

alejo46
Posting Yak Master

157 Posts

Posted - 2013-06-26 : 18:16:03

i think i found the solution becuse of the system variable @@rowcount is set to 0
A provisional solution was creating a var and and the vauie of select count, so this is:

declare @count int
SELECT @Count = COUNT(hor_proceso)
FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'
SET ROWCOUNT 200000
WHILE @count > 0
DELETE
FROM dbo.HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'
SET ROWCOUNT 0
One more question the instruction SET ROWCOUNT 0 is used for ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 01:05:57

quote:
Originally posted by alejo46

i think i found the solution becuse of the system variable @@rowcount is set to 0
A provisional solution was creating a var and and the vauie of select count, so this is:

declare @count int
SELECT @Count = COUNT(hor_proceso)
FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'
SET ROWCOUNT 200000
WHILE @count > 0
DELETE
FROM dbo.HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'
SET ROWCOUNT 0
One more question the instruction SET ROWCOUNT 0 is used for ?


SET ROWCOUNT 0

Is used to reset the ROWCOUNT setting. 0 is default value which will make sure the current ROWCOUNT set value is reset and any further statements will return you the full resultset (rather than just the first x rows as defined by ROWCOUNT)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alejo46
Posting Yak Master

157 Posts

Posted - 2013-06-28 : 06:17:22

Good morning
I just run the query as i mentioned but turn into an infinite loop and when the value for the var @count was 0 the while loop cycle kept running and i hat to cancel it

Id appreciate your help in advanced
eclare @count int
SELECT @Count = COUNT(hor_proceso)
FROM HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'
--print @count
SET ROWCOUNT 200000
WHILE @count > 0
DELETE
FROM dbo.HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'
SET ROWCOUNT 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 06:26:45

it is indeed an infinite loop as you're not decrementing the value if @count anywhere. So it will always be having value returned by select query and loop will go on ieterating infinitely

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-29 : 04:35:21

[code]SET ROWCOUNT 200000

WHILE 1 = 1
BEGIN
DELETE
FROM dbo.HECHOS_MOVTO_PREPAGO_ACTUACIONES
WHERE HOR_PROCESO >= '20110331'
AND HOR_PROCESO <= '20110430'

IF @@ROWCOUNT < 200000
BREAK
END

SET ROWCOUNT 0[/code]

N 56°04'39.26"
E 12°55'05.63"

alejo46
Posting Yak Master

157 Posts

Posted - 2013-06-29 : 16:22:53

OK THANKS, just one more question please, the loop WHILE 1 = 1 what exactly means ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-30 : 04:43:08

Continue forever, but do at least one loop.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources