Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-type: text/plain; charset=utf-8
Date: Thu, 19 Feb 2009 16:34:51 +0100
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Query Compilation Drastically Increased on Join
In-reply-to: <64A84ACFB244464D8C7086F909034D540DB3D830@athena.isdwhq.com>
Sender: Knut.Hatlen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Cc: "msegel@segel.com" <msegel@segel.com>
Message-id: <wjo88wo2l9t0.fsf@sun.com>
Organization: Sun Microsystems
Content-transfer-encoding: QUOTED-PRINTABLE
References: <64A84ACFB244464D8C7086F909034D540DB3D80C@athena.isdwhq.com>
 <20090218200143.947DD5D721@dbrack01.segel.com>
 <64A84ACFB244464D8C7086F909034D540DB3D830@athena.isdwhq.com>
User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/23.0.90 (usg-unix-v)

"Dustin T. Clifford" <DClifford@isdcorporation.com> writes:

> Michael,
>
> I agree with you that it is not =E2=80=98simple=E2=80=99 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 possib=
le
join orders increases 90 times (from 40320 to 3628800). In addition t=
o
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 ti=
me
if the compilation performs an exhaustive search for the best plan gi=
ven
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 su=
re
exactly how it determines that it should time out. Perhaps someone el=
se
knows?

--=20
Knut Anders