Stop Virtual Layer from automatically recalculation in QGIS

Question

QGIS's 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"

• Simbamangu: Could you add information about the virtual layer - how it's structured, size, and how added to canvas?
• Taras: Please, do not forget about "What should I do when someone answers my question?"
• David H. J.: @Taras I see. Since that answer did not solve my problem as I expected and it might help someone else, I decided not to vote on it. Thank you for your help though.

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.

• Jakob: Do you have control of your 'and-or-and'. I would but some ( ) for clarifying how 'and or and' are interconnected. Something like: where (aaa and xxx or yyy) and zzz

Answer 2

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

result

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

• David H. J.: Uncheck a virtual layer could definitely speed up operations on all other layers. But when interacting with the virtual layer itself, the loading speed is still an issue. How could we deal with that problem?
• Taras: Maybe use PostGIS
• Jakob: Save it in a file format or database table and then rerun it manually to get a update for another save.