Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Unanswered: Deleting millions of record help

    Hello Guy,

    Need some help here i have a query to delete millions of records. I whant to delete in batches of a 1000. My Select join statement will return millions of records so this takes alot of time how to i select a 1000 records delete everything that his not in those record and loop and not select the same records again

    Here is what i have :


    DECLARE @i INT

    WHILE (1=1)
    BEGIN

    BEGIN TRAN
    DELETE TOP(1000) FROM dbo.ABC123
    WHERE SUBSTRING(dumbdumb,1,8) NOT IN
    (
    SELECT SUBSTRING(a.dumbdumb,1,8)
    FROM dbo.ABC123 a
    JOIN dbo.TEST b
    ON SUBSTRING(a.dumbdumb,1,8) = SUBSTRING(b.dumbdumb,1,8)
    )
    COMMIT TRAN

    SET @i = @@ROWCOUNT
    IF @i = 0 BREAK;

    PRINT '# of Records deleted in batch ---- ' + CAST(@i AS VARCHAR(10))

    END
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have a primary key on your table? "SUBSTRING(dumbdumb, 1, 8)" is not an efficient way to reference your data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Yes i have a primary key lets give it the name Dummer. Unfortunetly the reference between both table ABC123 and TEST his dumbdumb. the primary key his on ABC123. the other does not have anything and i cannot modify it to add a key.


    By the way the column and table names are not as i am posting just changing the names here for confidentiality.
    Last edited by hillcat; 07-23-12 at 17:24.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This may run faster:
    Code:
    delete	ABC
    from	dbo.ABC
    	inner join --TargetSubset
    		(select	top 1000
    			Dummer
    		from	dbo.ABC123
    			inner join dbo.TEST on LEFT(ABC123.dumbdumb,8) = LEFT(TEST.dumbdumb, 8)) as TargetSubset
    		on ABC.Dummer = TargetSubset.Dummer
    
    while	@@ROWCOUNT > 0
    	delete	ABC
    	from	dbo.ABC
    		inner join --TargetSubset
    			(select	top 1000
    				Dummer
    			from	dbo.ABC123
    				inner join dbo.TEST on LEFT(ABC123.dumbdumb,8) = LEFT(TEST.dumbdumb, 8)) as TargetSubset
    			on ABC.Dummer = TargetSubset.Dummer
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •