Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Most efficient way to update an integer

    This is a probably a complete noob question (and appropriately, I am a noob).

    There is a database with millions of records. Lets say these records represent people. One of the columns is "total_crackers_eaten" and it is an integer and represent the total number of crackers eaten to date for that person.

    Every hour I get an information about how many crackers many of the people have eaten in the past hour.

    What is the most efficient method to update the total_crackers_eaten value in my database for all the people every hour?

    I was wondering if there was a "INSERT & SUM" type of command, one that would automatically increament the current value by some supplied quantity. Or is the only way to do this to read the current value, add the new crackers that were eaten, and store the new value back for that person?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What is the most efficient method to update the total_crackers_eaten value in my database for all the people every hour?
    COMPUTED values should NEVER be stored in the DB!
    COMPUTED value is generated only at data presentation time.

    crackers_eaten should be stored as consumption occurs & then SUM(crackers_eaten) at display time
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2011
    Posts
    2

    Efficent?

    Is that efficient? I don't need to keep track of each individual hourly count of crackers eaten, but I do need to know how many crackers each person has eaten over the past 2 years.

    What is the reasoning behind the "NEVER" rule?

    Thanks for the reply.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is that efficient?
    Answer may depend upon definition of efficient.
    >I don't need to keep track of each individual hourly count of crackers eaten,
    Current requirements may change in the future or future requirements may be expanded.
    >but I do need to know how many crackers each person has eaten over the past 2 years.
    Is this a sliding 2 year reporting period?
    EXACTLY how would COMPUTE & STORE results to always have 100% correct results.

    >What is the reasoning behind the "NEVER" rule?
    always produce 100% correct results; regardless of when query is issued.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2011
    Location
    Bengaluru, India
    Posts
    4

    Efficient ways to update integers

    I've had to optimize this kind of a function in the past. Some of my solutions may get me a lot of flak ... but what the heck ;-)

    1. This operation may best be done out of the database. In my case when I had to do it very often with large numbers incoming (like billions), it was faster to aggregate outside using a script in C or perl. Any technique can further be optimized by #2 below.

    2. You can save the current count and when new data comes in, stage it in a table, update the count and then load it into the master table.

    3. In my book, there are no NEVERs. If your business needs the data fast, you just got to find a way to provide it - precache, save it in the db, etc.

    ---------------------------------------------------
    Bharat
    DB Guru
    Boltell Travel Guides, Audio Guides, Audio Visual and Mobile Tour Guides

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by rkwillms View Post
    I was wondering if there was a "INSERT & SUM" type of command, one that would automatically increament the current value by some supplied quantity.
    That's a regular update:

    Code:
    UPDATE the_table
       SET total_crackers_eaten = total_crackers_eaten + 350
    WHERE person_id = 42;

Tags for this Thread

Posting Permissions

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