You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
It is possible that the database will be overhauled as this project progresses, but in the meantime we can gain significant improvements by indexing on key columns in the database. From my own testing, an easy fix is: CREATE INDEX experiment_id ON ncfiles (experiment);.
Before index creation, a sample query:
sqlite> select distinct ncfile, dimensions, chunking from ncfiles
WHERE experiment = '1deg_jra55v13_iaf_spinup1_A'
AND (basename_pattern = 'ocean.nc' OR basename GLOB 'ocean.nc')
AND variable in ('u')
ORDER BY ncfile;
...
CPU Time: user 9.605138 sys 18.436621
Index creation on the full database, post-creation:
sqlite> create index experiment_id on ncfiles (experiment);
CPU Time: user 84.730643 sys 139.632335
Same query, after index creation:
sqlite> select distinct ncfile, dimensions, chunking from ncfiles
WHERE experiment = '1deg_jra55v13_iaf_spinup1_A'
AND (basename_pattern = 'ocean.nc' OR basename GLOB 'ocean.nc')
AND variable in ('u')
ORDER BY ncfile;
...
CPU Time: user 0.168388 sys 0.589691
This is a few orders of magnitude faster. Obviously this isn't the most rigorous of tests (filesystem caching etc.) One thing I'm not sure about is the effect on build_index
The text was updated successfully, but these errors were encountered:
It is possible that the database will be overhauled as this project progresses, but in the meantime we can gain significant improvements by indexing on key columns in the database. From my own testing, an easy fix is:
CREATE INDEX experiment_id ON ncfiles (experiment);
.Before index creation, a sample query:
Index creation on the full database, post-creation:
Same query, after index creation:
This is a few orders of magnitude faster. Obviously this isn't the most rigorous of tests (filesystem caching etc.) One thing I'm not sure about is the effect on
build_index
The text was updated successfully, but these errors were encountered: