Stop Virtual layer from automatically recalculation

Question

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"

Answer 1

Other than using PostGIS suggested by Taras, or saving the virtual layer as a new regular layer suggested bay Jakob, I gound yet another way to speed this up. The solution was found in this webpage.

Basically it involves spatial indexes. The code would be 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')
    and pl._search_frame_ = el.geometry
    --the above logic is like this: aaa and (bbb or ccc) and ddd
group by pl."identifier"

I added code in the third to the bottom line, which means for each geoemtry of layer existing_lot, compute its bounding box, select only geometries of the planning_lot that are inside this box, then do the rest of the calculation.

It has an order issue, meaning el._search_frame_ = pl.geometry doesn't work, for reasons I haven't explored yet.

Answer 2

After you created a Virtual Layer, simply take off the check mark, see image below.

And the next time you need it click the checkmark back.