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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    What do you want to bet that there's still a cursor or two in that.

    I had a process a while back that deleted 2 million rows, did aggregations over 150 million rows then inserted another 2 million. Total duration - 45 minutes.

    Ok, that was a monster of a server, but still....

    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
  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    GilaMonster (1/18/2010)


    I have the feeling this is less of a case of it not working on SQL 2008 than it simply isn't supported on SQL 2008. So it might run on SQL 2008, but there would be no vendor support in the case of problems.

    Errh, wrong thread Gail :hehe:.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Ninja's_RGR'us (1/18/2010)


    Errh, wrong thread Gail :hehe:.

    I hate it when SSC does that. Answering 2 threads at the same time stuff often gets posted to wrong thread. Now where did the answer I wrote for here go.....

    Fixed.

    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
  • itsmeman

    Right there with Babe

    Points: 784

    No cursors were used. 150,000+ records took an hour to change, yup, bad, bad, bad. Those 150,000 could have had many more charges associated with the records. Both tables that needed to be edited have 2 million plus rows. The hardware is older and is used extensively.

    I'm sure I could have created a temp table slapped a non-clustered or clustered index and maybe I would have received better performance on those 150,000 records, but the reality is the two tables that were being modified were the culprit (all statistics were up to date).

    The shop here has a bunch of very old outdated methods from its original culture that was hired, I don't know, 8 years ago, and everyone still uses those methods to do maintenance and manage data at customers sites.

    I know venting is stupid, but I'm going to do it anyway. The process to improve anything around here due to the nature of the business is unwarranted, and I just don't have the ambition anymore to deal with all the hoops to jump through to get things more efficient.

    Plus, I don't know what I'm doing in the first place. At least I'm making an effort to improve.

  • Lynn Pettis

    SSC Guru

    Points: 442116

    itsmeman (1/18/2010)


    No cursors were used. 150,000+ records took an hour to change, yup, bad, bad, bad. Those 150,000 could have had many more charges associated with the records. Both tables that needed to be edited have 2 million plus rows. The hardware is older and is used extensively.

    I'm sure I could have created a temp table slapped a non-clustered or clustered index and maybe I would have received better performance on those 150,000 records, but the reality is the two tables that were being modified were the culprit (all statistics were up to date).

    The shop here has a bunch of very old outdated methods from its original culture that was hired, I don't know, 8 years ago, and everyone still uses those methods to do maintenance and manage data at customers sites.

    I know venting is stupid, but I'm going to do it anyway. The process to improve anything around here due to the nature of the business is unwarranted, and I just don't have the ambition anymore to deal with all the hoops to jump through to get things more efficient.

    Plus, I don't know what I'm doing in the first place. At least I'm making an effort to improve.

    Venting is good. If you really want to improve keep reading the posts on SCC, Wether forums or articles. Look at the problems, develop solutions and see how they compare to what others provide. If you still want to identify how to improve processes where you work, post what you can and see what transpires. Even if it is difficult to try and change things where you work, you actually have two choices. First is to just start looking for another job. The second is to just keep working and try to slowly implement changes that will eventually help improve things where you work.

  • itsmeman

    Right there with Babe

    Points: 784

    Very good advice. I love this site and the books offered for free have helped my knowledge tremendously. Dissecting SQL Execution Plans.... Perfect for tuning SQL.

  • David.Poole

    SSC Guru

    Points: 75083

    And I thought I was hard done by because a 1.5 million record update took 15 seconds!

  • SQLRNNR

    SSC Guru

    Points: 281210

    itsmeman (1/19/2010)


    Very good advice. I love this site and the books offered for free have helped my knowledge tremendously. Dissecting SQL Execution Plans.... Perfect for tuning SQL.

    Can we get those items that Gail and Lynn requested?

    Execution Plans (actual)

    DDL

    DML

    It will really improve the ability to provide you with some tuning help.

    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

  • Ion Freeman

    Say Hey Kid

    Points: 681

    itsmeman (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?

    I believe that SQL 2000 had a technology called 'Foreign Keys' to enforce something it called 'referential integrity.' Check constraints, triggers and even data types were available back then.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Ion Freeman (6/1/2010)


    itsmeman (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?

    I believe that SQL 2000 had a technology called 'Foreign Keys' to enforce something it called 'referential integrity.' Check constraints, triggers and even data types were available back then.

    Back then? :w00t:

    Let me break some news for you. FK "technology" can be traced back in time much closer to the big bang than SS2000. You are correct, there is something -we crazy people- call "referential integrity" 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 10 posts - 16 through 25 (of 25 total)

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