db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Long compilation time for a Prepared Statement
Date Thu, 14 Jul 2011 04:05:59 GMT
> 1. Does the compiler take the amount of records in consideration when
> compiling the query?

Yes. The optimizer has statistical information about the size of
the various tables, and about their keying structures, etc.

> 2. Am I right to assume the cause of the delay is in the excessive
> amount of joins, that is causing the compiler (and optimizer) to have
> a hard time figuring out the best query plan?

Possibly. There are extreme combinatorics here, for sure; some of
the search space exploration is factorial in complexity.

I had some similar problems, and opened a job with my findings:

In the details of that job are references to other related jobs.

> 3. Is there any parameter that controls the timeout for that
> optimization? Since the view does not actually have many records to
> begin with, I think it's acceptable to go with a non-optimal query
> plan.

As you'll see in my notes on DERBY-2130, I followed a similar
line of reasoning, and found that by forcing an early termination
of the search, I got a reasonable query plan.

> 4. Is there any other information I can provide to help debug? There
> are no stack traces in the log, but I'm not seeing that much info on
> it anyway: I see a line where compilation begins and the next one, two
> minutes later, is when the compilation ends. I have also attached my
> derby.properties below [4].

This is hard stuff. I'm not sure what to suggest. Although Derby
has powerful algorithms in this area, there are a few weak spots,
and it's possible you've hit one of them.

Since it's open source, we as a community can all work together to
try to improve Derby's implementation in this area.



View raw message