It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes optimization / right architecture Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "optimization / right architecture" Watch "optimization / right architecture" New topic

optimization / right architecture

Lidia Cyc

Joined: Aug 05, 2003
Posts: 25
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
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

Joined: Mar 15, 2004
Posts: 24
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.



Frederico Melo<br />--------------<br />Software Architect<br />Sun Certified Enterprise Architect for J2EE<br />IBM Rational Unified Process Specialist
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63536

"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.

Forum Bartender

[Asking smart questions] [About Bear] [Books by Bear]
Mike Houston

Joined: May 21, 2004
Posts: 1
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

Joined: Feb 12, 2003
Posts: 156
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).
I agree. Here's the link:
subject: optimization / right architecture
It's not a secret anymore!