Post reply

Update Large Table

  • January 6, 2012 at 2:34 pm

    #249293

    I have a program that dumps data into a table nightly. After the program is done. I end up with a 80+ Million Row table.

    I then have a SQL Process that adds three columns to this table and then needs to update these three columns with some hour offsets, but I only have to really update a specific amount of rows in this table based on what day of the month it is.

    After the update is done I then Insert these specific rows in to another table.

    My issue is that this takes a pretty long time to update these rows in this large(80+ million row) table.

    Does anybody have an idea to optimize this more?

    My one thought would be: create a temp table and just insert the "specific" rows I need and then add the columns and update them in this temp table. Then, insert the rows from this temp table in to the final table.... Not sure if this is faster or if there are better ways.

    After I get these rows in the final table. This 80+ million row table gets truncated for the next night.

    Please help!

  • January 6, 2012 at 4:13 pm

    #1429903

    Temp table method could work. Does your update statement have an adequate where clause?

    Is there an index in place that fits the conditions of your queries?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 7, 2012 at 2:24 am

    #1429942

    If temp table means #table then be aware of their life (limited to session) in the database.

    Also, I don’t think it would be any better than regular tables except they won’t be part of database design.

  • January 7, 2012 at 2:29 am

    #1429943

    How much control do you have in this process ?

    Adding columns such as you have described , really does sound like a design flaw to me.

    If it were me , i would re-address the whole thing from the ground up, anything else will , more than likely, cause you more pain in the future.


    Clear Sky SQL
    My Blog[/url]

  • January 7, 2012 at 3:35 am

    #1429948

    Can we have the update query and its query plan to check if it can be improved.

    Regards,
    Raj

    http://Strictlysql.blogspot.com

  • January 7, 2012 at 10:58 am

    #1430040

    I would also have the table defined WITH the three columnns that are being added rather than create it/load it /add columns.

    Make them non-nullable with default values if possible and you should find the updates much faster. At present the stiorage engine will be forced to keep moving ows and splitting pages as the row lengths will be increasing as you run the updates.

    Mike John

  • January 8, 2012 at 3:24 am

    #1430124

    Mike John (1/7/2012)

    Make them non-nullable with default values if possible and you should find the updates much faster.

    But adding the columns after the table is created would take much longer.

    I think you need to look at the process as a whole rather than just the update. It's likely a lot of the overhead can be processed prior to or during the the data import

  • January 8, 2012 at 3:30 am

    #1430125

    Dbloc (1/6/2012)

    ...After the update is done I then Insert these specific rows in to another table.

    Why not do this in one step? Select the rows you want from the 80M, calculating the values for the three new columns in the output.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 8, 2012 at 2:54 pm

    #1430226

    ChrisM@home (1/8/2012)

    Dbloc (1/6/2012)

    ...After the update is done I then Insert these specific rows in to another table.

    Why not do this in one step? Select the rows you want from the 80M, calculating the values for the three new columns in the output.

    Heh... I've finally learned to read the whole thread before jumping in. 😀 Glad you beat me to it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 9, 2012 at 10:28 am

    #1430665

    Doing this in one step will be the route I take. I didn't even think about that. Thanks for the help!!

  • January 9, 2012 at 10:42 am

    #1430676

    Jeff Moden (1/8/2012)

    ChrisM@home (1/8/2012)

    Dbloc (1/6/2012)

    ...After the update is done I then Insert these specific rows in to another table.

    Why not do this in one step? Select the rows you want from the 80M, calculating the values for the three new columns in the output.

    Heh... I've finally learned to read the whole thread before jumping in. 😀 Glad you beat me to it.

    LOL! I'm sure you were thinking - why do this in situ unless the update includes the values of rows which are excluded from the select?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply