Performance difference on windows compared to sqlite.exe from sqlite.org

[theroggy]

Comment:

I noticed a significant performance difference when running a script using the conda windows build compared to the binaries available on sqlite.org.

The last statement in the script below takes about double the time: 31 seconds compared to 18 seconds.

Could there be a difference in the compilation parameters that explains this difference?

-- Create table for bboxes
CREATE TABLE bboxes (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  minx FLOAT,
  maxx FLOAT,
  miny FLOAT,
  maxy FLOAT
);

-- Fill the table with 1 million bboxes
INSERT INTO bboxes(minx, maxx, miny, maxy)
  WITH RECURSIVE
    cnt(x) AS (
       SELECT 0
       UNION ALL
       SELECT x+10 FROM cnt
        LIMIT 1000
    )
  SELECT minx, maxx, miny, maxy 
    FROM (SELECT x AS minx, x+10 AS maxx FROM cnt) x,
         (SELECT x AS miny, x+10 AS maxy FROM cnt) y;

-- Create and fill up rtree.
-- Takes +- 31 secs using conda sqlite.exe, +- 18 secs with version downloaded from sqlite.org
CREATE VIRTUAL TABLE bboxes_rtree USING rtree(id, minx, maxx, miny, maxy);
INSERT INTO bboxes_rtree
  SELECT id, minx, maxx, miny, maxy FROM bboxes;

Reference to OSGeo/gdal#7614

[rouault]

Looking at https://github.com/conda-forge/sqlite-feedstock/blob/main/recipe/bld.bat , I don't see any /O2 option passed to "cl", so unless some magic happens, the Windows builds are not optimized.

[theroggy]

On windows using the conda-forge version it now also takes 18 seconds...

Thanks!!!