QGIS virtual layer has big performance impact. Even when panning or zooming in the canvas, a virtual layer could take seconds to minutes to refresh. From my speculation, the reason might be the query it has to go through.
My questions is, would that be possible to cache a virtual layer, so it doesn't have to go through all the query until a user asks for it?
Say, I may update the database once a day, so I would like the virtual layer to update also once a day on my command, instead of refreshing every time I zoom in or change a layer visibility.
Specifically, I have a virtual layer lot_comparison
combining two layers, existing_lot
(containing 900 records) and planning_lot
(containing 300 records).
the query goes like this,
select st_union(pl.geometry), el.*, pl.*
from "existing_lot" as el, "planning_lot" as pl
where el.fid = pl.fid
and (el."lotIDtype" in ('use previous lot index' ,'need subdivision')
or pl."lotIDtype" = 'need new lot ID')
group by pl."identifier"