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?
NOTICE: 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.
This topic has been locked.
1 reply Latest Post - 2008-12-22T22:58:48Z by Stan
Pinned topic Inner Join between two big tables in embedded Apache Derby
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2008-12-22T22:58:48Z at 2008-12-22T22:58:48Z by Stan
Stan 120000HAGM267 PostsACCEPTED ANSWER
Re: Inner Join between two big tables in embedded Apache Derby2008-12-22T22:58:48Z in response to josejohnI 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.