10.02.2022 14:56, Adriano dos Santos Fernandes wrote:
On 10/02/2022 06:44, Vlad Khorsun wrote:
08.02.2022 15:36, Adriano dos Santos Fernandes wrote:
Hi!
I have refactored DSQL statements/requests (continued worked of many
time ago) to separate shared (statement) and specific (request) parts.
It seems this ground work for compiled statement cache is complete and I
have even did a (very very) initial version of a compiled statement
cache working.
First, please, describe - what goals\benefits of this cache ? Especially,
taking into account current per-attachment metadata.
In ideal world applications prepare their statement and execute them
when that is going to be repeated.
I would even consider a bug if they do not do that correctly in
situations where an application process starts and do things in a loop.
But it's not surprise that many applications are not well coded and
sometimes does not do this. Competitor DBMSs has cache and works well in
this case too.
But more important to make bugged applications better, it's to make more
well coded applications better.
For example if ORM frameworks (you like it or not - they are used a lot)
cache prepared statements for better performance, that will have side
effects:
- It cannot control server memory usage.
- It locks not currently used objects preventing database changes.
I have seem in test a not very complex statement having it's prepare
time reduced by 50% when it's cached.
So, main benefit visible to end-user is to save prepare and check access
time, correct ? Also, good written apps (that re-uses prepared statements)
will not see much changes - at least until impl of shared metadata cache.
I'm not against of caching statements by the engine, I just want to explore
both sides of coin.
More, I already implemented simple cache of prepared statements near 5 years
ago and I know when it is useful :) It was not ported into Firebird because of
limited usage and its simplicity. But customer which uses it, was very happy.
Also reuse of cached statements reduces memory consumption of individual
uncached identical statements.
I.e. when application uses more than one instance of the same statement in
the same connection ? Hard to imagine, but everything possible...
Now I think it's better to discuss its semantics.
First what should be the statement key in the cache?
All what affects statement compilation process, at least.
I've peek these:
- statement text
- clientDialect
- isInternalRequest
Why it is important ? Do we have internal DSQL requests now ?
Yes.
Is it makes sense to have two caches - for internal and for user
statements ?
In long term I think yes, but it's not two cached, it's just a piece of
the key. And not used cached internal requests would go out of cache.
Anyway, it's very easy to disable it if it's considered as not important
now.
The main idea is to allow to disable user cache but not system cache.
Are you going to move IRQ_REQUESTS and DYN_REQUESTS into such system cache,
or am I too optimistic ?
- current client charset (as external engines may change it)
- active roles
If\when shared metadata will be implemented - will it be possible to
use cached compiled statement created in one attachment to use by another
attachment ?
It should be the easier part in the process of shared metadata changes.
If yes, does it means that another attachment should use same
client charset
Yes.
and active roles to be able to use cached compiled
statement ?
About roles, they may not necessary be part of the cache key.
If they are not, them when roles are different than one present in
cached statement, a verifyAccess would need to be called on the
statement get from the cache before it's usage is allowed.
I ask because such restrictions could make stmt cache less useful.
For example, we could keep list of already verified set of credentials
(user name + roles list) with cached stmt to not include it into key.
...
And you not explained cache usage - when and how cached statement
should be used.
I'm not sure you want more additional information than one I replied here.
I want to clarify - what happens when app prepares (or execute) two
identical stmts ? First instance could be taken from cache, ok. What
happens with second instance ?
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
