• 1 reply
  • Latest Post - ‏2008-12-22T22:58:48Z by Stan
3 Posts

Pinned topic Inner Join between two big tables in embedded Apache Derby

‏2008-12-11T20:45:46Z |
Hi There,
I have a situation where in which I have to write an inner join between two table of the order of (30k to 60k rows).

My query is as simple as,
select A.a,B.b from A ,B where A.a = B.b;

N.B: a and b are of type varchar and I runs it on embedded Apache Derby

But the problem is it takes nearly 15 mins to run. Is there any better way of doing an inner join between such bigger tables? Is there any ways by which I can increase the speed of derby?

Jose John.
Updated on 2008-12-22T22:58:48Z at 2008-12-22T22:58:48Z by Stan
  • Stan
    267 Posts

    Re: Inner Join between two big tables in embedded Apache Derby

    I wouldn't expect the query to be a problem - you can use SHOW PLAN to examine the query processing plan. This can be found in the tuning guide along with ohter information on tuning Derby. The big things to check are:

    1) Existance of indexes on at least one of the join columns.
    2) Check that the cardinality statistics have been initialized, if in doubt - drop and recreate the indexes See this article:

    Other things that could help:
    Increase memory allocated to Derby, the JVM and the machine.
    If the tables include large rows increase the page size.