topic Re: Converting from SDE Views to Regular SQL Views - Any Gotchas? in Data Management Questions

Converting from SDE Views to Regular SQL Views - Any Gotchas?

RandyKreuziger — Fri, 21 Feb 2014 20:59:33 GMT

We are upgrading from SQL 2008 / ArcSDE 10.0 to SQL 2012 / ArcSDE 10.1. Since we are moving to a new server at the same time I'd like to recreate the SDE Views as SQL Views.

Are there any gotchas I need to look out for?

For MXDs can we simply change the SDE database through the ArcCatalog Set Data Sources function?

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Fri, 21 Feb 2014 21:44:19 GMT

There is no difference between an "SDE view" (which never really existed, anyway)
and an "SQL view". A view is a view -- It's always been a database object, created
and managed by the database. If there was any difference, it was in the creation
process -- back before databases supported geometry extensions, the 'create_view'
operation had to populate metadata and create extra views to support the F and S
tables. The result was still a SQL view. Modern best practice is to use SQL to create
views (keeping in mind ArcGIS requirements), and register the views afterwards,
but this has been best practice since the 'sdelayer -o register' option was added
back in 8.x days.

- V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

TrevorHart1 — Mon, 10 Mar 2014 21:45:30 GMT

Actually there are performance implications of native views over SDE created views, see these links;

http://gdbgeek.wordpress.com/2014/01/15/feature-class-vs-query-layer-vs-spatial-view/
http://gdbgeek.wordpress.com/2014/02/11/feature-class-vs-query-layer-vs-spatial-view-again/

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Mon, 10 Mar 2014 23:45:22 GMT

Unfortunately, you've conflated the concept of spatial views and Query Layers,
when they are in fact orthagonal. ArcSDE registered tables *and* views are
faster than Query Layers on the *same* tables/views because the layer metadata
allows for more efficient use. This has nothing to do with the methodology of
how the table or view is registered with the geodatabase.

ArcSDE has supported registration of tables and views since it supported native
and/or ST_GEOMETRY types in each database.

- V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

MarcoBoeringa — Tue, 11 Mar 2014 06:27:27 GMT

Another point is, is that by design, hitting the ArcCatalog Preview button, retrieves the entire dataset, as it defaults to full extent. So it will take a long time whatever way the data in the RDBMS is registered or stored if the accessed dataset is large or the defined SQL query to access it complex.

It also remains unclear in the posts if the faster response times accessing Query Layers when adding the data through the Add Data button as per this link, isn't caused by simply selecting "Use Spatial Reference extent" as the default extent for guessing a suitable extent for a layer that isn't registered with ArcSDE nor the Geodatabase, which would circumvent the need to plough through the table to calculate an extent on the fly.

I also wonder at what stage some sort of caching on the local machine might play a role when accessing any of these layers.

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

NateArnold — Tue, 11 Mar 2014 14:26:56 GMT

Vince,
From an end user perspective, I think ESRI has done a terrible job of clarifying differences between spatial views and query layers:

When you use SSMS to make a "view that includes a spatial column", why would it not be interpreted as a "spatial view" (confusion in terms)?

When the CreateDatabaseView_management tool was introduced, why would an end user not think "I can use this tool to make a spatial view", when it actually gets interpreted in ArcGIS as a query layer?

Why can views created using SSMS or CreateDatabaseView_management not be registered with the GDB (and hence store feature type, extents, description/metadata, etc.)?

ArcGIS 10.1+ has seen much of the command line tools become GP/GUI tasks, but creating actual spatial views is one are where ESRI needs to focus. Since there is indeed a speed difference between spatial views and query layers, but views are much easier to manage using SSMS, our approach has been to make a "dummy" spatial view using command line, then modify the view definition in SSMS.

We've also seen a speed difference between spatial views built on feature classes stored in the default Geometry spatial type (slower) versus SDEBINARY (faster).

Nate

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Tue, 11 Mar 2014 15:27:28 GMT

When you use SSMS to make a "view that includes a spatial column", why would it not be interpreted as a "spatial view" (confusion in terms)?

It certainly is a spatial view, just not a registered spatial view.

When the CreateDatabaseView_management tool was introduced, why would an end user not think "I can use this tool to make a spatial view", when it actually gets interpreted in ArcGIS as a query layer?

It does create a spatial view, but not a registered spatial view (and is documented as such --
Bullet #3 Views created in enterprise geodatabases using this tool are not registered with
the geodatabase).

Why can views created using SSMS or CreateDatabaseView_management not be registered with the GDB (and hence store feature type, extents, description/metadata, etc.)?

They certainly can be registered with an enterprise geodatabase, if you a) have an enterprise
geodatabase, and b) choose to do so.

ArcGIS 10.1+ has seen much of the command line tools become GP/GUI tasks, but creating actual spatial views is one are where ESRI needs to focus.

I would say the next focus should be on registering views, not creating them.

Since there is indeed a speed difference between spatial views and query layers,

Wait right there -- This is the key misunderstanding. Query Layers are a way of accessing
spatial databases without ArcSDE. There is no correlation between views and
Query Layers, other than that they need to be spatial to be rendered. You can, in fact,
access a table or view as a Query Layer, even if it has been registered with an enterprise
geodatabase (ArcSDE). You cannot use ArcSDE tools against a table or view which hasn't
been registered. The fact Query Layers behave differently due to a lack of metadata has
nothing to do with whether a view is involved, and everything to do with the lack of metadata.
Yet only ArcSDE provides that metadata. I see no way for Esri to address performance issues
that can only be managed by the optimizer.

but views are much easier to manage using SSMS, our approach has been to make a "dummy" spatial view using command line, then modify the view definition in SSMS.

Best practice is to define the views in the database, then register them, not the reverse.

We've also seen a speed difference between spatial views built on feature classes stored in the default Geometry spatial type (slower) versus SDEBINARY (faster).

It's not just spatial views, it's all tables which involve GEOMETRY/GEOGRAPHY objects,
but these are an aspect of Microsoft's database implementation (and the difficulty of
tuning it), not anything Esri has control over.

- V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

NateArnold — Tue, 11 Mar 2014 18:02:52 GMT

They certainly can be registered with an enterprise geodatabase, if you a) have an enterprise
geodatabase, and b) choose to do so.

Please explain how to do this. I've created a view using CreateDatabaseView_management, in a SQL Server gdb, using an owner account, on a feature class owned by that account. After creating the view, right-click context menu --> Regsiter with geodatabase is greyed out. When I run RegisterWithGeodatabase_management against it, I receive ERROR 001399: Views are not supported.

Query Layers are a way of accessing spatial databases without ArcSDE..

This is an interesting point. True, CreateDatabaseView_management can be used for non-spatial table views too, but if it's used against feature classes that are already in ArcSDE, I think the tooling should be able to take advantage of registering the view instead of leaving it as a query layer.

It's not just spatial views, it's all tables which involve GEOMETRY/GEOGRAPHY objects, but these are an aspect of Microsoft's database implementation (and the difficulty of tuning it), not anything Esri has control over.

That's fine, just as an FYI to the original poster. We found for best performance with our data, load datasets into ArcSDE with the SDEBINARY keyword, then build the registered spatial view with command line tools. Of course, you should try it with one of the other storage types and compare 🙂

Nate

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

NateArnold — Tue, 11 Mar 2014 18:22:38 GMT

Also, one other problem we had when coming from 9.3 to 10.1 was that we changed from the default LONGRAW storage in Oracle to Geometry in SQL Server. Changing data sources in our AGS mxd's to the new gdb resulted in problems with the shape area and length fields. ArcMap did not properly interpret the change in fields, and the publisher/analyzer did not catch the problem, which resulted in AGS map service errors. I'm not sure if any of the newer builds have caught this though.

Nate

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

MarcoBoeringa — Tue, 11 Mar 2014 19:06:17 GMT

I would say the next focus should be on registering views, not creating them.

Agree

Wait right there -- This is the key misunderstanding. Query Layers are a way of accessing
spatial databases without ArcSDE. There is no correlation between views and
Query Layers, other than that they need to be spatial to be rendered.

Maybe it is better to describe the difference between a Query Layer and a Database (Spatial) View as the following:

- Database Views are Views defined by a SQL expression that are stored in, and managed by, the RDBMS. A database view thus is persisted at the RDBMS level.

- Query Layers are "somewhat" similar to Views, as being defined by a SQL expression, but only exist within the context of an ArcMap session. They are persisted at the ArcGIS for Desktop application level, within an ArcMap session/document. Query Layers do a lot more though than storing a SQL expression, they inherit all the properties of normal layers like legend symbology, labeling, min / max display scale and so on. Like normal layers, you can save them as a *.lyr file outside ArcMap, on any drive you have available.

In essence, a Query Layer is a "view-on-a-view", or maybe I should better say a ""view"-of-a-view" (since it defines symbology and display too), just like you can create views based on views (queries based on queries) in a database / RDBMS. But remember: a Query Layer does a lot more in the context of ArcMap, as per the layer settings described above...

They certainly can be registered with an enterprise geodatabase, if you a) have an enterprise
geodatabase, and b) choose to do so.

Vince, you are ignoring an important distinction here that may help in making it more understandable for others, see also the quote below from this sdelayer command page:

"Note: ArcSDE commands do not interact with the geodatabase system tables. Therefore, if your layer is registered with the geodatabase and participates in geodatabase functionality, such as networks or topology, do not use the sdelayer command to administer the feature class. Instead, use the ArcGIS tools and wizards."

In essence, there are 3 registration levels on top of each other. Depending on which option you use, a spatial layer will be "known" and useable within a certain context:

1) - Registering a a database view or table in the native database / RDBMs system tables only. This is by defining the database view through either the RDBMS's tools like SQL Server Management Studio, or(!) creating the view using the CreateDatabaseView_management geoprocessing tool. Data registered as such can be used and accessed as Query Layer only, and not participate within a geodatabase and its functionality.

2) - Registering (a possibly predefined) database view or table in the ArcSDE System Tables only that form part of the ArcSDE Repository using the ArcSDE Command Line Tools like sdelayer (This option will no longer be possible once the ArcSDE Command Line Tools are retired, unless ESRI comes up with a replacement, as Vince also pointed out in the first quote I included above). Data registered like this is recognized "more or less" as "normal" Feature Classes, in the sense of showing proper icon (Point, Line, Polygon), but still can not participate in all advanced geodatabase behaviour, as it isn't registered with the geodatabase.

This option registers data in the following tables:
    TABLE_REGISTRY (or sde_table_registry)
    COLUMN_REGISTRY (or sde_column_registry)
    LAYERS (or sde_layers)
    GEOMETRY_COLUMNS (or sde_geometry_columns)

3) - Registering a database table in the ArcSDE System Tables AND(!) in the Geodatabase System Tables that form part of the ArcSDE Repository by using the Register with Geodatabase menu option within ArcCatalog. This option gives you the full geodatabase functionality, including the ability for the data to participate in advanced geodatabase behaviour.

NOTE: This option is not available for database views (see remark Vince below also), because the data need to be editable to add crucial fields like ObjectID and other geodatabase maintained fields that form part of geodatabase Feature Classes, something which can not (generally) be done with views, e.g. some view types may be non editable at all, like those summarizing data by grouping. Therefore registering with the geodatabase may not be available for all data sources.

This option registers data in the following tables: (see also this Help page: Registering a table with the geodatabase)
    GDB_ITEMS
    GDB_ITEMRELATIONSHIPS
*** and also(!): ***
    TABLE_REGISTRY (or sde_table_registry)
    COLUMN_REGISTRY (or sde_column_registry)
    LAYERS (or sde_layers)
    GEOMETRY_COLUMNS (or sde_geometry_columns)

Only registering with options 2) or 3) will cause the crucial metadata to be stored in the ArcSDE System Tables, and hence the proper icon (Point, Line, Polygon) being shown immediately in ArcCatalog, and the ObjectID and spatial extent of the dataset being "known".

Note that for layers registered with all three options (1,2,3), all layers can also be accessed as Query Layers, but the functionality will be curtailed compared to accessing a layer registered with option 3) and accessed as a normal Feature Class. Query Layers offer less functionality than geodatabase Feature Classes. A list of the functionality available is here:

ArcGIS functionality available for database tables that are not registered with the geodatabase

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Sat, 11 Dec 2021 20:17:04 GMT

The 'sdelayer -o register' command is used to register tables or views in native storage
(or ST_GEOMETRY, where available) with ArcSDE (note that views cannot be registered with
the geodatabase, just with ArcSDE, which is what that message means).

C:\>sdelayer -o register

         Error: Table name and spatial column must be specified.
         Error: Entity Type Mask must be specified.
        Error: A geometry storage type must be specified (-t).
        Valid types are ST_GEOMETRY, SDO_GEOMETRY.

ArcSDE 10.2
Layer    Administration Utility
-----------------------------------------------------
sdelayer -o register -l <table,column> -e <entity_mask> -t <storage_type>
                  [Spatial_Index] [{-R <SRID> | [Spatial_Ref_Opts]}]
                  [-C <row_id_column>[,{SDE|USER}[,<min_ID>]]]
                  [-E {empty | xmin,ymin,xmax,ymax}][-P {BASIC | HIGH}]
                  [-S <layer_description_str>] [-q]
                  [-k <config_keyword>] [-i <service>] [-s <server_name]
                  [-u <DB_User_name>] [-p <DB_User_password>] [-D <database>]
Where
      [Spatial_Ref_Opts] := [-x <xoffset,yoffset,xyscale[,xyClusterTol]>]
                            [-z <zoffset,zscale[,zClusterTol]>]
                            [-m <moffset,mscale[,mClusterTol]>]
                            [-G {<projection_ID> | file=<proj_file_name>}]

      [Spatial_Index]    := [-g {<Grid_Options> |
                                  GRID,<Grid_Options> |
                                  AUTOMATIC |
                                  NONE |
                                  RTREE}]

      [Grid_Options]     := [<grid_sz0>[,<grid_sz1>[,<grid_sz2>]]
                                                   [,{FULL | SPARSE}]]
sdelayer -h
sdelayer -?

You must specify an appropriate coordinate reference (including a HIGH precision), include
a USER-set rowid column (from the existing table/view, that conforms with NOT NULL unique
integer), and specify a single topology class in the entity mask (ArcGIS requirement). The
registration must be performed by the table/view owner. If the object is a view, then you
should make sure its characteristics match that of the source table ('sdelayer -o describe_long').

- V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Tue, 11 Mar 2014 19:52:34 GMT

Marco, I would not classify a Query Layer as a "view" at all. It is QUERY.

A query is an expression which retrieves data from tables. A view is a
database object that stores an SQL query in such a way to reduce the
overhead of parsing the query.

The Query Layer stores the query and rendering rules against either a
table or a view, and reapplies that query each time it needs to
(including at maximum extent, at which point it might not be the most
efficient way to query).

One solution to slow spatial-first queries would be to create two scale-
dependent layers, one whose query forces the database to use a full
table scan, and one that lets the spatial index operate.

- V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

MarcoBoeringa — Tue, 11 Mar 2014 20:08:34 GMT

Marco, I would not classify a Query Layer as a "view" at all. It is QUERY.

A query is an expression which retrieves data from tables. A view is a
database object that stores an SQL query in such a way to reduce the
overhead of parsing the query.

The Query Layer stores the query and rendering rules against either a
table or a view, and reapplies that query each time it needs to

Thanks for that addition that clarifies it further. I already put the "view" notion of the Query Layer in quotes, and tried to emphasize the rendering aspect of it.

(note that views cannot be registered with
the geodatabase, just with ArcSDE, which is what that message means).

Made some changes to accommodate that. Going to log out now...

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

RandyKreuziger — Tue, 11 Mar 2014 22:44:18 GMT

It's not just spatial views, it's all tables which involve GEOMETRY/GEOGRAPHY objects,
but these are an aspect of Microsoft's database implementation (and the difficulty of
tuning it), not anything Esri has control over.

- V

I've noticed the horrible responce for Geometry verses SDEBindary. Has ESRI said how long SDEBindary will continue to be a storage option?

There is a known bug NIM089293 from a year ago dealing with the draw time for the GEOMETRY spatial type verses the draw time for SDEBINARY. Unfortunately, it's severity is "low." In my experience that usually means it's not going to be fixed.

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Wed, 12 Mar 2014 00:33:57 GMT

What could Esri possibly do about this issue? Stage a coup? -V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

MarcoBoeringa — Wed, 12 Mar 2014 07:10:57 GMT

I've noticed the horrible response for Geometry verses SDEBinary. Has ESRI said how long SDEBinary will continue to be a storage option?

A more interesting question is if there are technical reasons why SDEBINARY could no longer be supported in the future in SQL Server. In case of Oracle, the LONGRAW field type that stored SDEBINARY was deprecated by Oracle, so that set a clear endpoint. The more interesting (naive) question is therefore if the field type used by SDEBINARY in SQL Server, is set to go away.

Part of that answer lies probably in the below quote from this Help page and additionally, see this one for more explanation:

"In ArcGIS 9.3 and lower releases, ArcSDE Compressed Binary storage in SQL Server is stored as an image data type. New data created using ArcSDE Compressed Binary storage in SQL Server at ArcGIS 10 and later releases is stored as a varbinary(max) data type."

varbinary is explained here in Microsoft Technet pages:
binary and varbinary (Transact-SQL)

This datatype was one of the data types that is replacing ntext, text and image data types set for deprecation, see this page:
ntext, text, and image (Transact-SQL)

As can been seen from the quote above, image was the field type ESRI used for storing SDEBINARY at ArcGIS 9.3.x and below. So ESRI already anticipated and accommodated the deprecation of this datatype by Microsoft by switching to varbinary(max) at 10.0, so technically the option for storing SDEBINARY doesn't seem to be going anywhere soon...

Of course, the main reason ESRI has been pushing for a shift to "native" Geometry storage or ST_Geometry, is the better accessibility by third party (CAD) software to the data, and the abilities to easily define views etc. on the RDBMS side including spatial data (no separation of geometries and attributes in different tables - feature and business - for a single Feature Class, no "inaccessible" Compressed Binary storage). Another more technical reason may lie in this latter remark, as maintaining the referential integrity between feature and business table is more challenging (see this Help page and the remarks about the triggers set to manage this)

But of course, if your business / organizational workflow won't involve anything other than ESRI software accessing geodatabases, than these advantages are mute...

There is a known bug NIM089293 from a year ago dealing with the draw time for the GEOMETRY spatial type verses the draw time for SDEBINARY. Unfortunately, it's severity is "low." In my experience that usually means it's not going to be fixed.

This issue should probably not have been logged as a "known bug" at ESRI, as, as Vince explained already, this is not an issue ESRI is responsible for, but Microsoft. If it should be logged as a bug (which is already disputable since true bugs are generally code errors, not performance issues which would more realistically classify as "enhancement requests"), it should be logged at Microsoft.

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

RandyKreuziger — Wed, 12 Mar 2014 15:32:49 GMT

What could Esri possibly do about this issue? Stage a coup? -V

Sorry, but I'm not the one who classified it as a bug it was ESRI.

But why has ESRI made Geometry the default storage format at ArcSDE 10.1 pushing users towards the slower performing Microsoft spatial datatype in the first place?

I'd still like to know what ESRI's plan is with SDEBinary. If it's going to be around for the next 3 years I'll stick with it.

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

VinceAngelo — Wed, 12 Mar 2014 16:29:06 GMT

But why has ESRI made Geometry the default storage format at ArcSDE 10.1

Interoperability.

It's not always slower -- the hellish B/S/F table join should be much slower than
native search; it's just that Microsoft's spatial index algorithm needs work.

I have no knowledge of Esri plans, yet I seriously doubt SDEBINARY will last another
three years.

- V

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

NateArnold — Thu, 13 Mar 2014 14:21:28 GMT

The 'sdelayer -o register' command is used to register tables or views in native storage
(or ST_GEOMETRY, where available) with ArcSDE

Thanks for that. Bummer more command line.

Nate

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas?

ThomasColson — Sat, 11 Dec 2021 20:17:06 GMT

I've come to be pretty dependent on both Geometry and Geography storage types, given that I can do significantly more automated data management and processing with them both. Knowing that I'm taking a performance hit by using them, there are a lot of tuning options available, which I've managed to get Geography performing as well, if not faster in some cases, than binary. The biggest gain comes from tweaking the spatial index, but there are other gains from adding other non-clustered indexes and or statistics based on specific use.

As for view performance, one can force indexes on a view at creation with schemabinding:

CREATE VIEW [dbo].[ELKOFFTAKE_VIEW_LOCATIONS]
WITH SCHEMABINDING
AS
SELECT 
OBJECTID, VERROR, MAPSOURCE, SOURCEDATE, EDITDATE, MAPMETHOD, 
QUADNAME, UNITNAME, HERROR, COUNTY, STATE, X_COORD, Y_COORD, LAT, LON, 
WATERSHED, STREAMNAME, ELEVATION, YR, NOTES, COORD_UNITS, COORD_SYSTEM, 
DATUM, UTM_ZONE, MANAGEMENTZONE, PARKDISTRICT, EDITBY, CREATEBY, CREATEDATE, 
UNITCODE, RESTRICTION, VEG_CLASS, LANDFORM, GlobalID AS 'LOC_ID', LOC_NAME,  TRAIL, ROAD, VALID_RESULT
FROM [dbo].[WILD_ELK_OFFTAKE_LOC_PT]
GO

Then

CREATE UNIQUE CLUSTERED INDEX IDX_WILD_ELK_OFFTAKE_LOC_PT 
  ON ELKOFFTAKE_VIEW_LOCATIONS (LOC_ID);