CODEWITHSUNDEEP
javasqlderby
Mike Warren
Mike Warren

Reputation: 3886

Which method is better for updating database?

Suppose you have a bunch of records in a table, and in another table, you analyze all those records. Take, for example, my math game database. It has two tables for all the raw data that is the records (one for each mode), and two corresponding tables that analyze the two tables of raw data (still, one for each mode). The two analysis tables have 5 columns, one for each difficulty, and the columns represent data like fastestTime, slowestTime, avgTime, percentAccuracy, etc.

Of course, there are two options for updating these two tables. The first of which is to just use a nested SQL query every time the records tables are updated. The second of which is to, since this is all happening in Java, get the data's attributes before it goes into the database, and then calculate the new fastestTime, slowestTime, avgTime, etc. via this data and the ResultSet that is the the analysis tables that we are trying to update. Which would you think would be the cheaper way to update the tables and why?

//Sorry if this seems too vague, I just don't know how to phrase the question better right now....

Upvotes: 0

Views: 121

Answers (1)

Zutty
Zutty

Reputation: 5377

If its an operation that has to be performed on the whole table, like MAX(time) or something, then definitely do it in the database as it will be faster.

I think Derby supports triggers. http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html

If it was an operation that required some complex calculations then I might be tempted to do it in Java code instead, but in that case you might do it in batch, like every n minutes.

Upvotes: 1

Related Questions