--- In Firebird-Architect@yahoogroups.com, Jim Starkey <jas@n...>
wrote:

>
>
> paulruizendaal wrote:
>

...snip...

> >
> System wide SQL statement cache. Ann is arguing for normallized

SQL

> statements with single spaces in place of arbitrary white space and
> uniform capitialization. It's hard to argue against what is not

more

> than an hour of work, but I don't believe it will buy much but

domestic

> peace.

Hi there,

I am new to this mailing list, I have following some discussions for
a short period of time, and do not subscribe because don't need to
post. I am a FB user, and don't know the FB internals, and do not
have knowledge of how a db engine works... So forgive me to enter
this holy place :)

I am really interested on the cross connection prepared statements
cache...

I have read that is to expensive to have semantical analisys to check
if one statement is "equal" to others, and because of it, the cache
will compare only exact match (Ann proposed to normalize those
statements to improve the hit-ratio)...

What I was thinking is:

The "cache-manager" could be a bit more "smart" without be slow ?

if I have those statements:

Select PersonId, Name, Age from PersonTable where Age = 18 order
by Name;

and

Select PersonId, Name, Age from PersonTable where Age = 21 order
by Name;

the cache could not understand that this can be converted to

Select PersonId, Name, Age from PersonTable where Age =
<something> order by Name;

And cache this "meta-statement"

and make just parameter substitution when variations come in ?

Those statements could be divided in some pieces:

1.) Take from start to "where" clause (this should match exactly)
Select PersonId, Name, Age from PersonTable where

2.) Check for the where clause, (change every value to <something>)
Age = <something>

3.) Check the order/group by clause (must match exactly)
order by Name

Select Name, Age from PersonTable

will be considered diferent to

Select Age, Name from PersonTable

by the cache-manager...

Of course there are a lot more complicated queries, but if the cache
can cache the simple ones, the others will be treated by "exact
match".

Other examples that I consider simple statments:

Delete from PersonTable where PersonID = ?;
Update PersonTable set Age = ? where PersonID = ? and BirthYear = ?;
Update PersonTable set ParentID = ? where ParentId = ?;

I think that the most frequent queries (and the ones that will take
benefit by cross-connection cache) recieved by the engine are those
type of "simple queries"...

This kind of analisys is more expensive then prepare the statement ?

Is possible to differ a "simple" query from a "complex" one ?

I hope I could make you understand me (my english is very bad).

And forgive me if this is a too lame question to be asked here and if
my message looks prolix and tedious, I try to explain my ideas with a
poor control of English.

Any way, thank you guys and gals for the great work on this engine.
It is pleasure to read your discussions about the future features of
the product I use... A pleasure that only an open development group
can offer to the users...

See You

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil