ogr2ogr WFS data source really slow when upgrading from GDAL 1.11.3 to 2.1.2

Question

I'm using ogr2ogr to download features from a WFS service and save it to a sqlite database. Here's the full command:

ogr2ogr -f SQLite -gt 65536 -nln my_table_name --config OGR_WFS_USE_STREAMING NO --config OGR_SQLITE_CACHE 4096 -nlt POINT -skipfailures -append my_sqlite_file.sqlite "http://url/to/my/service"

When I run this on GDAL 2.1.2, downloading the features is extremely slow.

By the look of it, the my_sqlite_file.sqlite_journal file never grows to more than 10K and the table is constantly updated. In comparison, when using GDAL 1.11.3, my_sqlite_file.sqlite_journal grows to the excess of 400K and there are maybe 2-3 writes tops to the table.

I was able to pinpoint this problem down to the --config OGR_WFS_USE_STREAMING NO part: when I remove it from the command, I achieve similar behaviour and speeds with GDAL 1.11.3.

With the dataset I'm using, the download time grows from 30 seconds to 8 minutes.

Did something change between the two versions of the ogr2ogr?

Should I pass the --config OGR_WFS_USE_STREAMING NO part of the command somehow differently?

Would love to find a solution to this.

---

I experimented a bit more, still to no avail but at least I know more.

I removed all the -skipfailures, -gt and all the config options but still ogr2ogr supplied with GDAL 1.11.3 is much faster than the 2.1.x (tried 2.1.2 and 2.1.3). Looking at the memory and processor usage on windows here's what's going on.

GDAL 1.11.x:

• data download starts, ogr2ogr never exceeds 25MB of memory, CPU usage stays around 10%
• ogr2ogr performs the writes to the sqlite database, more writes when -gt 65536 eliminated, but still fairly quickly

GDAL 2.1.X

• data download starts, ogr2ogr downloads all the data at once and takes around 350MB of memory, CPU usage goes all the way to 50%
• then it hangs there performing really slow writes. If limit set to -gt 65536, then will perform first write after around 10 mins from the start

Really quite clueless to what to do here. I think the only thing I can do is file a bug report.

---

Using user30184's pointer on the debug I can see some differences in the process.

---

First of all the older version of ogr2ogr clearly states that it is doing a WFS request, but the newer version claims it's doing an HTTP request with both reporting respectively that they OGROpen succeeded in in a WFS request and an HTTP request.

Secondly, when starting the download the new version of ogr2ogr displays the following lines

SQLITE: dropping trigger ggi_mylayername_GEOMETRY

SQLITE: dropping trigger tmi_mylayername_GEOMETRY

and when I control-c the process it prints out the lines

SQLITE: restoring trigger ggi_mylayername_GEOMETRY

SQLITE: restoring trigger tmi_mylayername_GEOMETRY

all of these trigger lines were absent from the older version of ogr2ogr.

---

Would any of these have an effect on the sluggishness I'm experiencing with the new version?

I wonder if it loads the data in the wrong format (not WFS) and then the conversion is what is taking time and CPU power?

Answer 1

Using the debug suggestion from @user30184 the problem was that the new version of ogr2ogr needs to be told the format of the data at the very beginning of the URL. When the older version was completely happy to with just

http://urltosomewhere.com/service?SERVICE=WFS

and figures out the WFS part from the end, starting from version 1.8.0 GDAL/OGR needs the be given the URL in the format

WFS:http://urltosomewhere.com/service?SERVICE=WFS

I wish the documentation was a bit better organized. I only discovered this by random googling and ending up on this page and seeing the correct format at the top of the page.