db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Harshad <harshad...@gmail.com>
Subject Re: Performance issue of derby using JDBC
Date Mon, 15 Dec 2008 18:43:18 GMT
Hi Kristian,

The entire code is available at http://code.google.com/p/jamun/.
It is written in scala, which might not be familiar to all. I am posting below some snippets
that might help.

Kristian Waagan wrote:

> I think you'll have a better chance of getting any answers if you
> provide more information, for instance;
>   o data volume; number of rows in table(s)

Number of rows in the concerned table = ~ 50k

>   o the SQL query, and possibly the Java code executing it (a little
> later, the query plan might come in handy too)

This is the prepared statement:

conn.prepareStatement ( 
  "select name,version,release,time from PKG where PKG.id in (select id from PROVIDES where
name = ?)",
  ResultSet.FETCH_FORWARD,ResultSet.TYPE_FORWARD_ONLY )


This is the collector function (it is in scala, but I hope it's logic would be apparent):

def extractPkgResult(repo:Repo, resultSet:ResultSet) = {
     if (resultSet.next) {
       var pkgList:List[MngdPackage] = Nil
       var keepGoing = true
       while (keepGoing) {
         pkgList ::= MngdPackage(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
"s umm", "", Nil, Nil, Some(repo), resultSet.getLong(4))

         keepGoing = resultSet.next
       }
       pkgList
     } else Nil
   }


>   o database schema (including indexes)
create table PKG  (
                         id integer primary key,
                         name character varying (60),
                         version character varying (40),
                         release character varying (40),
                         time bigint)
create table PROVIDES (
                         id integer, foreign key (id) references PKG(id),
                         name character varying (256),
                         version character varying (40))

create index PROVIDES_NAME_IDX on PROVIDES (name)

> 
> Since you're searching for a string inside a VARCHAR, Derby has to check
> all rows in the table. Are you using LIKE in the query?

No, it's a "where x = y"

> Is there a way that you can reduce the number of rows that qualify?
No. It is already down to 1 (99% of the time)

> Are you seeing the long wait on the first rs.next() ?

Yes! I tried more detailed profiling now and this is indeed true.

> What about the subsequent rs.next() calls (if more than one row in the
> result)?

In case of empty result, first rs.next consumes big time.
In case of non-empty result, the rs.next at the end of the result (when it returns false)
consumes little time; less than 1 ms. All other rs.next calls consume big time (150 to 200
ms)

> 
> 
> There are also some tips here:
> http://wiki.apache.org/db-derby/PerformanceDiagnosisTips

I am attaching the query plan.

thanks,
Harshad
Mime
View raw message