Hi all,
I am new to Derby and database programming in general. This is my first real-world setup, and I am getting horrifying performance from my application. (The code is available at http://code.google.com/p/jamun/ )
It is one particular query that is causing me major worry. It is a simple query that searches for a string in a "character varying" column.
I have tried these following optimizations already:
* Using prepared statements
* Creating an index on the searched column (and ensuring that it indeed
gets used)
* Used a non-scrollable, forward only result set
* Avoided row-level locking
These seem to have paid off; the actual query time (statement.executeQuery) is pretty fast : about 1 or 2 milliseconds.
But reading from the resultSet using APIs such as getString, getLong, takes about 250 ms or more!!
A couple of questions:
1. Are these numbers typical?
2. Are there alternatives to the JDBC api (for derby)?
3. Is the time consumed by executeQuery misleading, because it gets evaluated lazily on actual access of resultSet?
thanks in advance,
Harshad