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

6512
19
02-21-2014 12:59 PM
Regular Contributor
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?
19 Replies
Esri Esteemed Contributor
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
Reply
0 Kudos
New Contributor III
Reply
0 Kudos
Esri Esteemed Contributor
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
Reply
0 Kudos
MVP Regular Contributor
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.
Reply
0 Kudos
Occasional Contributor
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
Esri Esteemed Contributor


  • 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
Reply
0 Kudos
Occasional Contributor

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
Reply
0 Kudos
Occasional Contributor
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
Reply
0 Kudos
MVP Regular Contributor
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