[gdal-dev] very slow sql ogr2ogr conversion

[Hector muro]

Hi Neil,

I found a similar situation once. Are you using Linux? If so, I think the
ogr2ogr MSSQLSpatial driver for Linux does not use BCP (for multiple
inserts, which is why the CSV goes so fast).

If not, have you tried using the MSSQLSpatial Configuration Options for the
command? I don't remember exactly what they are but it's well documented.
It was something on the lines of "MSSQLSPATIAL_USE_BCP" and
"MSSQLSPATIAL_BCP_SIZE"

Another thing I found useful was to check the SSMS Profiler to see what's
going on as you run.

Finally, I was forced by circumstances to use SQL Server, but if that's not
your case, I strongly recommend you to get away from it :)

Hope this helps,

Hector


On Tue, 27 Apr 2021, 19:47 Neil Walker, <neil.walker.1973 at gmail.com> wrote:

> Hello,
>
> First time user, I hope this is the right mailing list :)
>
>
>
> I have a fairly small (37mb - 160,000 linestrings) shp file and it loads
> into QGIS in a couple of seconds. I want to import this into sql server, so
> used the following ogr2ogr command:
>
>
>
> ogr2ogr -f "MSSQLSpatial"
> "MSSQL:server=myserver;database=mydatabase;Trusted_Connection=True;"
> "NZ_RoadLink.shp" -a_srs "EPSG:27700" -nln "neilshape" -progress -lco
> UPLOAD_GEOM_FORMAT=wkt
>
>
>
> It's UK Ordnance Survey open roads data if anyone is interested. After 40
> minutes it got to 3% complete then I gave up.
>
>
> So I instead saved it as a CSV and it took about 1 minute, I then imported
> this CSV into sql server and that took 1 minute too.
>
>
>
> I then ran the following command and it worked ok too after a few seconds,
> I don't know if it's right or not but looks ok...
>
>
>
> alter table add geom GEOMETRY
>
> update table set geom=replace(wkt,'Z','')
>
>
>
> So, can anyone let me know what could be causing this massive slowdown
> because my database connection is fast and there are no network issues. I
> have tried using all the available sql server drivers (SQL Server, default
> ODBC) but it's still this slow. I actually also have 'sql server native
> 11.0' but ogr2ogr refused to use this.
>
>
> If nobody has a clue can you confirm if my removal of 'Z' in this command
> is ok or what a better way is?
>
>
> Also, I don't know if it's just the way it is but I kept getting 'invalid
> database object mydatabase' until I entered the UPLOAD_GEOM_FORMAT=wkt
> parameter. Clearly 'mydatabase' has nothing to do with the error...
>
>
> Thanks.
>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20210428/e33cd8c9/attachment.html>