• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Performance of update statement versus comparison function

 
Ranch Hand
Posts: 413
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In my application I have the following scenario
I have to issue an update statement for a row in a single table

There are two possibilities I am considering but I am not sure as which one is better performance wise

1. Write a function that checks what all fields of the form are changed by the user and issuing the update statement conditionally for values changed but this is adding overhread of comparison functions .Though update statment is always issued but its set parameters can vary.

2. Update the single row with all current values in form whether changed or not changed by user by issuing a single update statement

So in other words which update statement is better perfomrance wise
update table abc set a = "b" (overhead of comparison function)
or
update table abc set a = "b",b = "c",c = "d"
[ November 10, 2004: Message edited by: Gaurav Chikara ]
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd suspect that option 2 isn't significantly slower (if at all). As it is significantly less complex, I'd use it.
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There are several things to consider. First, the fact that you're making an update statement (regardless of number of parameters) is going to take the majority of the time:
  • Build SQL statement (small)
  • Send statement to database over network (large)
  • Parse SQL (small)
  • Perform disk I/O to do update (large)
  • Return number of rows affected (small)

  • Compared to the network and disk I/O, the difference between sending five parameters versus ten won't be significant. The same goes for comparing ten simple attribute values for changes.

    Also, if you use a PreparedStatement, your JDBC driver may cache and reuse it, saving the database from having to parse it each time with a new set of parameters.

    And as Ilja has pointed out, the simplest method has advantages just by being simple.
     
    Author and all-around good cowpoke
    Posts: 13078
    6
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Seems to me you really need to detect the special case where nothing has been changed. If all you need are string equals comparisons, that is way way faster than any possible database operation.
    Bill
     
    Gaurav Chikara
    Ranch Hand
    Posts: 413
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks a lot for your expert views
    It made my day
     
    Ilja Preuss
    author
    Posts: 14112
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Originally posted by David Harkness:
    Parse SQL (small)



    Actually, as I think about it, the database also needs to "compile" the statement, to decide on how to execute it etc., and the time needed for this *can* be significant (not sure for a simple update statement).

    That could be another argument for using the same statement (as a PreparedStatement) for all updates and only changing the parameters, because in this case the the database can cache and reuse the compiled statement (notice that I'm no expert, so I might be missing something, or using terminology strangely...).

    If you have to do many updates in a row (pun not intended), you could even use a batched update, which can significantly reduce the communication overhead with the database (I once cut the time in half for updating some hundred rows, just by using a batched update).

    Hope this helps...
     
    Ilja Preuss
    author
    Posts: 14112
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    BTW - most importantly, whatever you try, measure the impact.

    You might also want to try http://www.p6spy.com/ for finding your database bottlenecks.
    reply
      Bookmark Topic Watch Topic
    • New Topic