[gdal-dev] very slow sql ogr2ogr conversion

[Neil Walker]

The problem is bcp.

The docs says bcp is only enabled for suitable drivers like native 11.0.

I have this because ssms uses it and its in my odbc list.

However when I try to use it as a driver, ogr2ogr fails. Only the default
driver works, which I presume does not support bcp.

So how can I get it to work when I know the driver is there but ogr refused
to use it?

Thanks.



On Wed, 28 Apr 2021, 08:17 Hector muro, <muro.hector at gmail.com> wrote:

> 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/6299c09e/attachment.html>