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.

32 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
  4. With the invention of smartphones, watching movies and tv shows have become so easy.
    We no longer have to wait for the shows to be telecasted on tv.
    Instead we can just download some great apps like Playbox, which provides free streaming of videos and tv shows.
    It is becoming very popular day by day and you must check it out.
    PlayBox HD Apk Download

    ReplyDelete
  5. We all live to play games on our smartphones.
    But what bugs the most is the ads those games contain and the in-app purchase option we have to go through to get the full version of those games.
    Freedom is such a unique application which frees you from these troubles. Want to know more about the Freedom app? Check out this website Download Freedom Apk Now

    ReplyDelete
  6. you can also download Freedom apk latest version
    from our site for more fun

    ReplyDelete
  7. The 2020 Masters Tournament will be watch Masters live Tournament and the first of golf's four major championships to be held in 2020.

    ReplyDelete
  8. It is even a good way for the fan to enjoy the Super Bowl 2020 Live Stream game even if you do not wish to opt for any contract cable. Not only Super Bowl live, but you can also enjoy the different sporting events on the television. You simply need a good connection along with the Sling Blue Package.

    ReplyDelete
  9. The 2020 Summer Olympics, officially known as the Games of Field hockey 2020 Summer Olympics and commonly known as Tokyo 2020 or the Recovery Olympics.

    ReplyDelete
  10. Thanks for sharing Such a valuable post and for more information. please follow the link- Virtual reality development companies

    ReplyDelete
  11. 1 World Figure Skating Championships 2020
    2 AGE AND MINIMUM TEST REQUIREMENTS.
    3 QUALIFYING:
    4 World Figure Skating Championships 2020 Live Stream Online
    5 World Figure Skating Championships 2020 Live on Smartphone
    6 World Figure Skating Championships 2020 Live on Cable
    7 World Figure Skating Championships 2020 Live without Cable
    8 World Figure Skating Championships 2020 Live on Playstation
    Click To Watch Now
    World Junior Figure Skating Championships 2020 Live Stream

    ReplyDelete
  12. Wilder vs Fury 2 Live Stream Rematch PPV Online
    Wilder vs Fury 2 Live After February 22nd there will be no more unanswered questions. I will finish what I started, and this time @Tyson_Fury will not be getting up off that canvas so quickly. I’ve proven myself time and time again and I will do it again in February.Wilder and Fury first met in December 2018, and the fight ended in a split draw. Since then, both Wilder and Fury have won two consecutive fights. Then Wilder vs Fury will face Fury in a rematch on February 22, 2020 at the MGM Grand in Las Vegas. Joshua fought in Saudi Arabia last month,

    ReplyDelete
  13. Masters Golf 2020 Live Stream Online. The 2020 Masters Tournament will be the 84th edition of the Masters Tournament and the first of golf's four major championships to be held in 2020. The Masters 2020 Live Stream. It will be held from April 9–12 at Augusta National Golf Club in Augusta, Georgia. United States.Masters 2020 Live Online.

    More: https://masters2020info.com/

    XFL 2020 Live Stream. XFL Football Game Start At 6 February. XFL 2020 Live Includes former XFL defenders, New York Guardians, St. Louis Battles, and Tampa Bay Vipers. The Dallas Renegades, Houston Roughnecks, Los Angeles Wildcats and Dragons matched the XFL West. - XFL 2020 Live Stream American Football Online with our XFL live stream guide.

    Website: https://xfl2020s.com/

    Watch UFC 249 Live Stream Online Without Cable UFC 249 Live Stream online Free 92nd UFC Fight. After more than a decade of having previously held the UFC Fight ceremonies at least at the end of February, the 92 UFC Fight will be held on February 9, 2020. And will be issued at the right time . So, if you don’t want to miss any past of the UFC 249, you must prepare for April 18, 2020.

    more:https://watchufc249.com/

     

    ReplyDelete
  14. https://ufc249fights.com/
    https://themaster2020s.com/
    https://master2020sgolf.com/

    ReplyDelete
  15. You can watch players Championship live on Sky Sports Golf. Specific times will be updated prior to the event.

    ReplyDelete
  16. Miami Masters Live Streaming https://miami--open.com/ You can catch all the tennis action in 2020 Miami, including the ATP Miami Masters live streaming.

    ReplyDelete

  17. Watch March Madness Live to see every NCAA live stream of tournament games from the First Four to the NCAA Final Four in Atlanta.

    ReplyDelete
  18. Hi there. I found your website by way of Google whilst looking for a related subject, your web site came up. It seems to be great. I have bookmarked it in my google bookmarks to come back then.

    Penn State Football Live Stream

    ReplyDelete
  19. Very nice info and straight to the point. I am not sure if this is really the best place to ask but do you people have any thoughts on where to employ some professional writers? Thanks in advance

    Notre Dame Football Live stream

    ReplyDelete
  20. If you are looking for everything about the Alabama College Football 2020 season, you are in the right place. This guide covers all there is to know about Crimson Tide. You will also get a look into the impressive records and achievements of the team.
    Alabama Football Live

    ReplyDelete
  21. Magnificent website. Plenty of helpful info here.
    I am sending it to a few buddies ans additionally sharing in delicious.
    And certainly, thanks for your effort

    Auburn Tigers Football Live stream

    ReplyDelete
  22. Hello! Would you mind if I share your blog with my zynga group? There’s a lot of people that I think would really enjoy your content. Please let me know. Thank you

    Michigan State Football Live Stream

    ReplyDelete
  23. Nebraska drives 29-17-3. In Lincoln, Nebraska drives 14-5-1. Iowa as of now has a 4 game series of wins. In a year ago's gathering, Iowa won 31-28 in Iowa City. Nebraska Football streaming will be broadcast on BTN. Kevin Kugler will do the detailed breakdown, with Matt Millen as the examination and Lisa Byington uninvolved. It can likewise be gushed on the Fox Sports App with your link or dish sign in.

    ReplyDelete
  24. Utah Utes football is one of the foremost football programs in the United States.This is a guide on how to watch Utah Football live streaming with and without cable. Get all the information you need regarding how to watch Utah College Football here.
    Utah Football Live Stream

    ReplyDelete
  25. Behind This Modern World, There Is Another World That Is Called Internet World. If You Want To Watch Kentucky Derby 2020 Online Without A Cable, You Have To Find The Online Accessible Channels.

    ReplyDelete
  26. Other stations could be streaming the game live. This means that there are different options available to you if you want to watch Master live. In the same way, if you want to watch from other parts of the world, various options are still available to you.

    ReplyDelete
  27. Masters 2020 Live is one of the four biggest national golf competition that takes place in the country. This is also the first of such big competition and it always takes place around April of every year.

    ReplyDelete