A small query related to database concepts.

[vipul modi <vi...@gmail.com>]

Hi

Lets say we have two queries on the same table TAB1 (COL1 , COL2 ,COL3)
as
SELECT * FROM TAB1
SELECT COL1 FROM TAB1
Isn't it make sense to  use the cached cursor of first query.
What could be possible drawbacks ..  other than having a less efficient
query plan , wouldn't it be really efficient for tables less than say 1000
rows.
I would like to add such a feature in derby.
Any suggestions please....

-- 
Vipul Modi
MSc.(Tech) Information System
BITS-Pilani Goa Campus

Re: A small query related to database concepts.

[Bryan Pendleton <bp...@gmail.com>]

> 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
treats
    select a from t
and
    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.

thanks,

bryan