Hello forum,
I have an scenario when in a store procedure i have many queries over the same tables and conditions but with different grouping. (the from and the conditions are generated dynamically depending of filters selected by the user).
Q1: SELECT field1, field2, count(*) FROM tableA ta left join tableB tb on ta.pk = tb.ida where condition1, confition2, ... group by field1, field2;
Q2: SELECT field3, field4, count(*) FROM tableA ta left join tableB tb on ta.pk = tb.ida where condition1, confition2, ...group by field3, field4;
...
....
QN:SELECT fieldX, fieldY, count(*) FROM tableA ta left join tableB tb on ta.pk = tb.ida where condition1, confition2, ...group by fieldX, fieldY;
All this queries in a execution of the store are over the same tables and condition but with different group criteria.
I want store in a place the result of a query like it that could be called common-query or CQ:
CQ: SELECT * FROM tableA ta left join tableB tb on ta.pk = tb.ida where condition1, confition2, ...;
And the use the result of CQ in the QX queries instead of execute N times the join and conditions.
My first approach was store all the result of the CQ query in a temporary global table but the problem is that the CQ query return approximate 300k rows and insert it to the temporary table is most expensive that execute N times the filter.
Do you know a less expensive way of reuse the CQ query result?
Thanks
Regards.