Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Best Practice for updating single field in N rows

    Hi

    I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.
    Following applies to disconnected environment.

    In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records.

    So business request:
    User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table?

    There are, to my mind, three ways of dealing with the latter senario -
    1) Client calls the server n times editing a record at the time.
    2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.
    3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server.

    3 - I think would only be an option if n was a very large number and\ or having the changes immediately reflected in the data is not priority.
    1 - would create a chatty app and presumably put the most load on the server.

    So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations\ gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions.
    OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely?

    Thanks in advance
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    generally, without knowing the specifics, I would say #2.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I avoid denormalizing data at all costs. In my personal experience, the code becomes unwieldy and inflexible, and the savings on network traffic is at least partially offset by the extra processing required to create and then parse the CSV string at either end of the transaction.

    So, go with #1 and you will have a more adaptable application. I seriously doubt that your users will be typing in data changes faster than the network or database can process them. What kind of maximum load are you expecting?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Thrasy (I googled ThrasymackyGreekFella the other day - didn't enlighten me much but then I think I was expected to read an article for a full 10+ minutes - he seemed terribly learned though) & Blindman

    Quote Originally Posted by blindman
    So, go with #1 and you will have a more adaptable application. I seriously doubt that your users will be typing in data changes faster than the network or database can process them. What kind of maximum load are you expecting?
    Ok -
    defo more adaptable.
    Nope - they certainly won't be typing in that fast. I can fire off async anyway.

    Max load? Not sure what you mean - there would defo be hot spots during the day.
    The specifics (I deliberately left them out as I am interested in general rules anyway) are for an attendance system (posted about this some weeks ago).
    As such, teachers will typically sit down at the beginning of the lesson and mark classes of on average ~20 students. 3000 students with about 50% timetables on average so ~75 batches of 20 updates 8 times a day. Pretty trivial load I suspect from your perspective.

    Quote Originally Posted by Thrasymachus
    generally, without knowing the specifics, I would say #2.
    That was my thought too but, as implied in the post, I'm open to other thoughts.

    I must admit, #1 is more appealing to code - I anticipated a lot of booing and hissing from DBAs about such an approach though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will get a lot of booing and hissing with #2. And I predict you will also get a lot of coding and debugging as well.

    Yeah, I had to google Thrasy's monicker when I first saw it too. But I didn't like what came up, so I created me own definition:

    Thrasymachus - A small bipedal dinosour of the Cenozoic age. Characterized by a long, plated tail, short forelegs, and fluffy ears. Transitional in form between Tyranosaurus Rex and the modern Pekinese.

    I think that is much more interesting to visualize than some dried up Greek philosopher.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Yeah, I had to google Thrasy's monicker when I first saw it too. But I didn't like what came up, so I created me own definition:

    Thrasymachus - A small bipedal dinosour of the Cenozoic age. Characterized by a long, plated tail, short forelegs, and fluffy ears. Transitional in form between Tyranosaurus Rex and the modern Pekinese.

    I think that is much more interesting to visualize than some dried up Greek philosopher.
    LMAO - defo - I think an image needs putting together in Potoshop for the little fella ready for the Return of the Avatars.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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