aspose file tools*
The moose likes JDBC and the fly likes Design question - update + insert versus delete + insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Design question - update + insert versus delete + insert" Watch "Design question - update + insert versus delete + insert" New topic
Author

Design question - update + insert versus delete + insert

Shweta Parekh
Greenhorn

Joined: Oct 06, 2004
Posts: 5
Hi,

I have a design dilemma here.
I have a 2D array coming from my front end.
array[0][0] = "abc"
....
array[0][2] = "bcd"
....
array[5][0] = "xyz"
....
array[5][2] = "xxx"

My database table workSpec structure looks like this (there is no limit on the number of rows and columns)
item Row Col Value
1230 0 abc
1230 1 abc
1230 2 bcd
1231 0 das
1231 1 das
1231 2 ada
123........
1235 0 xyz
1235 1 www
1235 2 xxx

I run a executeUpdate query to update this table with 6 rows and 3 columns.

Now if the user on the front end adds rows and columns to the array ie. the array size is now 10 X 12 (10 rows, 12 columns). My question is in this situation would it be a better design to -
1. delete the existing rows from the table and do inserts for all 10 X 12 values
2. do update for 6 X 3 grid and do inserts for the remaining 4 rows and remaining 8 columns.

Any ideas? Any help on this is highly appreciated.

Thanks,
Shweta
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
What database are you using? It's not good idea to delete the rows and insert again. The better approach will be to update existing rows and insert new rows as this will be performant when compared to delete+insert.
Shweta Parekh
Greenhorn

Joined: Oct 06, 2004
Posts: 5
Thanks for your response.
We are using Oracle DB.

-Shweta
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
And before updating, check if the rows have actually changed, rather than doing a needless update.


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
 
Don't get me started about those stupid light bulbs.
 
subject: Design question - update + insert versus delete + insert