db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: How to optimize performance of ResultSet traversal?
Date Thu, 28 May 2009 14:13:04 GMT
Gurvan Le Guernic <gleguern@gmail.com> writes:

>    Hi,
>  I am building an application relying on Derby (embeded) to retrieve
> data depending on its "proximity" to a "position". When a user queries
> for a "position", the application "guesses" a few "constraints" which
> are true for the nearest data, retrieves data fulfilling the
> "constraints" from Derby, and iterate until having enough results.
>  The consequence is that for "one application query", "multiple SQL
> queries" are run (by a matter of facts, the majority of SQL queries do
> not return any results). I need to optimize the time spent running
> application queries.
>  I evaluated executions. It turns out that running SQL queries (
> ps.executeQuery() ) is relatively fast (3ms up to 31ms), but
> retrieving results ( iterate sqlRes.next() in a while loop) is time
> consuming (17ms up to 343ms) even if the task inside the loop takes
> negligible time (max 1ms for all the iterations of a given ResultSet).
>  The worst case scenario is for the many queries returning no results:
>    ps.executeQuery(): 3 or 4 ms
>    sqlRes.next(): 17 to 21 ms
>  How do I optimize/configure Derby such that iterating
> ResultSet.next() in the condition of a while loop does not take has
> much time?
>  If it can help optimization, the database content is constructed once
> and for all. When the application runs, it only executes SELECT
> queries on the database. The maximum number of results returned by a
> single SQL query is in the order of 30.


Embedded Derby doesn't do much except opening a cursor when you call
executeQuery(). It's not until you call next() the data is
scanned/joined/sorted to give the results you want. There's no way to
move work from next() to executeQuery(), AFAIK, so the only advice I can
give is that you should make sure you have the necessary indexes to
speed up your query. This section of the tuning guide is a good starting
point: http://db.apache.org/derby/docs/10.5/tuning/ctundepth10525.html

Note that the number of rows returned is not always that relevant for
the performance. The number of rows you need to look at in order to
produce the result is more important, and a good set of indexes may help
reducing this number.

Hope this helps,

Knut Anders

View raw message