Improve migration performance

[margrietpalm]

Biggest issue is migration 223, specifically making the polygons. For test case (zevenaar):

• create_buffer_polygons: ~6 seconds
• create_square_polygons: ~4 seconds

Note that using either backup=False or backup=True does not make a noticable difference.

Optimized query (works in sqlite 3.33 and newer)

Potential solution (indexes may not be necessary) that works in sqlite 3.33 and newer

-- Ensure indexes are present for faster lookups
CREATE INDEX IF NOT EXISTS idx_surface_id ON v2_impervious_surface(id);
CREATE INDEX IF NOT EXISTS idx_surface_map_surface_id ON v2_impervious_surface_map(impervious_surface_id);
CREATE INDEX IF NOT EXISTS idx_surface_map_connection_node_id ON v2_impervious_surface_map(connection_node_id);
CREATE INDEX IF NOT EXISTS idx_connection_nodes_id ON v2_connection_nodes(id);

-- Optimized update query
UPDATE v2_impervious_surface
SET dwf_geom = ST_Buffer(vcn.the_geom, 1)
FROM v2_impervious_surface_map vism
JOIN v2_connection_nodes vcn ON vcn.id = vism.connection_node_id
WHERE v2_impervious_surface.id = vism.impervious_surface_id
AND v2_impervious_surface.dwf_geom IS NULL;

journal_mode

Changing journal_mode to WAL should ensure that results are not directly written to disk. Attempts so far didn't result in any changes.

• Added connection.execute(text("pragma journal_mode = WAL;")) to migrations/env.py::run_migrations_online
• Added connection.execute(text("pragma journal_mode = WAL;")) to functions that slow downn migration

other optimizations (to do)

https://phiresky.github.io/blog/2020/sqlite-performance-tuning/

[leendertvanwolfswinkel]

Slow sqlites:

zevenaar.sqlite | 1858 s
Purmer_Purmerend | 2 | 830.213017
Y0254_5_BOL_purmerend | 8 | 790.278416
Y0228_Lochem_KBO | 6 | 584.664718
Hoofdgebied_uitwerking_Cluster16_hwa | 2 | 372.378615
assen_pittelo - pittelo_klimaatsom (1) | 19 | 2551.272434
assen_marsdijk - marsdijk_klimaatsom (1)
groningen-selwerd - selwerd1d2d_rwa_gecombineerd_dem_huidig (1) | 90 | 281.943609
hub_scenarioberekeningen__0d1d_test | 1 | 207.50814
marsdijk | 10 | 253.05844
westerkogge_leggertool__0d1d_test | 10 | 1784.648107
sgravendeel_riolering | 1 | 1907.207829