Geometry Datatype – Line String Behavior Enhancements

When I did the session at SQLPass about the 42 tips, I had included an interesting behavior I got when working with Geometry data types. When I was doing a session at a large SI location, one of the application developers walked up to me to say they were getting some unacceptable performance and wanted to know if there was a solution to their problem. I was just lucky here because I seem to be at the right place at that point in time. I promised to visit his desk once I finished my session because that was the very reason why I was there.

Post the session I walked upto his place and saw that he was playing around with Geometry datatype and that took me by surprise. I was not aware that they were using the same and I was pleasantly surprised because it was going to be a learning experience for me too. I watched at their code and then later said would like to get a repro and revert back. The code was some 500 lines of the geometry datatype that they were building and it was taking some ridiculous 26 seconds to query.

Geometry Datatype – Line String Behavior Enhancements spatial-line-trace-01

The code was something like:

Solarwinds
DECLARE @Shape GEOMETRY =
'LINESTRING (69780.797425 956010.563079, 69669.780371 955901.565027, 69670.
789364 956011.572091, 69890.805466 956009.554067, 69779.788432 955900.556035,
-- removed some 12 pages of points
305638, 64548.030118 973221.25711, 63783.027513 973778.383136, 62897.892436
62897.892436)'
SELECT @Shape

Since this was a new domain for me, I wrote back to a few friends in Microsoft to check if there was anything I need to aware rather than staring at the 12 pages of points data. After about a day, one of the friends said – there was some optimization that was enabled and this was a known issue. I was pointed to the KB article which was a big sigh of relief. So I pressed to ask for the resolution. I was told:

  • SQL Server 2012 SP2 CU6
  • SQL Server 2014 CU8
  • SQL Server 2014 SP1 CU1
  • SQL Server 2016 CTP3 – where I ran this

We can optimize this by enabling a trace flag of T6534. As soon as I did the same. The same query returned in sub-seconds.

Geometry Datatype – Line String Behavior Enhancements spatial-line-trace-02

It was a great learning for me too, and wrote back to the DBA showing the same. I used the below method to enable the trace flag as the service start parameter.

Geometry Datatype – Line String Behavior Enhancements spatial-line-trace-03

As I try to wrap up this post, I am interested in knowing how many of you are using a Geometry database in your environments? What are some of the use cases of using them? Have you ever encountered this Trace flag before or have you enabled the same?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Script: Remove Spaces in Column Name in All Tables
Next Post
SQL SERVER – Unable to Start SQL Browser

Related Posts

No results found

2 Comments. Leave new

  • Those of us in the GIS field use it a lot. The geometry data type is a good alternative to the proprietary binary type offered by Esri and the other major GIS vendors. Thanks for posting this useful hint!

    Reply
  • I would be curious to have that LINESTRING text to test with my Sql Server instance. I generated a very long LINESTRING (over 398K characters) and ran the query along the lines of the one you posted and got sub-second response without CU 8 or Trace Flag 6534. It is a Sql Server Express (used for testing) 2012 64-bit (11.0.5058.0) running on a very mediocre Dell Workstation running Windows 7.1 with 8 GB of RAM.

    Was the issue you described specific to SS 2014?

    Thanks again for posting an article on SS Spatial data types. I see so few recent posts outside of the GIS realm (there are some notable exceptions). I hope that more people start taking advantage of the geometry and geography data types.

    Reply

Leave a Reply

Menu