Re: [gdal-dev] mssql import speed

[Tamas Szekeres]

Hi Martin,

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

https://github.com/OSGeo/gdal/pull/621

Best regards,

Tamas

2018-05-12 21:59 GMT+02:00 Martin Landa :

> Hi,
>
> 2018-05-12 16:17 GMT+02:00 Tamas Szekeres :
> > 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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

Hi,

2018-05-12 16:17 GMT+02:00 Tamas Szekeres :
> 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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] 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 :

> Hi,
>
> 2018-05-12 15:51 GMT+02:00 Martin Landa :
>
> > 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
> gdal-dev@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

Hi,

2018-05-12 15:51 GMT+02:00 Martin Landa :

> 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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

Hi,

2018-05-11 23:51 GMT+02:00 Martin Landa :
>>  --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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

2018-05-11 23:36 GMT+02:00 Martin Landa :
>  --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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

Hi,

2018-05-11 23:15 GMT+02:00 Martin Landa :
> 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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

Hi,

2018-05-11 9:10 GMT+02:00 Martin Landa :

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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] mssql import speed

[Martin Landa]

Hi,

2018-05-10 19:18 GMT+02:00 Martin Landa :
> 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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] 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 :

> 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 1 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
> gdal-dev@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

[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 1 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
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev