Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of olavsa@gmail.com designates
 64.233.162.228 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=FrVBXQFlaMLOlTiQpaDjjiCwpE5vImO5soCP1IWpjmQJPK49SQS/Ma6uKEgwBGsZxvzNva0lqb5SMLLhYZGkmpmcRHZut0DQD/moLL2dO0G5lHgJn5gxyWGuWWDLAjLdBgVR5ml85ogemgJDGQm+j7MtMtI/g9qlEWZKDCb4oeM=
Message-ID: <d4ea1e910704121426w3fcdd79dta2726ae14e456ac4@mail.gmail.com>
Date: Thu, 12 Apr 2007 23:26:36 +0200
From: "=?ISO-8859-1?Q?Olav_Sandst=E5?=" <olavsa@gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Derby select query speed questions
In-Reply-To: <619FD34741327C47AC7FBA8DBADBF5E0E19405@ffex01.fnfr.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <619FD34741327C47AC7FBA8DBADBF5E0E19405@ffex01.fnfr.com>

On 12/04/07, Adam Bovill <abovill@fnfr.com> 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