More info about the MAL plan cache
Hi there, I have a couple of questions about the plan cache. 1) I know it caches MAL a user-functions for a given "SQL query". What does "SQL query" really means? The unparsed SQL script? The parse tree? The relational algebra tree? The normalized/optimized algebra tree? 2) What are the conditions for the cache to be used? Does it kick in only for some types of queries? I noticed some rather large queries (which take 300-400ms just to be translated from SQL to MAL) are not being cached (or so it seems - how can I check?) 3) Hypothetically speaking, would it be possible to interleave SQL and MAL queries? I am well aware that using MAL directly is not recommended - I only would like to know about feasibility. My typical use case is: START TRANSACTION; a few INSERT INTO "parameters"; SELECT * FROM my_big_view; -- my_big_view uses table "parameters" ROLLBACK; -- values inserted into "parameters" are flushed View "my_big_view" can be a rather large query. It takes an awful lot of time just to get a MAL plan for it. Suppose I have already a MAL plan for it, at client side. I could start a MAL session and run it. I guess though, the main problem is that it wouldn't be in the same transaction as the preceding (sql) INSERT INTO, so it wouldn't get the values inserted into "parameters". Would the only possibility be to move also the INSERT INTO into MAL? So skip SQL altogether? Thanks for you help, Roberto
I was forgetting a last question:
4) How does PREPARE relate to that? Is it just an explicit way of using the
MAL plan cache, or does it go a different way?
On 7 October 2016 at 13:17, Roberto Cornacchia wrote: Hi there, I have a couple of questions about the plan cache. 1) I know it caches MAL a user-functions for a given "SQL query". What
does "SQL query" really means? The unparsed SQL script? The parse tree? The
relational algebra tree? The normalized/optimized algebra tree? 2) What are the conditions for the cache to be used? Does it kick in only
for some types of queries? I noticed some rather large queries (which take
300-400ms just to be translated from SQL to MAL) are not being cached (or
so it seems - how can I check?) 3) Hypothetically speaking, would it be possible to interleave SQL and MAL
queries? I am well aware that using MAL directly is not recommended - I
only would like to know about feasibility. My typical use case is: START TRANSACTION;
a few INSERT INTO "parameters";
SELECT * FROM my_big_view; -- my_big_view uses table "parameters"
ROLLBACK; -- values inserted into "parameters" are flushed View "my_big_view" can be a rather large query. It takes an awful lot of
time just to get a MAL plan for it.
Suppose I have already a MAL plan for it, at client side. I could start a
MAL session and run it. I guess though, the main problem is that it
wouldn't be in the same transaction as the preceding (sql) INSERT INTO, so
it wouldn't get the values inserted into "parameters".
Would the only possibility be to move also the INSERT INTO into MAL? So
skip SQL altogether? Thanks for you help,
Roberto
participants (1)
-
Roberto Cornacchia