SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Large table cleanup with minimal locks


Large table cleanup with minimal locks

Author
Message
Edward.Polley 76944
Edward.Polley 76944
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 263
Comments posted to this topic are about the item Large table cleanup with minimal locks
app1dak
app1dak
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
Is the IN Clause in the WHERE predicate faster than using the WHERE EXISTS clause? I've heard that large IN Clauses tend to be slow performers. I work mostly with DB2 on iSeries though. Just starting using SQL Server for some projects.
jay.dunk
jay.dunk
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 30
would a table variable not be better than a # table?
Kalman
Kalman
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 27
Hi

I think There is much better solution (very simple to write and should work faster) - this by using CTE . You can select TOP X rows in the CTE and then delete it , this will be done in a loop .

If You want I can give an example .

Sincerely
Kalman
jay.dunk
jay.dunk
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 30
@kalman --- that wont get round the contention, if you use the method shown in the article you can throttle the deletes to avoid locking for too long a time, whilst only perform one select per large batch to get the ids you want.

I do something similar on a 24/7 db, 365 days a year with all files on a single raid 5 partition!?! I use the same approach but with a table variable and I process over 1.5 million deletes per day on a rolling basis.

The cte will keep repeating teh select for the delete and therefore likely cause locking for the duration of the select/delete.
okbangas
okbangas
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3543 Visits: 1387
A few thoughts: Why are you concerned about locking of the temp tables, they're only accessible from your own session.

Furthermore, option maxdop 8 does allow for parallelism yes, but it is generally not recommended to span NUMA nodes, which maxdop 8 may do on dual quad servers. I do think maxdop 8 can potentially cause more performance issues than maxdop 4 in some cases, and this SHOULD be configured correectly as a server option by the dba.



Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
heino.zunzer
heino.zunzer
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 104
Well, several issues with this, but the foremost issue is with using
where ... in (select...)


Please don't. You already have your IDs in a table and a Primary Key (hopefully clustered) on SalesID in your application table. So just join the tables!

DELETE 
YourDB.dbo.SalesZIP
from
YourDB.dbo.SalesZIP orig
inner join #InnerTemp dead on orig.SalesID = dead.SalesID


jack 63767
jack 63767
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 44
hi,

if you want to use a join in this case, be careful and define a unique primary key constraint on your temp table(heap!). Joins on heaps are potential inefficient operations.

ciao,

jack
jay.dunk
jay.dunk
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 30
Still nailing down best fit on t-sql, but if you dont need any additional info from the 2nd table why join especially if not one to one? Surely just checking for a match will be quicker than actioning a join? hash join against merge join in query plan i would have thought?
Chris Taylor
Chris Taylor
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 1918
jay.dunk (3/6/2012)

I do something similar on a 24/7 db, 365 days a year with all files on a single raid 5 partition!?! I use the same approach but with a table variable and I process over 1.5 million deletes per day on a rolling basis.


Be careful with using a table variable with a large amount of rows because a table variable is optimized for one row, by SQL Server i.e. it assumes 1 row will be returned therefore possibly producing a sub-optimal execution plan.

I agree with the comments regarding using the inner join as opposed to WHERE...IN (SELECT...)

_________________________________________________________________________________

SQLGeordie

Web:- Jarrin Consultancy
Blog:- www.chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search