topic Re: SQL Server Native Types Pros and Cons in Data Management Questions

SQL Server Native Types Pros and Cons

JoseSousa — Mon, 04 Jul 2011 04:13:40 GMT

Hi guys,

I am trying to search for good articles from Esri about this but couldn't find it. I need to know the advantages and disadvantages of this and how to enable it properly.

I need to know if by having it enabled I can perform standard queries against it and whether it supports a versioned environment with archiving and other features.

Is there any good place to search for this info?

Cheers,
José

Re: SQL Server Native Types Pros and Cons

VinceAngelo — Mon, 04 Jul 2011 18:02:22 GMT

What version of ArcGIS are you using? The documentation has plenty of content at 9.3.1
and 10.0, which is roughly where support was introduced.

It would be unusual for Esri to publish "pros and cons" since the definition of each is subject
to unique interpretation. A search of these forums on "SQL Server geometry geography"
brings up a number of real-world issues to consider.

- V

Re: SQL Server Native Types Pros and Cons

JoseSousa — Mon, 04 Jul 2011 21:43:42 GMT

Hi Vince,

Thanks for replying. I am using ArcGIS 10. The documentation is very rich on content about the QueryLayer but it does not provide much details about the capabilities of the Native Types within the Esri GIS software.

In the end I still do not know, by reading the Esri documentation, if this is better or worst than using the SDE binaries or other approach. The only thing I know is that Esri supports it. This does not provide much guidance or help on this subject as you might understand.

If Esri had already some general testings and some recommendations of when to use it and why, it would be helpful for the users as I would avoid having to perform these testings by myself and having to discover the issues that can be derived from the choice of using Native Types.

For instance, in your opinion, if you had to create a basic web site with data that is versioned (10 layers), using Geometric Networks and just with read-only capabilities would you choose SDE binaries or Natives Types?

Regards,
José

Re: SQL Server Native Types Pros and Cons

VinceAngelo — Mon, 04 Jul 2011 22:16:42 GMT

It's not the role of Esri documentation to tell how to use native geometry types, just how
they integrate with Esri applications. Since the potential of Spatial Types and Functions
is near infinite, it's not really possible for even the database vendors to document the
possibilities.

Esri maintains a vendor-neutral stance. They partner with all the database vendors, and
really can't delve into "better or worse" evaluations without compromising that neutrality
(and/or the terms of the partner agreements).

The reason customers should evaluate for themselves is simple: Every deployment is
different. If an evaluation requires 13 pages of footnotes for 4 pages of content, and
is not indicative of performance elsewhere, why publish it?

Generally speaking, the reason to use native types is to expliot them with non-Esri clients.
If you have no intention of doing spatial queries outside of ArcGIS, then the only reason
to use native types would be performance, which is pretty easy to evaluate in a prototype
during development.

- V

Re: SQL Server Native Types Pros and Cons

JoseSousa — Mon, 04 Jul 2011 22:20:31 GMT

Thanks Vince.
Jose

Re: SQL Server Native Types Pros and Cons

RobertHu — Thu, 21 Jul 2011 21:09:29 GMT

Jose,

If your system profile is close to what described in this article, you may consider to stay with SDEBinary until Miscrosoft fixes the bug. We feel the pain, and are at the beginning stage of turning some of our layers beck to SDEBINARY.

http://support.esri.com/en/knowledgebase/techarticles/detail/38871

Robert

Re: SQL Server Native Types Pros and Cons

EdKatibah — Mon, 01 Aug 2011 21:50:13 GMT

Regarding the Knowledge Base - Technical Article referred to in the last post: Reduced spatial query performance on multiprocessor servers running SQL Server 2008.

Microsoft has just released a fix for this issue: SQL Server 2008 R2 RTM CU9 Hotfix Build 10.50.1801.0

Please check with Microsoft Support to get this server release.

Thanks,

- Ed

Ed Katibah
Spatial Program Manager
SQL Server Customer Advisory Team (SQLCAT)
Microsoft Corporation

Re: SQL Server Native Types Pros and Cons

ShiraBezalel — Fri, 17 Feb 2012 14:19:09 GMT

If you have no intention of doing spatial queries outside of ArcGIS, then the only reason
to use native types would be performance, which is pretty easy to evaluate in a prototype
during development.
- V

Hi Vince,

Based on this statement, is it fair to say that the native SQL Server geometry type should be faster than SDEBINARY? That was the answer I got from ESRI Tech Support and it makes sense to me since there is no join required to the F_ table. However, we are testing migrating over our dense vector layers from SDEBINARY to GEOMETRY and my performance testing has yielded mixed results. In one case, using SDEBINARY is clearly faster than GEOMETRY. (I've looked at the showplan output and confirmed that the spatial index is getting used in the case of the GEOMETRY feature class so I don't think we're hitting the bug mentioned in this thread.) Now I'm wondering if our understanding that the native SQL Server geometry type is going to yield better performance isn't necessarily true. Any thoughts?

Thanks,
Shira

Re: SQL Server Native Types Pros and Cons

VinceAngelo — Fri, 17 Feb 2012 16:10:19 GMT

I really don't know which "should" be faster. I know the SDEBINARY format and how much
compression to expect relative to a shapefile or ASCII representation for various sizes of
shapes of differing types and coordinate reference parameters, but I'm not familiar with
Microsoft's internal representation the way I am with SDO_GEOMETRY. This is why I
recommended prototyping. There are enough differences between deployments that it's
not really easy to predict how all the pieces will behave with different variables.

- V