• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How ResultSet works? PreparedStatment handicaps?

 
Ranch Hand
Posts: 117
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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 ]
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 117
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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.
 
No more fooling around. Read this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic