0

We have 5 Lacs records in a SQL table. Below is table structure

UserID varchar

FirstName varchar

LastName varchar

Address varchar

We need to delete 50K records in this table. We tried below approach

Approach 1: This has 50K DELETE statements which takes around 40 minutes

DELETE FROM [dbo].[Table] WHERE UserID = 'tom345'

DELETE FROM [dbo].[Table] WHERE UserID = 'john45'

and so on until 50K DELETE statements

Approach 2: This contains all 50K user id in IN statement but it takes more that 40 minutes

DELETE FROM [dbo].[Table] WHERE UserID IN ( 'tom345', 'john45', .... )

Is there any other approach which takes less time comparatively?

Thank you

2

Create a temp table, put in your users and use that in your delete statement:

CREATE TABLE delete_users (user_id ... primary key);
INSERT INTO delete_users (user_id) values (...),(...)...;
DELETE FROM  [dbo].[Table] x 
WHERE EXISTS (
    SELECT 1 FROM delete_users y WHERE x.user_id = y.user_id
);
DROP TABLE delete_users;
  • This is really very quick.Thank you @Lennart. Below query perfectly worked out for me DELETE FROM [dbo].[FIRSTTABLE] WHERE EXISTS ( SELECT 1 FROM [dbo].[SECONDTABLE] ST WHERE ST.UserID = [dbo].[FIRSTTABLE].UserID ) – Sukhjeevan May 22 '20 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.