How ResultSet works? PreparedStatment handicaps?

Hello. I have two questions about how ResultSet works.

1� When I get a ResultSet from a select query with, for example, 20 rows. How many rows are loaded in memory at a time. I've heard I won't have the 20 rows in memory at a time, but in that case, is JDBC accessing the DB again and again to get the same cursor in order to get the next row? In that case, how many rows reads each time? Isn't it a bottleneck to access DB so many times?

2� What are the handicaps of using a PreparedStatement over using a simple Statement? I've read PreparedStatment is slower if I use it only once, as its only advantage is when using the same PreparedStatement many times (for example, in a loop), but the creation of a PreparedStatement is slower. Is there any other handicap than that? Is there any other advantage?

Thank you very much.

Originally posted by Antonio Fornie:
1� When I get a ResultSet from a select query with, for example, 20 rows. How many rows are loaded in memory at a time. I've heard I won't have the 20 rows in memory at a time, but in that case, is JDBC accessing the DB again and again to get the same cursor in order to get the next row? In that case, how many rows reads each time? Isn't it a bottleneck to access DB so many times?

The short answer is, most of the time you don't care. You can set the fetch size and there are techniques (such as batch insert/updates) for working with a specific number at a time, but its really one of those issues best left to the driver.

Originally posted by Antonio Fornie:
2� What are the handicaps of using a PreparedStatement over using a simple Statement? I've read PreparedStatment is slower if I use it only once, as its only advantage is when using the same PreparedStatement many times (for example, in a loop), but the creation of a PreparedStatement is slower. Is there any other handicap than that? Is there any other advantage?

Even if you use a prepared statement once, its far better than a statement (assuming you parameterize your inputs!) since it 1) sanitizes your input and 2) allows better code maintenance, ergo you can use the prepared statement multiple times in the future without having to rewrite it. For a good example of why its always good to sanitize your inputs (convert single quotes to double quotes, etc), I refer you to xkcd.

1� When I get a ResultSet from a select query with, for example, 20 rows. How many rows are loaded in memory at a time. I've heard I won't have the 20 rows in memory at a time, but in that case, is JDBC accessing the DB again and again to get the same cursor in order to get the next row? In that case, how many rows reads each time? Isn't it a bottleneck to access DB so many times?

This depends entirely on the JDBC driver. Is it a bottleneck? Possibly. But something has to take the strain. If you have a large result set you are liable to run out of memory in your client app if you return all the results in one go.

2� What are the handicaps of using a PreparedStatement over using a simple Statement? I've read PreparedStatment is slower if I use it only once, as its only advantage is when using the same PreparedStatement many times (for example, in a loop), but the creation of a PreparedStatement is slower. Is there any other handicap than that? Is there any other advantage?

None, that I'm aware of. I'd be surprised if it were noticably slower. Where did you hear this? PreparedStatements have the considerable advantage that you can bind variables, so safegard your code against SQL injection attacks, plus avoid all those pesky formatting issues normal Statements have.

(Ah, that's the problem with long answers! A faster typer always gets there first

)
[ September 01, 2008: Message edited by: Paul Sturrock ]

Originally posted by Paul Sturrock:
(Ah, that's the problem with long answers! A faster typer always gets there first )

Better luck next time

(it is nice that are answers are nearly identical tho)!
[ September 01, 2008: Message edited by: Scott Selikoff ]

Is it a bottleneck? Possibly.

Yes, there are two antipatterns Antipatterns hard to avoid. As you avoid one, you meet the other. Whether you access DB too much often, whether you bring too much data to memory.

That's what I was thinking of when I asked. It'd be nice to know a little more about what JDBC is doing, but if it only depends on the driver, there's nothing to do.

About PreparedStatement I don't remember where did I read it. But probably it was in sun java page itself. It said a PreparedStatement was slower than a simple Statement if you only use it once. Anyway, I didn't think about security (funny link), just because of this I guess it's better to use only PreparedStatement.

Thank you very much.

Antonio

That's what I was thinking of when I asked. It'd be nice to know a little more about what JDBC is doing, but if it only depends on the driver, there's nothing to do.

Some drivers will include properties to influence performance - check the documentation for which ever one you are using. I haven't yet come accross any situations where the JDBC driver becomes such a bottleneck we've had to do something yet. Still, it might happen I suppose.