db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: A small query related to database concepts.
Date Fri, 24 Dec 2010 16:08:49 GMT
> Isn't it make sense to  use the cached cursor of first query.

I think it's an interesting idea!

At the point where Derby is getting ready to compile the second
query, how would it know whether it already had a cached plan
for the first query?

It seems like you would need:
1) A loop which looked at all the existing cached query plans
2) A method which could take a cached query plan, and a new
query of interest, and returned "yes" if the cached query plan
satisfies the new query of interest.

I worry that implementing that method (2) would involve doing
much of the work of actually compiling that second query, so
in the end it's not clear that you'd save anything, except
that we'd be able to discard the second query's plan and save
on space in the compiled query cache (which does indeed tend
to fill up if the conditions are right).

Are there examples other than the one you mentioned ("select *"
versus "select A,B,C")? Some other examples would help demonstrate
the generality of your idea.

One situation that I have encountered in my work with Derby
involves whitespace differences in queries. Currently, Derby
    select a from t
    select  a  from  t
as two different queries, and compiles both queries. It seems
like your approach would enable the second query to use the
already-cached query plan of the first.

However, I know that some Derby users have used the current
behavior as a feature. That is, sometimes there are cases where
the user knows that the existing cached query plan is sub-optimal
(for example, the tables have changed in size dramatically since
the query was compiled), and as a workaround they run the query
with a trivial whitespace difference in order to ensure they
get a new query plan, not the existing cached one.

I think your idea is interesting, and I think that Derby would
be a good platform for you to experiment with it; you could
implement your proposal and then write some benchmarks to try
to analyze its behavior.



View raw message