Thread: Re: eWeek Poll: Which database is most critical to your

Re: eWeek Poll: Which database is most critical to your

From
"Henshall, Stuart - WCP"
Date:
> -----Original Message-----
> From: Neil Conway [mailto:nconway@klamath.dyndns.org]
> Sent: 27 February 2002 00:24
> 
> On Tue, 2002-02-26 at 19:08, Dann Corbit wrote:
> > Statistical tools are a good idea, because they can tell us where
> > indexes should be added.  However, you cannot simply return 
> the result
> > of the previous query, because the contents may have 
> changed since the
> > last time it was executed.  It is simply invalid to do 
> that.  If some
> > other system is doing that, then it isn't a relational database.
> 
> No -- as I said, any inserts, updates or deletes that affect the table
> in question will cause a full cache flush.
> 
> > How do you know whether or not someone has affected the row that you
> > are reading?  If you do not know, then every single update, 
> insert or
> > delete will mean that you have to refresh.
> 
> Yes, that is true.
> 
> >  And not only that, you will
> > also have to track it.  For sure, it will make the whole system run 
> > more slowly rather than faster.
> 
> I don't think tracking changes imposes a lot of overhead -- it is
> relatively simple to determine if a query affects a given table.
> 
> > Very likely, it is only my limited understanding not really 
> grasping 
> > what it is that you are trying to do.  Even so,  I don't 
> think it really
> > helps even for read only queries, unless it is exactly the 
> same query
> > with the same parameter markers and everything that was 
> issued before.
> > That is very unusual.  Normally, you won't have the entire 
> query hard-
> > wired, but with allow the customer to do some sort of 
> filtering of the
> > data.
> 
> Hmmm... the more I think about it, the more unusual it would be for
> _exactly_ the same query to be repeated a lot. However, the article
> reported a significant performance gain when this feature was enabled.
> That could mean that:
> 
>     (a) the performance measurements/benchmarks used by the 
> article were
> synthetic and don't reflect real database applications
> 
>     (b) the feature MySQL implements is different than the one I am
> describing
> 
> When I get a chance I'll investigate further the technique 
> used by MySQL
> to see if (b) is the case. However, it is beginning to look like this
> isn't a good idea, overall.
> 
> Cheers,
> 
> Neil
> 
> -- 
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
> 
>
If I understand correctly you'll be taking matching the on the original 
query string, then pulling out the previous plan, rather than doing all 
the planning again? Or where you thinking of storing the resultant tuples 
(seems far more diffcult to do effciently)?
Either way would be handy for me though as I have a number of clients who 
all basically ask the same query and then ask it again every few minutes 
to update themselves. Therefore this sounds like something that would 
improve performance for me.
Hope I've understood correctly,
- Stuart


Re: eWeek Poll: Which database is most critical to your

From
Neil Conway
Date:
On Wed, 2002-02-27 at 10:51, Henshall, Stuart - WCP wrote:
> If I understand correctly you'll be taking matching the on the original 
> query string, then pulling out the previous plan, rather than doing all 
> the planning again? Or where you thinking of storing the resultant tuples 
> (seems far more diffcult to do effciently)?

Well, those are really two different features. The second (caching
entire result sets based upon the _exact_ query string) is implemented
by MySQL, and is probably the more exotic feature of the two. There is
some debate about whether this is even worthwhile, or just results in
better benchmark results...

As Tom points out, the first feature (caching query plans) is probably
better implemented by allowing application developers to prepare queries
and specify their own parameters. This is a fairly conventional RDBMS
feature and it is already on the TODO list.

> Either way would be handy for me though as I have a number of clients who 
> all basically ask the same query and then ask it again every few minutes 
> to update themselves. Therefore this sounds like something that would 
> improve performance for me.

Good to know...

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC



Re: eWeek Poll: Which database is most critical to your

From
Denis Perchine
Date:
Hello,

> If I understand correctly you'll be taking matching the on the original
> query string, then pulling out the previous plan, rather than doing all
> the planning again?

No.

> Or where you thinking of storing the resultant tuples (seems far more
> diffcult to do effciently)?

Yes. They store a result of the query, not the plan. Storing a plan is not 
such a win as storing a result of queries returning relativly small amount of 
tuples.

--
Denis