Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete Expand / Collapse
Author
Message
Posted Thursday, September 9, 2010 3:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:27 AM
Points: 16, Visits: 293


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
Post #983493
Posted Thursday, September 9, 2010 5:52 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:03 PM
Points: 683, Visits: 1,110
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!
Post #983521
Posted Friday, September 10, 2010 11:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 575, Visits: 692
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.
Post #984010
Posted Friday, September 10, 2010 11:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
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
Post #984015
Posted Friday, September 10, 2010 2:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:27 AM
Points: 16, Visits: 293
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
Post #984139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse