SqlGeography spatial operations slow - SQL Server 2016

Question

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;
 }

I abandoned using Spatial types in SQL because of performance. This was back in 2012 though. Have you configured spatial indexes? https://www.simple-talk.com/sql/t-sql-programming/sql-server-spatial-indexes/ — Matt Evans, May 06 '17 at 10:17
My tests show that the new library is a few percent slower than the old one. However, Microsoft have examples where they say that the new library is more than 20 times faster than the old one. How would spatial indexes work when I'm using SqlGeography I C#? — barto90, May 07 '17 at 05:16
@barto90 I suspect there is some confusion about what SqlGeography is? It's not a general-purpose GIS library, just the client-side library you use to represent database data. Just like ints, dates, table types, it doesn't matter how the *client* creates the data. It's the *server* that runs the queries, so yes, indexes *always* matter. *Your* code doesn't show any database access. I suspect you didn't actually measure the *server's* spatial performance — Panagiotis Kanavos, Jun 23 '17 at 16:03
@barto90 it's like talking about database date optimizations when the test only deals with System.DateTime objects instead of actually running queries against tables with date values — Panagiotis Kanavos, Jun 23 '17 at 16:07

score 8 · Accepted Answer · answered Jun 20 '17 at 10:04

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

SqlGeography spatial operations slow - SQL Server 2016

1 Answers1