Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Red face Unanswered: deleting one row at a time Procedure?

    Hi
    I need to delete huge old rows from a table
    could u guys have any best idea.
    ordinary deletion is quite risky and time consume.
    I am thinking to use delete Procedure
    delete and select one row at a time.
    i am new in sybase so
    any response is appreciate.
    thanks

  2. #2
    Join Date
    Dec 2004
    Posts
    25
    Dont think that there is anything faster than the ordinary delete command.

    You can do it one row at a time with the use of cursors which will probably be much slower than the 'ordinary' delete.

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You could do some sort of select-into trick, depending on how many rows you are deleting. For example, if you are deleting 90% of the rows in a table it will probably be faster to select into a new table those 10%, drop the old table, then rename your new one to be the old table name.
    Thanks,

    Matt

  4. #4
    Join Date
    Jan 2005
    Posts
    8

    Thanks

    Quote Originally Posted by xmwolverine
    Dont think that there is anything faster than the ordinary delete command.
    thanks
    You can do it one row at a time with the use of cursors which will probably be much slower than the 'ordinary' delete.
    Thanks a lot ,
    if u could write me some sample of crusors?will be more helpfull for me,
    deletion will be
    the old date such as <=20030731
    Cheers
    rhino

  5. #5
    Join Date
    Jan 2005
    Posts
    8

    Thanks

    Quote Originally Posted by MattR
    You could do some sort of select-into trick, depending on how many rows you are deleting. For example, if you are deleting 90% of the rows in a table it will probably be faster to select into a new table those 10%, drop the old table, then rename your new one to be the old table name.
    it will be quite bit risky in my inviornment
    anyway thanks for ur comment.
    cheers!
    rhino

  6. #6
    Join Date
    Dec 2004
    Posts
    25
    Quote Originally Posted by MattR
    You could do some sort of select-into trick, depending on how many rows you are deleting. For example, if you are deleting 90% of the rows in a table it will probably be faster to select into a new table those 10%, drop the old table, then rename your new one to be the old table name.
    Actually this could be really fast...
    select into tempdb..<table> (or #table) where all rows that dont need to be deleted.
    truncate original table <== that's the fastest delete you can possibly ask for.
    insert into table from the tempdb..table

    this will make sure that the object id of the original table does not change and you dont need to recompile the SPs that refer to this table.

  7. #7
    Join Date
    Aug 2004
    Posts
    42
    Following is a code structure that we use for just this purpose. The idea is to use a cursor and batch the one row deletes into transaction of an appropriate size. The bigger the transaction size the faster it goes but the more like you are to have contention. We usually keep the size around 50 and don't have much trouble running soimething like this during high volumn OLTP periods. Of course, like most things, it depends: if you have lots of rows to delete you can have a slightly higher transaction size as the rows are more likely to be more closely packed (same number of rows likely to be on smaller number of pages). If you have fewer rows to delete you might even go all the way to a transaciton size of one. Anyway, here it is:

    set nocount on
    go
    exec sp_spaceused MyTable
    go
    declare cursorMyTable cursor for
    select SSN
    from MyTable
    where RevokeDate = "8/13/2000"
    for update
    go
    declare
    @rows int
    ,@Limit int
    ,@total int
    ,@SSN char(9)
    ,@TranSize int
    ,@RorC char(1)
    ,@Debug char(1)
    ,@TimerA datetime
    ,@TimerB datetime
    ,@Seconds float
    ,@total_c char(10)

    select
    @TranSize = 100
    ,@Limit = 0
    ,@RorC = "C"
    ,@Debug = "Y"

    select @TimerA = getdate()
    open cursorMyTable
    select @rows = 0, @total = 0
    while (1 = 1)
    begin
    fetch cursorMyTable into @SSN

    if (@@sqlstatus in (1,2)) break

    select @rows = @rows + 1

    if (@rows % @TranSize = 1 or @TranSize = 1)
    begin
    begin transaction
    end

    delete from MyTable where current of cursorMyTable

    if (@rows % @TranSize = 0 or @TranSize = 1)
    begin
    if (@RorC = 'C')
    commit transaction
    else
    rollback transaction

    if (@Debug = 'Y')
    begin
    select @TimerB = getdate()
    select @Seconds = datediff (ms, @TimerA, @TimerB) * 0.001
    select @TimerA = @TimerB
    print "MyTable %1! (%2!)", @rows, @Seconds
    end
    end

    if (@rows = @Limit) break
    end

    if (@rows % @TranSize != 0)
    begin
    if (@RorC = 'C')
    commit transaction
    else
    rollback transaction

    if (@Debug = 'Y')
    begin
    select @TimerB = getdate()
    select @Seconds = datediff (ms, @TimerA, @TimerB) * 0.001
    select @TimerA = @TimerB
    print "MyTable %1! (%2!)", @rows, @Seconds
    end
    end

    close cursorMyTable
    deallocate cursor cursorMyTable

    select @total = @rows
    select @total_c = str (@total, 10, 0)
    print 'MyTable %1! rows deleted', @total_c
    go

    Tad Hawkins

  8. #8
    Join Date
    Jan 2005
    Posts
    8
    Quote Originally Posted by Tad Hawkins
    Following is a code structure that we use for just this purpose. The idea is to use a cursor and batch the one row deletes into transaction of an appropriate size. The bigger the transaction size the faster it goes but the more like you are to have contention. We usually keep the size around 50 and don't have much trouble running soimething like this during high volumn OLTP periods. Of course, like most things, it depends: if you have lots of rows to delete you can have a slightly higher transaction size as the rows are more likely to be more closely packed (same number of rows likely to be on smaller number of pages). If you have fewer rows to delete you might even go all the way to a transaciton size of one. Anyway, here it is:

    set nocount on
    go
    exec sp_spaceused MyTable
    go
    declare cursorMyTable cursor for
    select SSN
    from MyTable
    where RevokeDate = "8/13/2000"
    for update
    go
    declare
    @rows int
    ,@Limit int
    ,@total int
    ,@SSN char(9)
    ,@TranSize int
    ,@RorC char(1)
    ,@Debug char(1)
    ,@TimerA datetime
    ,@TimerB datetime
    ,@Seconds float
    ,@total_c char(10)

    select
    @TranSize = 100
    ,@Limit = 0
    ,@RorC = "C"
    ,@Debug = "Y"

    select @TimerA = getdate()
    open cursorMyTable
    select @rows = 0, @total = 0
    while (1 = 1)
    begin
    fetch cursorMyTable into @SSN

    if (@@sqlstatus in (1,2)) break

    select @rows = @rows + 1

    if (@rows % @TranSize = 1 or @TranSize = 1)
    begin
    begin transaction
    end

    delete from MyTable where current of cursorMyTable

    if (@rows % @TranSize = 0 or @TranSize = 1)
    begin
    if (@RorC = 'C')
    commit transaction
    else
    rollback transaction

    if (@Debug = 'Y')
    begin
    select @TimerB = getdate()
    select @Seconds = datediff (ms, @TimerA, @TimerB) * 0.001
    select @TimerA = @TimerB
    print "MyTable %1! (%2!)", @rows, @Seconds
    end
    end

    if (@rows = @Limit) break
    end

    if (@rows % @TranSize != 0)
    begin
    if (@RorC = 'C')
    commit transaction
    else
    rollback transaction

    if (@Debug = 'Y')
    begin
    select @TimerB = getdate()
    select @Seconds = datediff (ms, @TimerA, @TimerB) * 0.001
    select @TimerA = @TimerB
    print "MyTable %1! (%2!)", @rows, @Seconds
    end
    end

    close cursorMyTable
    deallocate cursor cursorMyTable

    select @total = @rows
    select @total_c = str (@total, 10, 0)
    print 'MyTable %1! rows deleted', @total_c
    go

    Tad Hawkins
    Thanks Tad,
    for your good hints and sample
    i am intrested to modyfy and use in my invironment
    could u explain about the SSN, @rows....and others declare
    what it points and
    does it necesarry to declare whole parameter
    sorry for begineer question.
    cheers
    Rhino

  9. #9
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Mass Deletes

    Quote Originally Posted by rhino123
    Hi
    I need to delete huge old rows from a table
    could u guys have any best idea.
    ordinary deletion is quite risky and time consume.
    I am thinking to use delete Procedure
    delete and select one row at a time.
    i am new in sybase so
    any response is appreciate.
    thanks
    1 Sybase is quite excellent in 'risky' environments, etc. You just have to manage the size of transactions based on other users, no of rows per page, no of pages affected (by each iteration), etc. RTFM for details.

    2 One row at a time may take years and is quite unnecessary; Sybase is a "set-oriented" engine and one-row-at-a-time is a "procedural" approach.

    3 In the case where you would be deleting close to 50% or more, xmWolverine's suggestion is the best.

    4 In both his and my suggestion, drop as many indexes as you can for the duration.

    5 Don't forget to update statistics when you are finished, but drop/create indexes is the best.

    6 Here's an example of what I use for "mass" deletes in place.

    Code:
    set nocount on       -- suppress rows affected messages
    set rowcount 1000    -- or 500 or 100 or whatever
                         -- limits the "transaction" size
    declare @error int,
        @rows int,
        @deleted  int
    
    select getdate()
    
    while (1=1)          -- forever
        begin
        delete mytable
            where mydate <= "whatever"
        select @rows = @@rowcount,
            @error = @@error
        if (@rows = 0)
            break        -- terminate the loop
        if (@error != 0)
            print "Error %1", @error
        select @deleted = @deleted + @rows
        end
    
    print "Rows Deleted %1", @deleted
    select getdate()
    7 Put it in a script file and run at night when OLTP usage is low, or over several nights, etc.

    8 If you
    Code:
    set rowcount 1
    then you will get cursor-type processing without the cursor overhaeds.
    Last edited by DerekA; 01-28-05 at 07:08.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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