updating 1,000 records takes 5-6 minutes I would like to decrease the time that takes

  • itsmeman

    Right there with Babe

    Points: 794

    hello,

    i know this says sql 2005.. but the platform I am running on is sql 2000.

    i have a database with 1.17 million records.

    I need to make a change to 131,000 of these records.

    The best way determined to handle this change is through a cursor.

    To update 1,000 records it takes 5-6 minutes. I think this is unacceptable because to update 131,000 records it will take roughly 11 hours.

    Being new to table and performance handling, can someone direct me in the right direction to decrease the amount of time it takes to update 1,000 records?

    thanks.

    joshua

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Please post SQL 2000 questions in the SQL 2000 forums in the future.

    Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Shouldn't be any need for a cursor here. Of course, without seeing what you want to update and to what, it's hard to say for sure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Lynn Pettis

    SSC Guru

    Points: 442138

    Agree, I must, with Gail. Hard it is to help with what has been provided.

  • Sunny-138471

    Ten Centuries

    Points: 1162

    Table structure is important. 131000 is not really a big number. It would be great if you could provide the kind of indexes that exist on the tale and how the data is updated. AS of now I could give the following hints:

    1. The column based on which the update happens should be indexed

    2. The index on the column which actually gets updated can be removed tempirarily and can be created once the index is complete

    3. Any foreign key relationships on the actual column getting updated should be removed during the time of update

    Thanks

    Satish More

  • c00ler01

    Ten Centuries

    Points: 1070

    Can you describe what you are trying to do? Quite a lot of the times the cursors can be avoided...

  • itsmeman

    Right there with Babe

    Points: 794

    This issue is done for now....

    what advice would you all have to not using cursors if you

    have sensitive data that needs to be checked to maintain its integrity?

    is that a different question to post in a different section?

  • c00ler01

    Ten Centuries

    Points: 1070

    Can you give us example of checks that you are carrying out?basic checks should be enforced through CHECK,unique,null or not null,data type etc.

    Everything else I would validate through front end.but once again I don't know what you are trying to do...

  • Gail Shaw

    SSC Guru

    Points: 1004446

    joshua.aaanderson (1/5/2010)


    what advice would you all have to not using cursors if you

    have sensitive data that needs to be checked to maintain its integrity?

    Write a set-based query. That's all I can say based on the vagueness of the question. If you can get more specific someone can probably give a more detailed answer.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • badkow

    Right there with Babe

    Points: 715

    SQL Server is designed to handle data in set format. Handling data on a row by row basis requires a lot of times the use of cursors and cursors have some disadvantages associated with them(just google to read about them).

  • Lynn Pettis

    SSC Guru

    Points: 442138

    I have to agree with Gail again. If you can provide us with more specifics, table DDL (CREATE TABLE statement(s)), sample data (series of INSERT INTO statement(s)) for then table(s), expected output based on the sample data. With that we could show you how to put together a set-based approach to solving the problem.

    Using cursors shows a procedural way of thinking about the problem at a row at a time. Instead, you should be looking at what do I want to do to this column.

  • Jeff Moden

    SSC Guru

    Points: 994519

    itsmeman (1/5/2010)


    This issue is done for now....

    Two way street here... please explain how this issue is "done for now".

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Lynn Pettis

    SSC Guru

    Points: 442138

    Jeff Moden (1/17/2010)


    itsmeman (1/5/2010)


    This issue is done for now....

    Two way street here... please explain how this issue is "done for now".

    My guess, they ran the update that took 11 hours.

  • Jeff Moden

    SSC Guru

    Points: 994519

    Lynn Pettis (1/17/2010)


    Jeff Moden (1/17/2010)


    itsmeman (1/5/2010)


    This issue is done for now....

    Two way street here... please explain how this issue is "done for now".

    My guess, they ran the update that took 11 hours.

    Heh... that would be my guess, as well. 😀 Hopefully the OP will come back and confirm one way or the other but that isn't the general nature of such posts.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • itsmeman

    Right there with Babe

    Points: 794

    I rewrote the script.

    Got it down to an hour. 🙂

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    no offense but that's still slow as hell.

    I have a script that calculates the inventory per location as well as total per item for each of all 900 000 transaction in our system.

    It takes 30 seconds to run those steps :

    Copy the full table into temp table (only required columsn obviously)

    Put a clustered index

    Do the first calculation that updates 900 000 rows based per location.

    Drop the clustered index

    Build the clustered index based on different keys

    run the update for the 2nd column.

    All that takes only 25 seconds and assuming my math is still ok, that's over 4.5M lines written! Ok that's a nice server (2 cpus dual core, 16 GB of ram and san drives) but it's still a freaking heck of a lot faster than what you have right now.

    A base comparaison would be that I update 150 000 rows a second with my code while you write 36 rows per seconds. That only 4166 times faster than your code... just saying :w00t:.

Viewing 15 posts - 1 through 15 (of 25 total)

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