[gdal-dev] mssql import speed

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] mssql import speed

Martin Landa
Hi,

I wonder how to speed up import to MSSQL. I have testing SQLite DB
(~1GB) with 48 tables (overall number of records 11e6). I started with

$ ogr2ogr -f MSSQLSpatial MSSQL:... test.db

It took more than 3hours! Tested on Windows computer with SQL Server
2012. I used SQL Server Native Client 11.0.

So I tried to enable BCP (which should be enable anyway) and increased its size:

$ ogr2ogr -f MSSQLSpatial MSSQL:... --config MSSQLSPATIAL_USE_BCP YES
--config MSSQLSPATIAL_BCP_SIZE 10000 test.db

No difference, more than 3 hours. Anything I could miss?  It's my
first experience with MSSQL. Anything related to configuration or so?
Thanks for pointers in advance! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Tamas Szekeres
Hi Martin,

Try to check in the SQL profiler that the BCP was successfully enabled and not plain insert statements are executed. (ie. the BCP enabled plugin was loaded)
If BCP was enabled you might also try to load data on the server machine, so the network doesn't limit the bandwidth.

Otherwise we don't seem to have more options.

Best regards,

Tamas


2018-05-10 19:18 GMT+02:00 Martin Landa <[hidden email]>:
Hi,

I wonder how to speed up import to MSSQL. I have testing SQLite DB
(~1GB) with 48 tables (overall number of records 11e6). I started with

$ ogr2ogr -f MSSQLSpatial MSSQL:... test.db

It took more than 3hours! Tested on Windows computer with SQL Server
2012. I used SQL Server Native Client 11.0.

So I tried to enable BCP (which should be enable anyway) and increased its size:

$ ogr2ogr -f MSSQLSpatial MSSQL:... --config MSSQLSPATIAL_USE_BCP YES
--config MSSQLSPATIAL_BCP_SIZE 10000 test.db

No difference, more than 3 hours. Anything I could miss?  It's my
first experience with MSSQL. Anything related to configuration or so?
Thanks for pointers in advance! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
In reply to this post by Martin Landa
Hi,

2018-05-10 19:18 GMT+02:00 Martin Landa <[hidden email]>:
> It took more than 3hours! Tested on Windows computer with SQL Server
> 2012. I used SQL Server Native Client 11.0.

sorry, typo, I have SQL Server 2014. Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
Hi,

2018-05-11 9:10 GMT+02:00 Martin Landa <[hidden email]>:

I missed on testing Windows server to install gdal-dev-mss package.
But unfortunately, no improvement. BCP is still not working in my
case. I tried all available drivers, same behaviour, I am getting to
be lost. Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
Hi,

2018-05-11 23:15 GMT+02:00 Martin Landa <[hidden email]>:
> I missed on testing Windows server to install gdal-dev-mss package.
> But unfortunately, no improvement. BCP is still not working in my

oh, sorry for noise, I forgot to define

 --config GDAL_DRIVER_PATH C:\OSGeo4W64\apps\gdal-dev\bin\gdalplugins

In progress, Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
2018-05-11 23:36 GMT+02:00 Martin Landa <[hidden email]>:
>  --config GDAL_DRIVER_PATH C:\OSGeo4W64\apps\gdal-dev\bin\gdalplugins
>
> In progress, Ma

works like a charm, from 3 hours to 3 minutes! Thanks for packaging
gdal-mss :-) Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
Hi,

2018-05-11 23:51 GMT+02:00 Martin Landa <[hidden email]>:
>>  --config GDAL_DRIVER_PATH C:\OSGeo4W64\apps\gdal-dev\bin\gdalplugins
>>
>> In progress, Ma
>
> works like a charm, from 3 hours to 3 minutes! Thanks for packaging
> gdal-mss :-) Ma

well, after solving BCP issue, I have another, on empty DB the command

C:\OSGeo4W64/apps/gdal-dev/bin/ogr2ogr -f MSSQLSpatial --config
GDAL_DRIVER_PATH C:\OSGeo4W64\apps\gdal-dev\bin\gdalplugins --config
MSSQLSPATIAL_LIST_ALL_TABLES YES %connstr% %filedb%

fails with

ERROR 1: Failed to set identity insert on layer, [Microsoft][SQL Server Native C
lient 11.0][SQL Server]IDENTITY_INSERT is already ON for table 'kn2.dbo.par'. Ca
nnot perform SET operation for table 'dbo.bud'..
ERROR 1: Unable to write feature 1 from layer BUD.
ERROR 1: Terminating translation prematurely after failed
translation of layer BUD (use -skipfailures to skip errors)

Any idea would could be wrong? Thanks! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
Hi,

2018-05-12 15:51 GMT+02:00 Martin Landa <[hidden email]>:

> ERROR 1: Failed to set identity insert on layer, [Microsoft][SQL Server Native C
> lient 11.0][SQL Server]IDENTITY_INSERT is already ON for table 'kn2.dbo.par'. Ca
> nnot perform SET operation for table 'dbo.bud'..
> ERROR 1: Unable to write feature 1 from layer BUD.
> ERROR 1: Terminating translation prematurely after failed
> translation of layer BUD (use -skipfailures to skip errors)

Input DB has about 50 layers, see

...
1: PAR (None)
2: BUD (None)
3: ZPOCHN (None)
...

It seems that all records from PAR layer are sucessfully writen to
target PAR table in MSSQL. Writing features from second layer (BUD)
fails. No records written. It must be related to BCP. Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Tamas Szekeres
Hi Martin,

Can I get the sample data for this? 
Looks like more than one table is set "IDENTITY_INSERT ON" at the same time.

Best regards,

Tamas


2018-05-12 16:10 GMT+02:00 Martin Landa <[hidden email]>:
Hi,

2018-05-12 15:51 GMT+02:00 Martin Landa <[hidden email]>:

> ERROR 1: Failed to set identity insert on layer, [Microsoft][SQL Server Native C
> lient 11.0][SQL Server]IDENTITY_INSERT is already ON for table 'kn2.dbo.par'. Ca
> nnot perform SET operation for table 'dbo.bud'..
> ERROR 1: Unable to write feature 1 from layer BUD.
> ERROR 1: Terminating translation prematurely after failed
> translation of layer BUD (use -skipfailures to skip errors)

Input DB has about 50 layers, see

...
1: PAR (None)
2: BUD (None)
3: ZPOCHN (None)
...

It seems that all records from PAR layer are sucessfully writen to
target PAR table in MSSQL. Writing features from second layer (BUD)
fails. No records written. It must be related to BCP. Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Martin Landa
Hi,

2018-05-12 16:17 GMT+02:00 Tamas Szekeres <[hidden email]>:
> Can I get the sample data for this?

sure [1].

> Looks like more than one table is set "IDENTITY_INSERT ON" at the same time.

Steps to reproduce (tested with SQL Server 2014):

1) create new mssql db
2) C:\OSGeo4W64/apps/gdal-dev/bin/ogr2ogr -f MSSQLSpatial --config
GDAL_DRIVER_PATH C:\OSGeo4W64\apps\gdal-dev\bin\gdalplugins --config
MSSQLSPATIAL_LIST_ALL_TABLES YES %connstr% %filedb%

Thanks! Martin

[1] http://geo102.fsv.cvut.cz/~landa/tmp/Export_vse.db.7z

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql import speed

Tamas Szekeres
Hi Martin,

Thanks for the data.
Fixed this one in this PR:


Best regards,

Tamas

2018-05-12 21:59 GMT+02:00 Martin Landa <[hidden email]>:
Hi,

2018-05-12 16:17 GMT+02:00 Tamas Szekeres <[hidden email]>:
> Can I get the sample data for this?

sure [1].

> Looks like more than one table is set "IDENTITY_INSERT ON" at the same time.

Steps to reproduce (tested with SQL Server 2014):

1) create new mssql db
2) C:\OSGeo4W64/apps/gdal-dev/bin/ogr2ogr -f MSSQLSpatial --config
GDAL_DRIVER_PATH C:\OSGeo4W64\apps\gdal-dev\bin\gdalplugins --config
MSSQLSPATIAL_LIST_ALL_TABLES YES %connstr% %filedb%

Thanks! Martin

[1] http://geo102.fsv.cvut.cz/~landa/tmp/Export_vse.db.7z


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev