-1

I need to generate a table (matrix) in Java, with the ultimate aim of exporting it to a SQL database. I'm using Object[][], since the table columns contain different data types. However, during the table generation I need to constantly perform operations of the form "find and update the row whose first two elements are x and y", and I'm hesitating to do that with double loops as it would take a lot of time (the table will be very long). Do you have any suggestions? Would it be more efficient to directly work in SQL through JDBC to perform the updates, instead of doing all the work in Java and export it afterwards?

splinter123
  • 1,183
  • 1
  • 14
  • 34
  • I think your question is a bit too generic. You will get very generic responses which make assumptions on your actual needs, frequency of updates etc. First of all, do you actually have a performance problem, or is this purely academic? – fish Feb 24 '14 at 11:09
  • There is no need for details in my opinion. The answer below provides exactly the information I needed to both of my questions, by stating that memory processing is faster and by providing a very useful technique for the operations I described. I haven't coded it yet, since I wanted to ensure efficiency before writing hundreds of lines of code (and maybe realize later I could have done it in ten!) – splinter123 Feb 25 '14 at 09:09
  • Well I'm glad you are happy, but things are not always that straight forward that you can say for sure that one way is more efficient than other, without knowing more about it. – fish Feb 27 '14 at 18:14

1 Answers1

0

In general, doing things in memory is a lot more faster than to do it on a database. Keep in mind that everytime you will need to update a row, the database will have to create a transaction. That means create a lock, update, release the lock, and that is really slow.

If there is a lot of processing to do, in memory is almost every time a lot faster. Attention however since it could also put too many objects in memory and cause out of memory errors.

Now, it looks like you could use a better data structure to do your calculus, that you could then serialize into the rows that you want to insert.

For exemple, since you are looking for the rows whose first two elements are x and y, you could store your elements in a Map<String,List<String>> where the key would be x_y and the list would be the rows you would like to insert at the end of processing.

you could then access your elements quite easily, and not iterate over your rows every time.

Once your whole generation is done, you just need to iterate over your Map to insert it in the database.

Arnaud Potier
  • 1,750
  • 16
  • 28