Storing arrays in databases

Question

What is the most efficient way to store large arrays (10000x100) in a database, say, hsqldb? I need to do this for a certain math program that I'm writing in java. Please help. The whole array will be retrieved and stored often (not so much individual elements). Also, some meta-data about the array needs to be stored about the array.

Answer 1

Great question.

Unless you want to translate your arrays into a set of normalized tables, which it sounds like you don't, you might want to contemplate serialization.

Serialization is a fancy word for turning objects into some format that you can save to disk or a database. The two major formats for serialization are binary and XML, and I'm betting that Java has some support for it.

Depending on what data types you're using, you should be able to turn your array into XML or binary and then save that to a single field in the database. You could get started with this technique in Java by checking out http://java.sun.com/developer/technicalArticles/Programming/serialization/. I know that it's built into .NET.

Hope that this helps. Let me know if I can give you any more direction.

Answer 2

How about storing the data as a BLOB and using Java to decode the BLOB into an actual Java array? It would be much more efficient for storing and retrieving the whole array in one gulp, but would be terrible for twiddling individual elements.