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


Delete


Delete

Author
Message
nari.koud
nari.koud
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 569
I have TWO TABLES ABC and XYZ ..and we are doing some logic to Insert millions of records into XYZ from ABC and after Inserted from ABC With @MINID AND @MAXID Range,All records which are inserted into XYZ should be deleted in ABC ...


for ex :@MIN= 1 and @MAX -100000

Note:Not The Table or Whole data ,records 1-100000 should be deleted and ABC may have 500000000 records

Here is the Querey i Wrore for deleteing

DELETE FROM ABC
WHERE ORD_ID IN (Select ORD_ID FROM XYZ
Where ID BETWEEN @MinID AND @MAXID )

THIS Worked excellent and MY LEAD SAID ..he need to have Better querey to improve the performance ...can Any one PLease help me to write a Querey with good PErformance i can tell you what scenario he explained me

This is not exact querey ..im explaing the general scenario ..blablahh




DELETE From ABC a Where EXITS (a.O_ID BEtween @MINID AND @MAXID)
AND SELECT (Order_ID from XYZ x where x.o_ID =a.ID)


something these kind ...

can you please help me in writing the querey to improve the performance


Thanks
Regards
Jim McLeod
Jim McLeod
Right there with Babe
Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)

Group: General Forum Members
Points: 791 Visits: 1121
Delete performance is dependent on two factors: how quickly you can identify the rows to be deleted, and how quickly you can delete the row. Make sure you're testing this on a non-Production system.

The first one is easy - just change the "DELETE FROM..." to SELECT * FROM...". If this returns pretty quickly, then your bottleneck won't be in the selecting phase.

The deleting phase does a bit more work. Each row must be marked as deleted, and the previous version of the row must be written to the transaction log in case of a rollback. The problem with deleting many rows at once is the amount of locking that will occur during the delete. This may lock the entire table and block other users.

Instead of deleting 100,000 rows at once, consider deleting smaller batches. Larger batches increase the chance of the entire table being locked, so a small batch of 5,000-10,000 rows might show much better performance. Transactions are shorter, so users are locked for smaller amounts of times, and the transaction log can be cleared (backed up in Full recovery, automatically truncated in Simple) after each batch.

Note that delete triggers, or any foreign key relationships that need to be checked/cascaded could also cause performance issues if not well indexed.

The most important thing is to test though. If you let it run for 4 hours, it may take another 4 hours to rollback!
Derrick Smith
Derrick Smith
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 715
Some good points posted already, but I just wanted to add one more

Sometimes it's a good idea to drop all indexes (except the one you're sorting on of course), delete, and then recreate them. This saves the engine from having to update every single index every time it deletes a row.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63698 Visits: 19115
I'd agree with both points above. USe batches, and if you can test, try dropping indexes.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
nari.koud
nari.koud
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 569
Thanks For the quick responses .....
DELETE FROM ABC
FROM ABC a
WHERE EXISTS ( SELECT ID FROM XYX x WHERE x._ID = a.ID)
AND Order_ID BETWEEN @MinID AND @MAXID



Using INNERJOIN:
DELETE FROM ABC
From ABC a
INNER JOIN Staging..XYZ x
ON a.Id = x.id
WHERE x.ID BETWEEN @MinID AND @MAXID

This is what i went with ...and he said Perfect ....



Thanks Again
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