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. Also reuse of cached statements reduces memory consumption of individual uncached identical statements. >> 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. >> - 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. >> Do you see any thing more? >> >> Then there is when statements should go out of cache? >> >> I see three approaches: >> - 1. Timeout after its put in the cache, updated when it is get from it >> - 2. LRU based on memory consumption and max cache size >> - 3. Both 1. and 2. >> >> I think we can start with 1. > > For me 2 is a must, 1 good to have but less important. > Ok. >> Should it be enabled by default? >> >> I think yes. > > Yes for internal requests. Not sure about user requests. > I would better put small max. memory usage for it than disable. It would impact a lot some usage patterns - for example - reports with sub-reports where sub-report always prepare its identical parameterized query. > 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. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
