Sunday, May 15, 2016

OGR MSSQL bulk insert support added in GDAL 2.1

With the release of GDAL 2.1.0 the new bulk insert support has been added to the OGR MSSQL Spatial driver. Using the bulk insert option the upload is approx. 20 times faster comparing to the earlier driver versions, which is especially useful when uploading large data tables to MSSQL server.

The bulk insert option involved the need to implement the write support for the native SqlGeometry/SqlGeography serialization formats, which in turn eliminates the need of the server side geometry conversions from WKB and WKT causing large amount of memory pressure at the server and may cause issues like this one.

The implemented bulk insert support requires the SQL Server Native Client 10+ driver to be installed on the system. This is installed along with a SQL server setup or available from a separate package provided by Microsoft. This requirement is a bit unpleasant for the binary distribution providers, because the driver should link against the corresponding sqlncli dll. Therefore we added support to build the MSSQL spatial driver as a plugin dll (ogr_MSSQLSpatial.dll). The gdal-core msi installers (development, daily) at GISInternals have been modified to include the bulk insert enabled MSSQL driver as an optional component (not installed by default). If you enable this component, make sure that the SQL Server Native Client 11 driver is also installed on your the system, otherwise ogr2ogr will likely fail to start.



By installing this driver, the bulk insert option is enabled by default when using MSSQLSpatial as an ogr2ogr target database, however we can disable bulk insert by using the MSSQLSPATIAL_USE_BCP=FALSE config option which turns the upload to a set of "INSERT INTO" operations as usual, but that option continues to use the native geometry/geography serialization formats. We could also select the earlier WKT/WKB upload by using the UPLOAD_GEOM_FORMAT layer creation option. For more information about the config and layer creation options supported by the driver please refer to the MSSQL Spatial driver information page.

6 comments:

  1. Firstly thanks for all the great work, especially on the MS SQL.

    I've just updated to 2.0 and I'm getting a bcp error about invalid table name when I try an import that previously worked. The MapInfo layer name was "Borough Wards". If I use the -nln "Wards" option, data loads fine

    ReplyDelete
    Replies
    1. I've just fixed this one in GDAL trunk and branch 2.1 within the scope of this bug: https://trac.osgeo.org/gdal/ticket/6527

      Delete
  2. Thanks for the (extremely) quick response

    No sure where to get this ... is this in the Stable branches daily list at http://www.gisinternals.com/stable.php

    ReplyDelete
  3. Heads-up: as per Microsoft at https://msdn.microsoft.com/en-us/library/ms131321.aspx support for the SQL Server Native Client is not avauilable for SQL Server 2016.

    ReplyDelete
    Replies
    1. As far as the recent "official" ODBC drivers will support the bcp operations (without significant issues) we are good to go.

      Delete
    2. As far as the recent "official" ODBC drivers will support the bcp operations (without significant issues) we are good to go.

      Delete