Nibble Delete Problem - 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)

Nibble Delete Problem

Author

Topic

Aandrew
Starting Member

5 Posts

Posted - 2007-10-09 : 10:52:52

I have a stored procedure which Im using to do clear down some data with as little impact as possible,

CREATE PROCEDURE sp_NibbleDelete
@sqlstring varchar(1000)
AS
SET DEADLOCK_PRIORITY LOW

DECLARE @s2 NVARCHAR(1000)

SELECT @s2 = CAST(@sqlstring AS nvarchar(1000))

DECLARE @count int
SET @count = 2000

SET ROWCOUNT 2000

WHILE @count = 2000 BEGIN
EXECUTE sp_executesql @s2
SELECT @count = @@ROWCOUNT
WAITFOR DELAY '000:00:00.200'
END

SET ROWCOUNT 0

GO

This is an example of a SQL string that is passed to the nibble delete sp to execute ..

Delete From PayPosUK
Where Exists
(
Select NULL From RecHeadUK
Where ( RecHeadUK.[CompNo] = PayPosUK.[CompNo]
AND RecHeadUK.[BranchNo] = PayPosUK.[BranchNo]
AND RecHeadUK.[TerminalNo] = PayPosUK.[TerminalNo]
AND RecHeadUK.[TransNo] = PayPosUK.[TransNo]
And RecHeadUK.[Date_] < '20070711'
And RecHeadUK.[Confirmed] = 1 )
)

The change Im thinking of putting in to improve performance is below ..

Delete From PayPosUK
Where Exists
(
Select TOP 2000 NULL From RecHeadUK
Where ( RecHeadUK.[CompNo] = PayPosUK.[CompNo]
AND RecHeadUK.[BranchNo] = PayPosUK.[BranchNo]
AND RecHeadUK.[TerminalNo] = PayPosUK.[TerminalNo]
AND RecHeadUK.[TransNo] = PayPosUK.[TransNo]
And RecHeadUK.[Date_] < '20070711'
And RecHeadUK.[Confirmed] = 1 )
)

I have two questions really ..

- Would the SQL still do the same job after putting this TOP 2000 in the query??
- Secondly .. since Im using ROWCOUNT .. is the TOP 2000 even necessary???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-09 : 11:06:45

rowcount is the same thing as top N except it's also applied on other DML statements.
just remeber to set it back to 0 after you're done

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 11:08:35

I don't think you understand EXISTS...

SET ROWCOUNT 5000

WHILE 1 = 1
	BEGIN
		DELETE		y
		FROM		PayPosUK AS y
		INNER JOIN	RecHeadUK AS x ON x.CompNo = y.CompNo
					AND x.[BranchNo] = y.[BranchNo]
					AND x.[TerminalNo] = y.[TerminalNo]
					AND x.[TransNo] = y.[TransNo]
		WHERE		x.[Date_] < '20070711'
				AND x.[Confirmed] = 1

		IF @@ROWCOUNT = 0
			BREAK
	END

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 11:08:49

1) Superfluous
2) No

EXISTS stops the moment it finds a matching record, the TOP nnn won't help (but, worst case, may actually force the inner query to find that many, and then find one match from that!)

Your performance issue is probably more to do with finding the rows to delete in the first place, and possibly the time to create a suitable query plan.

Worth sticking "dbo." in front of the table names to improve the chance of the plan being cached. (Worth checking that the plan IS being cached, because if it isn't that's the first thing to fix! e.g. I presume you are re-asking with the same '20070711' const, but if not that needs to be parameterized)

Our best performing bulk-deletes tend to get ALL the PKs into a Temp Table first, and then JOIN [using clustered PK] off those to get the actual rows to delete.

We release for 5 seconds, not just a few milliseconds, and we adjust the SET ROWCOUNT batch size according to how long the delete took. If the delete was fast we add a small percentage on, if it was slow we half the number. (Subject to some hardwired Min and Max batch sizes)

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Bulk+Delete+of+Records

Kristen

Aandrew
Starting Member

5 Posts

Posted - 2007-10-09 : 11:11:08

quote:
Originally posted by Peso

I don't think you understand EXISTS...

E 12°55'05.25"
N 56°04'39.16"

Im not a sql developer .. Ive used it a bit and so got volunteered for this job :(

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 11:14:40

"just remeber to set it back to 0 after you're done"

We put it INSIDE the loop for that reason - belt+extra+braces!!

I would actually Pre/Post-pend it to the @sqlstring so that nothing outside of that could be, in effect, restricted.

Kristen

Aandrew
Starting Member

5 Posts

Posted - 2007-10-09 : 11:31:36

Thanks for your help all, lots to look into.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources