• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

optimization / right architecture

 
Lidia Cyc
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my method has to check if a row already exists in db and in case it does - update it, or in case it does not - insert it.

the db table is unique indexed .

the mission sounds very simple, but i thought about 3 possible ways of doing this, and i'm not sure wich one will be the fastest + the best architecture.


way 1:
to run "select..." request, and then check if resultset.next().
if true - update, else - insert.

way 2:
to run "insert...". if the unique index does not exist, it will work
fine. if it exists, the sqlexception will be thrown. in the catch
block i check if exception.getMessage() includes a string "duplicate
entry" (i don't remember the message text right now...) if it does
include it - i run "update..".

way 3:
first of all i run "delete.." query, and then "insert..".

thanks in advance for your comments.
 
Frederico Melo
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should use the select.. and then insert or update approach. The reason is simple: it's not a good design throwing exceptions to check business rule, and exceptions throwing is expensive. So, the exceptions handling approach is out.
The delete approach doesn't seems a good design too, there is a much higher cost for the database to delete and than create a new record than to select and update.

regards

Fred
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65216
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Cyc Lid",

We're pleased to have you here with us in the JSP forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender
 
Mike Houston
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer a fourth alternative:

Update the record first. If the update count==0, then do the insert. This has the advantage in cases where you are mostly updating things (just one SQL statement).

Edit: Maybe you meant this to be the 3rd alternative .
[ May 21, 2004: Message edited by: Mike Houston ]
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A few things to think about:
* since generally you're going to do more than one db operation, you need to think about locking, unless you know from the way the application is written that there won't be any concurrency problems
* the most efficient approach might depend on whether you expect mostly inserts, mostly updates, or about the same of each
* consider doing this in a stored procedure; that will insure you never do more than one database round-trip (and the cost of each round-trip will be a big part of the overall cost).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic