Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2008-12-22T22:58:48Z by Stan
josejohn
josejohn
3 Posts
ACCEPTED ANSWER

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?

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

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

    ‏2008-12-22T22:58:48Z  in response to josejohn
    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: http://wiki.apache.org/db-derby/CheckingForIndexStatistics

    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.