Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: Update huge table in batches

    Hi,

    I have a table in sybase which has got some 48 million rows and running an update as a full is always filling up the transaction log. Can someone send me the efficient/faster batch script which would update the table in batches of say 50000 please?

    Thanks

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Repeat the following code until the number of rows updated goes down to 0. Make sure you only update the rows that need updating and not ones you've already updated.
    Code:
    set rowcount 50000
    
    update YourTable ...
    
    wait for delay '00:00:10'
    
    set rowcount 0

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Somasundaram

    Trying to update (or delete or insert) a massive number of rows in a single transaction is a very silly thing to do in a transactionally powered server. You will:
    need a transaction log size of 2.5 times the row_size being insert/update/deleted
    hold millions of locks
    essentially block all other users for the entire duration of the command
    and if the massive "transaction" fails, the rollback will be 2.5 times as long
    On a transactional server, you need to keep your transactions small. For batch update/insert/delete, you need to break up the batch into small transactions. A transaction size of 50,000 is still far too large, try 500 or 1,000.

    Delete and insert are simple:
    Code:
    SET ROWCOUNT 500
    WHILE (1=1)
        BEGIN 
        DELETE FROM table WHERE <delete_condition> 
        IF @@ROWCOUNT != 500
            BREAK
        END
    SET ROWCOUNT 0
    Updates require a tiny bit more code, it depends on how you identify the rows that require update vs the rows that have been updated (or do not require update). If you cannot figure it out from the above code, post your current update code, and table/index DDL; I will provide the exact solution. You can obfuscate table and column names.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Mike

    The technical information you have posted is incorrrect, and it must be pointed out. Cutting and pasting code from another thread does not work, because the context is lost. It is idle speculation and actually gives the seeker erroneous information, it wastes their time. Here you have pasted code from another seekers (not responders) post, which was incorrect, which was corrected; so:
    • you have posted doubly incorrect code
    • which has nothing to do with this seekers problem.
    The only thing it gives them is a massive delay in execution time, 2 hours 40 minutes in this instance. You have failed to understand the distinction between the transactional aspects of the problem and the replication aspects. It would be better if you post solutions from actual knowledge and experience, rather than idle speculation; then (a) the seeker does not waste time and (b) the info does not need to be corrected.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Derek Asirvadem
    Mike

    The technical information you have posted is incorrrect, and it must be pointed out.
    What exactly is wrong with the code Derek? At least mine does an update rather than a delete.

    Quote Originally Posted by Derek Asirvadem
    which has nothing to do with this seekers problem.
    ...
    Cutting and pasting code from another thread does not work, because the context is lost.
    In what way? The code snippet was written for the OP. It isn't a full solution simply because we don't have enough information yet. Breaking down large SQL into smaller batches has been done this way for decades Derek - you didn't certainly discover it. You appear to have simply repeated you code from this thread and haven't even bothered to change the delete into an update.
    Quote Originally Posted by Derek Asirvadem
    The only thing it gives them is a massive delay in execution time, 2 hours 40 minutes in this instance. You have failed to understand the distinction between the transactional aspects of the problem and the replication aspects.
    The OP has made no mention of replication. His issue is the transaction log filling and, we can assume, the table being locked from other processes. The delay was put in to allow other processes to continue as normal while the update is occurring. Personally I'd of had a smaller batch size but the user asked for 50000. I think it's a fair assumption that the update is a one off so I don't think it makes any difference if the update takes 3 hours as long as it completes without filling the log.

    Quote Originally Posted by Derek Asirvadem
    It would be better if you post solutions from actual knowledge and experience, rather than idle speculation; then (a) the seeker does not waste time and (b) the info does not need to be corrected.
    It is from actual knowledge - this is quite a common problem and I've been using similar code for the past 20 years. If there is a mistake then please give the details or are you just smarting from the last debate we had? Derek, you've been on this forum since April and since that point you've repeatedly told all the other db experts that they know nothing and that you're experience far outweighs all of theirs - please don't start this with me.

  6. #6
    Join Date
    May 2009
    Posts
    17
    Derek,

    Copy of table/index DDL

    CREATE TABLE dbo.WS_DAILY_PREM
    (
    local_currency varchar(3) NULL,
    prod_source_cd varchar(2) NULL,
    policy_expiry_date smalldatetime NULL,
    production_office_cd varchar(3) NULL,
    organization_cd varchar(3) NULL,
    organization_branch_cd varchar(3) NULL,
    manager_cd varchar(2) NULL,
    major_line_cd varchar(2) NULL,
    minor_line_cd varchar(2) NULL,
    class_peril_cd varchar(3) NULL,
    policy_no varchar(10) NULL,
    certificate_no varchar(9) NULL,
    insured_name varchar(90) NULL,
    coinsurance_cd varchar(2) NULL,
    coinsurance_percent smallmoney NULL,
    installment_frequency char(1) NULL,
    aistart_submission_no varchar(10) NULL,
    mars_request_no varchar(9) NULL,
    renewal_type char(1) NULL,
    current_renewal_status char(1) NULL,
    source_system_code varchar(2) NULL,
    policy_status_code varchar(2) NULL,
    policy_office_code varchar(6) NULL,
    renl_cert_no varchar(6) NULL,
    eff_date_seq_no int NULL,
    shell_policy_flag char(1) NULL,
    record_type_code char(1) NULL,
    acct_period_date smalldatetime NULL,
    producer_cd varchar(6) NULL,
    ins_name_client_no varchar(10) NULL,
    dun_bradstreet_no numeric(9,0) NULL,
    expiring_annual_premium money NULL,
    future_annual_premium money NULL,
    net_annual_premium money NULL,
    coinsurance_premium money NULL,
    o_policy_incept_date smalldatetime NULL,
    policy_incept_date smalldatetime NULL,
    mars_request_end_no smallint NULL,
    mars_request_rev_no smallint NULL,
    fos_flag char(1) NULL,
    deductible_amt money NULL,
    broker_comm_percent money NULL,
    liability_limit money NULL,
    fac_comm_percent smallmoney NULL,
    ins_reins_type_cd varchar(2) NULL,
    ri_code varchar(7) NULL,
    cancellation_reason varchar(2) NULL,
    prem_tran_obj_id tinyint NULL,
    trans_date smalldatetime NULL,
    trans_type_cd varchar(2) NULL,
    o_prem_amt money NULL,
    b_prem_amt money NULL,
    orig_curr_cd varchar(3) NULL,
    base_curr_cd varchar(3) NULL,
    policy_eff_date smalldatetime NULL,
    sequence_no varchar(2) NULL,
    userid_cd varchar(8) NULL,
    underwriter_id varchar(8) NULL,
    prdr_role_cd char(1) NULL,
    prem_eff_date smalldatetime NULL,
    timestamp smalldatetime NULL,
    insured_town varchar(45) NULL,
    insured_province varchar(10) NULL,
    product_cd varchar(6) NULL,
    type_of_business varchar(2) NULL,
    days_delay int NULL,
    booking_band varchar(15) NULL,
    coin_type varchar(50) NULL,
    reason varchar(50) NULL,
    ref_group_cd varchar(6) NULL,
    coverage_txt varchar(30) NULL,
    easy_line_cd varchar(4) NULL,
    local_line_cd varchar(5) NULL,
    autobook_flag char(1) NULL,
    market_segment_cd varchar(5) NULL,
    broker_comm_amount money NULL,
    policy_fee money NULL,
    prem_exp_date smalldatetime NULL,
    sect_obj_no int NULL,
    risk_obj_no int NULL,
    cov_obj_no int NULL,
    section_cd varchar(2) NULL,
    risk_cd varchar(5) NULL,
    coverage_cd varchar(5) NULL,
    layer_no int NULL,
    rein_dist_no int NULL,
    rein_obj_no int NULL,
    ri_prem_pct smallmoney NULL,
    wip_no varchar(10) NULL,
    wip_iteration_no varchar(2) NULL,
    ri_comm_amount money NULL,
    country_cd varchar(3) NULL,
    rep_iss_off_cd varchar(3) NULL,
    product_name varchar(30) NULL,
    ramo_ministero_cd varchar(3) NULL,
    ramo_cd varchar(2) NULL,
    service_bureau_cd char(1) NULL,
    ret_prem_reason_cd varchar(15) NULL,
    late_prem_reason_cd varchar(15) NULL,
    producer_name varchar(30) NULL,
    total_liab_amt money NULL,
    occ_lmt_amt money NULL,
    producer_group_cd varchar(6) NULL,
    maj_client_cd varchar(9) NULL,
    install_no varchar(3) NULL,
    pol_record_type_cd char(1) NULL,
    pol_chnge_eff_date smalldatetime NULL,
    pol_chnge_exp_date smalldatetime NULL,
    nxt_inst_billing_date smalldatetime NULL,
    nxt_inst_due_date smalldatetime NULL,
    coin_indicator_cd varchar(2) NULL,
    Uri_local_cd varchar(6) NULL,
    Fac_rein_dist_no int NULL,
    Delinquent_cd char(1) NULL,
    Reinsurance_ref varchar(15) NULL,
    reason_cancel_cd varchar(3) NULL,
    instal_option_cd char(1) NULL,
    year_month_num int NOT NULL,
    RI_comm_percent smallmoney DEFAULT 0 NOT NULL
    )
    LOCK ALLPAGES
    go
    GRANT SELECT ON dbo.WS_DAILY_PREM TO public
    go
    IF OBJECT_ID('dbo.WS_DAILY_PREM') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.WS_DAILY_PREM >>>'
    ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.WS_DAILY_PREM >>>'
    go
    CREATE INDEX ws_prem_idx1
    ON dbo.WS_DAILY_PREM(year_month_num,prem_exp_date)
    go
    IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.WS_DAILY_PREM') AND name='ws_prem_idx1')
    PRINT '<<< CREATED INDEX dbo.WS_DAILY_PREM.ws_prem_idx1 >>>'
    ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.WS_DAILY_PREM.ws_prem_idx1 >>>'
    go
    CREATE INDEX ws_prem_idx2
    ON dbo.WS_DAILY_PREM(acct_period_date,autobook_flag,o rganization_cd)
    go
    IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.WS_DAILY_PREM') AND name='ws_prem_idx2')
    PRINT '<<< CREATED INDEX dbo.WS_DAILY_PREM.ws_prem_idx2 >>>'
    ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.WS_DAILY_PREM.ws_prem_idx2 >>>'
    go
    CREATE INDEX ws_prem_idx3
    ON dbo.WS_DAILY_PREM(timestamp,source_system_code)
    go
    IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.WS_DAILY_PREM') AND name='ws_prem_idx3')
    PRINT '<<< CREATED INDEX dbo.WS_DAILY_PREM.ws_prem_idx3 >>>'
    ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.WS_DAILY_PREM.ws_prem_idx3 >>>'
    go
    CREATE INDEX ws_prem_idx4
    ON dbo.WS_DAILY_PREM(policy_no,certificate_no,renl_ce rt_no,eff_date_seq_no,organization_cd,ins_reins_ty pe_cd,prdr_role_cd)
    go
    IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.WS_DAILY_PREM') AND name='ws_prem_idx4')
    PRINT '<<< CREATED INDEX dbo.WS_DAILY_PREM.ws_prem_idx4 >>>'
    ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.WS_DAILY_PREM.ws_prem_idx4 >>>'
    go
    CREATE INDEX ws_prem_idx5
    ON dbo.WS_DAILY_PREM(policy_office_code,product_cd,se ction_cd,risk_cd,coverage_cd,local_line_cd)
    go
    IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.WS_DAILY_PREM') AND name='ws_prem_idx5')
    PRINT '<<< CREATED INDEX dbo.WS_DAILY_PREM.ws_prem_idx5 >>>'
    ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.WS_DAILY_PREM.ws_prem_idx5 >>>'
    go


    Table has got 26350263 rows

    Update code we are using:-

    declare @rowcount int

    while 1=1

    begin

    set rowcount 10000

    UPDATE WS_DAILY_PREM SET insured_name = 'AAAA'
    where insured_name <> 'AAAA'

    select @rowcount = @@rowcount

    if @rowcount = 0

    break

    else

    continue

    end

    go

    Issues:-

    After running some 24 hrs failed with 'ASE ran out of locks' understandable. Locks has been configured as 900000(increased from 100000)

    Takes huge amount of time to update runs about 48 hrs still not complete so thought dropping the indexes and doing the update and then rebuild(but then rebuild could have an issue)

    Please advise best/exact solution.

    Thanks and Regards
    Somasundaram

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Sadly Derek has been banned for a few weeks due to various outbursts like the one above. Hopefully he'll of calmed down when he returns. If you still want to communicate with him then you could try clicking on his name and sending him an email.

    None of your indexes are useful to the original update so you'd end up with repeated table scans after a while. You could try adding an index on just this field and rerunning your original code. I'd suggest putting in a small delay in each loop and selecting out the time so you can see the progress. This will also show if the updates are speeding up or slowing down on each iteration. The "lock allpages" also looks a bit suspect to me but I don't know your system.

    If you're updating all the rows then you could also experiment with dropping the column and adding it again with a default value of AAAA. You'd really want to test this first on a much smaller copy of the table. Then try it on your development database with a full copy of the original table. Then finally do it on the live data. Also remember that the fields position in the table will change. Something like:
    Code:
    alter table dbo.WS_DAILY_PREM
    drop column insured_name
    
    alter table dbo.WS_DAILY_PREM
    add insured_name varchar(20)
    default "AAAA"

Posting Permissions

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