Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SqlGeography spatial operations slow - SQL Server 2016

I have run some tests on the new spatial library SqlGeography in SQL Server 2016, which according to Microsoft should be a lot faster than the previous versions:

SQL Server 2016 – It Just Runs Faster: Native Spatial Implementation(s). Apply SQL Server 2016 and a breadth of methods and spatial activities are faster and scale better. There are no application or database changes just the SQL Server binary updates showing dramatic improvement.

However, the tests shows that the new library is slower than the old one. I have tested it in C# with the Nugets published by Microsoft, Microsoft.SqlServer.Types. I have tested version 11 against version 14 (SQL Server 2016).

What should I do in order to get the new spatial library to perform better?

The source code for the small test is:

var line1 = CreateLine(56, -4, 58, 16);
var line2 = CreateLine(58, -4, 56, 16);

for (int i = 0; i < 50000; i++)
{
     var intersection = line1.STIntersects(line2);
     var contains = line1.STBuffer(1000).STContains(line1);
}

public static SqlGeography CreateLine(double fromLat, double fromLon, double toLat, double toLon)
{
     SqlGeographyBuilder constructed = new SqlGeographyBuilder();
     constructed.SetSrid(4326);
     constructed.BeginGeography(OpenGisGeographyType.LineString);
     constructed.BeginFigure(fromLat, fromLon);
     constructed.AddLine(toLat, toLon);
     constructed.EndFigure();
     constructed.EndGeography();
     var line = constructed.ConstructedGeography;
     return line;
 }
like image 256
barto90 Avatar asked May 06 '17 09:05

barto90


1 Answers

In this article Microsoft writes that Microsoft.SqlServer.Types is no longer used in T-SQL code in SQL Server 2016. https://blogs.msdn.microsoft.com/psssql/2016/03/03/sql-2016-it-just-runs-faster-native-spatial-implementations/

How it works in SQL 2014:

As the SQL Server Spatial data types have matured we uncovered the Unmanaged (SQL Server) to Managed (Microsoft.SqlServer.Types) to SqlServerSpatial###.dll (unmanaged) transitions (PInvoke and PUnInvoke) may become a scalability bottleneck

In SQL 2016:

(T-SQL) SQL Server 2016 invokes the native implementation of the methods, avoiding the Unmanaged to Managed to Unmanaged transitions, improving performance.

It seems that SQL 2016 uses SqlServerSpatial###.dll directly and perfomance is increased in T-SQL code only

like image 102
Mikhail Lobanov Avatar answered Sep 18 '22 17:09

Mikhail Lobanov