[java] Java ResultSet memory usage

Are you saying that PreparedStatements are slow(er than ResultSet) ? I don't think so...

And ease of usage? Take a look at the @Update annotation, and the tutorials on how to use it - might give you a lot of ideas.

As for the question itself, the "performance" of ResultSets are implementation specific; Each driver implements the ResultSet interface in its own way, so it wouldn't surprise to see different performances from different drivers. Don't know if writting your own is worth the time/hassle for the irrelevant performance gain... The guys who write JDBC drivers already squeeze out the most out of their classes [grin]

Quote:

Original post by Son of Cain Are you saying that PreparedStatements are slow(er than ResultSet) ? I don't think so...

Well, the database is on a different server machine, i'd say that does matter slightly, and reading data from an existing resultset would still be faster as you will do that anyway, but now with one you already have instead of one you get from jdbc. I haven't tried preparedstatements though, because i thought it didn't really matter that much but i just read the contrary on several websites, thanks for that.

Quote:

And ease of usage? Take a look at the @Update annotation, and the tutorials on how to use it - might give you a lot of ideas.

The @Update and @Select annotations look interesting and i might use them when i re-write the current database code. What i meant by easy to use is that at the moment using methods like ResultSet.setInt() works the easiest for me as i can just take the resultset and make the updates which can then be read instantly by other threads. No statements or whatever, just one simple function call. Of course i can re-write this to use preparedstatements, and i might, but doing nothing is still easier for me ;).

Quote:

Don't know if writting your own is worth the time/hassle for the irrelevant performance gain...

I don't think so, i'd rather write some simple code for caching the data myself. As i said my biggest problem isn't really performance (yet) but memory usage.

I think i'll go with caching the data myself for now (simply having a few integers and strings stored in a java object) and using preparedstatements to do the updates. Unless other solutions have really big advantages.

There are also a few new classes in JDK 5 for JDBC... RowSet, if I'm not mistaken. Google for new JDBC features on that SDK and you might find some good improvements.

500 bytes? Probably you mean that a row is 500 bytes, but the ResultSet caches many rows for performance reasons.
The driver might or might not be persuaded to degrade performance and cache less rows, but it might be futile due to other contributions to memory usage (like the PreparedStatement equivalents that the driver uses to modify the ResultSet).

Why are you using many ResultSet instances at the same time? Can't you process them few at a time and then close them?
It is (usually) good practice to execute complex queries instead of juggling cursors and sending individual fields and rows back and forth between application and database.
What are you doing exactly? Do you need a lot of heterogeneous data in your application, or are you interested in writing the modifications to the database?

Firstly, ResultSet is an interface therefore it does not use up any memory, only instances of classes that implement it do so.

Therefore, the answer to "why does it use up so much memory" is driver-specific and you should delve into the source code of your JDBC driver to find out.

If you're using a closed-source JDBC driver, your investigation may end there (provided you're not allowed to or prepared to decompile the driver).

I wonder how you're measuring the memory usage of the Resultset, and whether that method is accidentally also including some memory from the connection which it may hold a reference to (this is obviously also driver-specific).

Another thing to bear in mind is that Java stores its strings in some Unicode format internally (UTF-16 I *THINK*), therefore if you assume 1 char == 1 byte, you may get the amount of memory used wrong.

Finally, the Java memory usage measurement might be wrong because some (Type 2 and 3) drivers may allocate native memory in a ResultSet implementation.

Mark

Quote:

Original post by markr Firstly, ResultSet is an interface therefore it does not use up any memory, only instances of classes that implement it do so. ...

I know, but still i get an object of type ResultSet that in some way contains or refers to data which i often can't access directly, that's why i say it is the ResultSet that uses so much memory.

Quote:

I wonder how you're measuring the memory usage of the Resultset, ...

Runtime.FreeMemory before and after the line i recieve the resultset. It's not always accurate but it gives you a good indication as long as you keep it simple. Also when i simply load the resultsets of just half the objects, the difference in memory usage comes down to about 20k to 30k per resultset. Even task manager says so, and invoking the garbage collector doesn't change things.

Anyway, i changed my code to store all needed data in java objects. For every item (row) in the database i now have a java object of about 1k or 2k in size instead of a 30k resultset and it works great.

@LorenzoGatti:

The content of the database is a large number of seperate objects which are read often and sometimes updated and always have just one instance in the java vm (i probably could have gone with ejb's). I can't use a larger resultset because i'd have to use moveto which just doesn't work if multiple users need it at the same time. Maybe the RowSet object can help me here, i'll look into it some other time.

Aside from that i need a lot of data on relations between these objects which need to be found or calculated when they are loaded. As i already have the objects, and as the data is used frequently, adding the data itself to the object too seems to be a good way to go.

The reason i used updateble resultsets is that the data still gets updated every now and then, i figured that was the easiest way. Not the best way appearently.