On 12/04/07, Adam Bovill <[EMAIL PROTECTED]> wrote:
When I perform queries on my derby database, the response times seem to be rather large. (At the bottom of this email you can see my schema)Database: Sessions table has 19 rows Items table has 29000 rows I perform my queries in the following way: return _DBconnection.createStatement().executeQuery(sql); If I perform the following query on the sessions table: select id, sid from sessions where id =15 it takes 30-50 ms If I perform the following query on the items table: select id, aid from items where id =26111 it takes 40-65 ms Note that both of these queries are on the primary key It would seem that these times were rather slow for such simple queries and small tables. Are these the expected times? Or are they slower that expected?
The reponse times you report is rather slow. Derby should use about 50 microseconds on these queries not 50 milliseconds. On a dual CPU machine embedded Derby is able to execute about 20.000 queries like these per second.
What steps should I take to try to improve the speed? At the moment I'm just running derby w/o changing any settings. (I'm running on a 3ghz p4 with a good bit of ram. Running under linux v. windows doesn't seem to make a difference.
I think a lot of this has to do with how you execute the queries: return _DBconnection.createStatement().executeQuery(sql); Here it seems like you for every query you execute, you do: 1. Create a new statement 2. Execute a query by giving Derby a string containing the SQL code. If this string differs between runs Derby will have to compile this query every time you run you query (since you likely change the value of the id = 19...) Compiling queries in Derby is expensive. Suggestions: 1. Do not create a new statement for every query you run 2. Use (and re-use) a preparedStatement for each of you query types. For an example of what it cost to execute and compile a query compared to executing a prepared statement see slide 22 in the following presentation given at last years ApacheCon: http://home.online.no/~olmsan/publications/pres/apachecon06us/apachecon06.pdf Hope this help, if not report back and request more advice. Olav
