• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Derby ... limiting fetched rows number

 
Alex Nedelcu
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I recently began to consider Apache Derby for my next project.
But I have a big problem ... I cannot find a way to limit the rows number ... aka the LIMIT clause from MySQL.

The only (incomplete) way I found of doing this is by using the setMaxRows and setFetchSize methods from JDBC statements.
But I don't think they are optimized, and my result set may get pretty big.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please don't confuse setFetchSize() and and setMaxRows().

Setting the fetch size suggests to the driver the number of rows it should get in a chunk from the database. You set it to reduce the number of network requests made to the database while iterating over the resultSet, or control the size of the driver's internal data buffers. The driver implementation might not repspect the setting, and other settings or the driver implemntation itself may cause it to cache all the rows of the resultset in memory anyway. It does not limit the number of rows returned when the ResultSet is iterated over.

setMaxRows() limits the number of rows returned in the ResultSet. As I understand it (I don't use either more than casually) it has the same logical effect as the MySQL LIMIT syntax, but setMaxRows operates at the JDBC/driver level while LIMIT occurs with SQL execution. It's certainly possible that limiting via SQL can be implemented more efficiently than can limiting in the driver for a subset of SQL statements, but for the general case, the database has to produce much or all of the result and then drop the trailing rows, no matter which implementation is used. this is a simple consequence of the fact that ORDER BY is going to be applied last, implemented as a sort, and there is simply no way rows can be dropped until the result has been ordered.

If you're concerned about the performance of something, there is absolutely no substitute for benchmarking yourself; JDBC is sufficiently complicated and subtle that operating on theory alone is usually a recipe for disaster, there's always something you didn't know or didn't think about.
 
Masoud Kalali
Author
Ranch Hand
Posts: 531
Java Mac OS X Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stu explains everything about those two methods
I should add that derby has not sql command syntax to limit the rows returned.
also it does not have offset command to ask it to return an specefic offset of the result (Paging?)
 
Alex Nedelcu
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your replies. You guys are quite helpfull.

Have any of you used Derby in a real world application?
Any impressions ?

I don't have experience with Derby, and maybe you guys know a better alternative. I am interested in embedded databases, and I looked at HSQLDB, but it seems it doesn't have transaction support, which is very important to me.

I am currently reading "Apache Derby Off to the Races" and because they place great attention on data recovery after a system crash ... should I be worried ?
 
Masoud Kalali
Author
Ranch Hand
Posts: 531
Java Mac OS X Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I used derby in real jobs and i am quite happy with it.
as you said one of its advantages over HSQL is transaction and fault recovery which imho is very important feature in buziness applications.

as you know derby is backed by some major company like Sun and IBM so it has a shiny future too.


you do not need to be worry about it it crashes , because it will not crash
 
Sergey Zolotaryov
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When looking for a free and reliable database which supports transactions I would highly recommend Firebird. I used it in one big project and it proved to be very fast and reliable. This db offers excellent concurrency and is supported on linux and windows, so its easy to develop with it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic