db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Query Compilation Drastically Increased on Join
Date Thu, 19 Feb 2009 15:34:51 GMT
"Dustin T. Clifford" <DClifford@isdcorporation.com> writes:

> Michael,
> I agree with you that it is not ‘simple’ and we do have plans to
> optimize this query amongst others.
> The question still remains to me though, why after adding 1 or 2
> tables (the query had 8 tables joined before) does the compile time
> jump so drastically when the execution time remains, approximately,
> the same.

Hi Dustin,

If you go from 8 tables to 10 tables in the join the number of possible
join orders increases 90 times (from 40320 to 3628800). In addition to
the join orders, the optimizer will have to pick table scan or index
scan for each of the tables, which increases the difference in the
complexity further as you add more tables. It is this combinatorial
explosion that increases the compilation time. However, even if it
requires a lot more work from the compiler to find the best execution
plan, the actual plan doesn't have to be that much more expensive. So I
don't think it's unreasonable that the execution time increases less
than the compilation time as the complexity increases.

12 minutes does sound like a long time, though. Perhaps not a long time
if the compilation performs an exhaustive search for the best plan given
the large number of possible combinations, but I was under the
impression that the optimizer would time out if the compilation took too
long and just take the best plan that it has found so far. I'm not sure
exactly how it determines that it should time out. Perhaps someone else

Knut Anders

View raw message