I am trying to write a stored procedure that simply echoes massaged data from a fairly small result-set that is achieved from a complex slow query.
My objective is to write a fast stored proc aka to execute that slow complex query just once or as few times as possible. I Cannot use temporary tables because the session is shared through connection pooling. Array seems like the right fit but not sure how to echo results from it.
delimiter //
CREATE OR REPLACE PROCEDURE (...) AS
DECLARE
qry QUERY(...) = <my complex slow select statement that returns a handful of rows>
arr ARRAY(RECORD(...)) = COLLECT(qry);
aggr1 BIGINT = 0;
aggr2 BIGINT = 0;
BEGIN
FOR r IN arr LOOP
aggr1 += r.col1
aggr2 += r.col2
END LOOP;
ECHO SELECT ... include aggr1 & aggr2 & cols...
FROM qry JOIN qry ;
END //
delimiter ;
How do I avoid the second SELECT statement and ECHO the results from arr directly?
Too bad about the connection pooling – a temp table would have been great here.
The TABLE function is good to know about but won’t help you directly since it only works for arrays of scalars, not records.
If you really don’t want to run the qry more than once, consider putting the results in a real table with an ID column, then select from that table using the ID column to filter to find the rows you want. Then write a batch job to garbage-collect old rows from there, e.g. delete stuff that’s more than an hour old.
Materialized CTEs is an experimental feature that would help here, but it’s not recommended for production use. In a future release, we expect to make it fully supported, but I can’t commit to a time for that.
I was very encouraged by the materialized CTEs… so much so that, I already wrote my stored procs Thank you for clarifying that it is an experimental feature (I somehow missed the glaring Warning).
Creating a real table is definitely an overkill for my purpose.
Thanks for all the tips. At this point, looks like we may just have to execute the query multiple times.
delimiter //
CREATE OR REPLACE PROCEDURE (...) AS
DECLARE
qry QUERY(...) = <my complex slow select statement that returns a handful of rows>
arr ARRAY(RECORD(...)) = COLLECT(qry);
aggr1 BIGINT = 0;
aggr2 BIGINT = 0;
sql TEXT = 'ECHO ';
count BIGINT = 0;
BEGIN
FOR r IN arr LOOP
aggr1 += r.col1;
aggr2 += r.col2;
sql = CONCAT(sql, 'SELECT ', r.col1, ', ', r.col2.... );
IF _count < LENGTH(arr)-1 THEN
sql = CONCAT(sql, ' UNION ALL ');
END IF;
count += 1;
END LOOP;
EXECUTE IMMEDIATE sql;
END //
delimiter ;
In other words, we built the data using dynamic sql looping through the array instead of executing the complex-slow select multiple times. This may not work for every scenario but posting this in hopes that it may at least inspire other developers.
Thank you for clarifying that it is an experimental feature (I somehow missed the glaring Warning).