I am wondering if there is a known difference in processing speed in ArcGIS between the three geometry data types in SQL Server: compressed binary, SQL Server geometry, and SQL Server geography.

We have two SDEs in questioning on this issue

  • The first, and older, SDE has feature classes using Compressed Binary and showing the Geometry Property - "Storage: High Precision using Esri Binary spatial Type."
  • The second SDE, which was recently created, is almost an exact copy of the first SDE expect that the feature classes use SQL Server Geography and show the Geometry Property - "Storage: High Precision using Geometry spatial type (SRID 2278).

The only other difference's between the two SDE's is that the second SDE is in a new schema and domain tables were added.

Our users are reporting that the new SDE is running much slower than our original SDE we used to use while the feature classes are being edited.

Seeing as there could be many reasons as to why this is I figured I would start with the Geometry Properties because I did notice they were different between the two SDE's and as far as I know these two different storage types utilize spatial indexing in a different way which could impact performance.

Does anyone have any advice before we continue troubleshooting?

  • 1
    I would start with the spatial index. Assuming that you are on 2012+ make sure you are using the auto grid option and that you bounds are set tightly around your data. I have seen instances of ESRI creating indexes in SQL server with extreme bounds that effectively make the index redundant. – MickyT Nov 9 '16 at 19:39
  • @MickyT I will make note of your recommendation and will check our bounds when we meet with our Senior Systems Analyst tomorrow. – HiMyNameIzMatt Nov 9 '16 at 19:48
  • 1
    Your question is about geometry storage types but the title says "data types". Please edit the question to clarify this, and to specify the exact versions of both SQL Server and ArcGIS. Note that this is the subject of one of the longer discussion threads on GeoNet, and the answer is generally "Under optimal circumstances, there shouldn't be significant differences, but there is high variability in data and tuning which can influence user experience." – Vince Nov 9 '16 at 21:15