db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gurvan Le Guernic <glegu...@gmail.com>
Subject How to optimize performance of ResultSet traversal?
Date Tue, 26 May 2009 17:20:44 GMT
 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.

View raw message