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)
 querys comparison to delete faster in batch mode
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 06/23/2013 :  22:52:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/24/2013 :  00:23:22  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/24/2013 :  00:42:56  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/24/2013 :  01:06:12  Show Profile  Reply with Quote
@Visakh, OP wants query in MSSQL 2000...
ROW_NUMBER() OVER() will not available in MSSQL 2K

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/24/2013 :  01:12:43  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/24/2013 01:13:46
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/24/2013 :  03:01:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 06/24/2013 :  16:15:08  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/25/2013 :  00:56:13  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/26/2013 :  11:39:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 06/26/2013 :  17:44:20  Show Profile  Reply with Quote
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
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 06/26/2013 :  18:16:03  Show Profile  Reply with Quote
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 ?


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/27/2013 :  01:05:57  Show Profile  Reply with Quote
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
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 06/28/2013 :  06:17:22  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/28/2013 :  06:26:45  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/29/2013 :  04:35:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 06/29/2013 :  16:22:53  Show Profile  Reply with Quote
OK THANKS, just one more question please, the loop WHILE 1 = 1 what exactly means ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/30/2013 :  04:43:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Continue forever, but do at least one loop.


N 56°04'39.26"
E 12°55'05.63"
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.12 seconds. Powered By: Snitz Forums 2000