aspose file tools*
The moose likes JDBC and the fly likes optimization / right architecture Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "optimization / right architecture" Watch "optimization / right architecture" New topic
Author

optimization / right architecture

Lidia Cyc
Greenhorn

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

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.

regards

Fred


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

Joined: Jan 10, 2002
Posts: 61764
    
  67

"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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Mike Houston
Greenhorn

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: http://aspose.com/file-tools
 
subject: optimization / right architecture