Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Performance of update statement versus comparison function  RSS feed

 
Gaurav Chikara
Ranch Hand
Posts: 413
  • Mark post as helpful
  • send pies
  • 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 ]
 
Ilja Preuss
author
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • 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.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • 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.
     
    William Brogden
    Author and all-around good cowpoke
    Rancher
    Posts: 13078
    6
    • Mark post as helpful
    • send pies
    • 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
    • Quote
    • Report post to moderator
    Thanks a lot for your expert views
    It made my day
     
    Ilja Preuss
    author
    Sheriff
    Posts: 14112
    • Mark post as helpful
    • send pies
    • 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
    Sheriff
    Posts: 14112
    • Mark post as helpful
    • send pies
    • 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.
     
    Don't get me started about those stupid light bulbs.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!