What is the best way to delete millions of records in TSQL?

Question

I have the following structre table

Table1       Table2        Table3
--------------------------------
 sId          sId           sId
 name          x              y
  x1          x2             x3

I want to delete all records from table1 that do not have a corresponding record in table3 based on sId, and if sId is present in table2, then do not delete the record from table1.Tech is about 20.15 and 10 million records in table1, table2 and table3 resp . - I did something like this

Delete Top (3000000)
        From Table1 A
        Left Join Table2 B
        on A.Name ='XYZ' and
           B.sId = A.sId
        Left Join Table3 C
        on A.Name = 'XYZ' and
           C.sId = A.sId

((I added an index to sId, but not to the name.)) But it takes a long time to delete entries. Is there a better way to delete millions of records? Thanks in advance.

Answer 1

5000 10000 , 40% , , , /bcp, /bcp

while @@rowcount > 0
begin
Delete Top (5000)
        From Table1 A
        Left Join Table2 B
        on A.Name ='XYZ' and
           B.sId = A.sId
        Left Join Table3 C
        on A.Name = 'XYZ' and
           C.sId = A.sId
end

, , ,

CREATE TABLE #test(id INT)

INSERT #test VALUES(1)
INSERT #test VALUES(1)
INSERT #test VALUES(1)
INSERT #test VALUES(1)
INSERT #test VALUES(1)
INSERT #test VALUES(1)
INSERT #test VALUES(1)

WHILE @@rowcount > 0
BEGIN 
DELETE TOP (2) FROM #test

END 

Answer 2

- , . , , , .

, . .

Answer 3

concurrency .

- : http://sqlblogcasts.com/blogs/simons/archive/2009/05/22/DELETE-TOP-x-rows-avoiding-a-table-scan.aspx

Answer 4

? , .

Answer 5

DELETE, , SELECT, , . , , . , DELETE .

, , . , , .

Answer 6

, , Table1_good. : Drop Table1 Table1_good 1

, .

Answer 7

, AFTER DELETE 3, 1. , .

Answer 8

I would create a temporary table by creating a selement and populating the temp table, adding indexes to the temp table and deleting from my table from which I want to delete records. Then I would reset my temporary table when I do something like this

Select * into #temp from mytable 

Where is blah blah (or your request)

// add contraindications if you want

I just ran the primary key in the temp table

then i would say

Delete mytable where is the primary key (select myPrimarykey from #temp)