Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: best way to delete rows

    hi,

    wats the best way to delete rows from a table with over 500,000,000 records.

    I have a table lets say tbl_transactions, now I want to delete all rows with TransactionDate< '01 dec 2007'...

    I tried the delete statement with the where clause but the query was executing even after 10 hrs... then I tried to insert the valid rows into a different table but I got a TempDB error..

    can anyone please tell the best and fastest way of doing this ..

    thanks a lot..

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    By far the most efficient method would be to switch data from partition(s) in this table and then drop them. That's assuming you have a partition scheme set up. If you don't then you should definitely consider it for a table with 500 million rows. Especially if large delete operations are something you do often.

    What proportion of the data is being deleted? If you don't have a partition scheme and the data to be deleted is more than say 40% then it may be easier to SELECT INTO a new table then drop the old table.

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    This is one of the ways we use to delete old records and not filling the transaction log

    Code:
    CREATE PROCEDURE dbo.usp_DeleteFrom_MyTable
    (
    	@LastFullMonthsToKeep TINYINT = 18
    )
    AS
    
    -- Avoid unwanted resultsets back to client
    SET NOCOUNT ON
    
    -- Make sure user supplied parameter has valid value
    IF @LastFullMonthsToKeep IS NULL
    	SET @LastFullMonthsToKeep = 18
    
    IF @LastFullMonthsToKeep < 18
    	BEGIN
    		RAISERROR('It is not allowed to use less than 24 months to keep.', 16, 1)
    		RETURN -100
    	END
    
    -- Initialize calculations
    DECLARE	@ToDate DATETIME,
    	@WorkDate DATETIME,
    	@Msg VARCHAR(200),
    	@Time DATETIME,
    	@Records INT,
    	@Avg INT
    
    -- Get earliest date to keep
    SET	@ToDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - @LastFullMonthsToKeep, '19000101')
    
    -- Print debug information
    SET	@Msg = '*** Delete all records daily dated before ' + CONVERT(CHAR(10), @ToDate, 120)
    RAISERROR(@Msg, 10, 1) WITH NOWAIT
    
    -- Set the looping date for truncation
    SET	@Msg = '    ' + CONVERT(CHAR(8), GETDATE(), 108) + ' - Starting'
    RAISERROR('', 10, 1) WITH NOWAIT
    RAISERROR(@Msg, 10, 1) WITH NOWAIT
    
    -- Set initial work date
    SELECT	@WorkDate = MIN(MyDate)
    FROM	MyTable
    
    SET	@WorkDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @WorkDate), '19000101')
    
    -- Do the truncation daily
    WHILE @WorkDate < @ToDate
    	BEGIN 
    		SELECT	@WorkDate = DATEADD(DAY, 1, @WorkDate),
    			@Time = GETDATE(),
    			@Msg = '    ' + CONVERT(CHAR(8), @Time, 108) + ' - Now deleting records dated before ' + CONVERT(CHAR(10), @WorkDate, 120)
    
    		RAISERROR(@Msg, 10, 1) WITH NOWAIT
    
    		DELETE
    		FROM	MyTable
    		WHERE	MyDate < @WorkDate
    
    		SELECT	@Records = @@ROWCOUNT,
    			@Avg = DATEDIFF(MILLISECOND, @Time, GETDATE()),
    			@Avg = ROUND(1000.0 * @Records / CASE WHEN @Avg = 0 THEN 1 ELSE @Avg END, 0),
    			@Msg = '               ' + CAST(@Records AS VARCHAR(12)) + ' records deleted (' + CAST(@Avg AS VARCHAR(12)) + ' records per second)'
    
    		RAISERROR(@Msg, 10, 1) WITH NOWAIT
    	END
    
    -- Finished
    SET	@Msg = '*** ' + CONVERT(CHAR(8), GETDATE(), 108) + ' - Finished'
    
    RAISERROR('', 10, 1) WITH NOWAIT
    RAISERROR(@Msg, 10, 1) WITH NOWAIT
    
    RETURN 0

  4. #4
    Join Date
    Feb 2009
    Posts
    3
    Quote Originally Posted by dportas
    By far the most efficient method would be to switch data from partition(s) in this table and then drop them. That's assuming you have a partition scheme set up. If you don't then you should definitely consider it for a table with 500 million rows. Especially if large delete operations are something you do often.

    no there is no partition scheme for this table.. could u tell me a good online resource for doing the partitioning... I did some initial research on this.

    for my requirements I can partition the table based on transaction date.. but the view cannot be altered manually each time a new set of transactiondate transactions are entered into the table...

    how do I make the view dynamic..i.e. so that I dont hv to add another table in the union all clause...

    thanks...

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Partitioned views are a legacy feature from 2000. 2005/2008 Enterprise Edition can partition tables with no UNION view required. If you are still using 2000 then you should certainly consider upgrading.

    Creating Partitioned Tables and Indexes

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    Quote Originally Posted by dportas
    Partitioned views are a legacy feature from 2000. 2005/2008 Enterprise Edition can partition tables with no UNION view required. If you are still using 2000 then you should certainly consider upgrading.

    Creating Partitioned Tables and Indexes
    Iam using 2000 and upgrade will not be possible anytime soon..is there a work around I can do..

    thanks..

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    bcp out the data you want, truncate the table, bcp the data back in.

    Take a backup first.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Also, you may want to drop the indexes before bcp'ing back in, and use batches, i.e. -b10000

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by PMASchmed
    bcp out the data you want, truncate the table, bcp the data back in.

    Take a backup first.

    we have a winner!!!

    Johnny, tell 'em what they've won
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Do I make it to the showcase round ? or do I have to spin that big wheel thingy first ?

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by PMASchmed
    Do I make it to the showcase round ? or do I have to spin that big wheel thingy first ?
    Gotta try for the 25 GRAND!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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