aspose file tools*
The moose likes JDBC and the fly likes How ResultSet works? PreparedStatment handicaps? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How ResultSet works? PreparedStatment handicaps?" Watch "How ResultSet works? PreparedStatment handicaps?" New topic
Author

How ResultSet works? PreparedStatment handicaps?

Antonio Fornie
Ranch Hand

Joined: Aug 07, 2008
Posts: 117
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.


I feel there must be far better ways to do what I do... that makes learning even funnier
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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 ]
Antonio Fornie
Ranch Hand

Joined: Aug 07, 2008
Posts: 117
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
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How ResultSet works? PreparedStatment handicaps?