Creating views in SDE 10.3

14355
25
Jump to solution
12-19-2014 06:03 AM
KenCarrier
Occasional Contributor III

How are views managed in 10.3? At 10.2.2 creating a view inside SDE acted like a query layer. By this I mean when adding the view to ArcGIS Desktop you would be required to specify a unique ID field and a spatial extent. On the other hand when creating a view via SDE command line the view would get registered with sde and the user would not have to specify anything when adding that layer to ArcGIS Desktop. This is one function from sde command line which had better performance and usability than creating the views through ArcGIS Desktop. How has this changed in 10.3?

25 Replies
DanMcCoy
Occasional Contributor III

"Coming at the 10.5 release the ‘Register with Geodattabase’ tool will be enhanced with additional parameters and allows database views as acceptable input datasets."

https://community.esri.com/ideas/8897#comment-42312 

0 Kudos
MichelleMestrovich1
New Contributor III

I wanted to give everyone an update on the view situation.  I've been in contact with my ESRI rep and they have  been getting a lot of complaints about this. It looks like they will be adding the view functionality back in a future release. They realize that folks really need them even though ESRI did not intend them to use them that way (not sure what they expected us to use them for and haven't really been given an answer). 

Anyway, it's good news, although not sure how soon it will be resolved.

I've notice in 10.3 the views I created with SDE command line 10.2.2 have sketchy performance. They are slow to draw or search in ArcMap and ArcCatalog. I've made attribute and spatial indexes on the underlying data to the views, but still no luck.

I really don't want to create static layers of these views, but I may have not choice until ESRI fixes this. Does anyone have any suggestions on this.


Thanks


Mich 🙂

0 Kudos
KenCarrier
Occasional Contributor III

Michelle,

The way I see it you have 2 options. Typically the slow performance from Views is relative to a few things. The complexity of the data, the complexity of the query behind the view, or the geometry storage you are using. If using SQL Server there are some known issues with performance degradation when using ST_Geometry versus SDE_BINARY. So if you are using ST_Geometry I would expect there to be a little lag. So you could look at changing the geometry type being used to store the features.

Secondly, while stand alone feature classes sound like a drag it does not add too much overhead if it is scripted. I also ran into these same performance issues and to keep performance optimal I created a stand alone feature class from my view, then scripted a truncate and append method whereby I would truncate the stand alone feature class only if the record count in the view was more than 1 or 2 to ensure good data was in the view. Then I would append the records from the view to the stand alone feature class. Thereby breaking the join between the tables, increasing performance, and allowing me the opportunity to modify the spatial index if needed. Additionally if you have any attribute indexes on the feature class they will remain but might need to updated after a bulk load. If you are handy with python or model builder this could all be automated on a nightly routine after a recnpost or any other nightly maintenance. Furthermore, since using a truncate and append method there is no need for an exclusive schema lock to update the data, meaning ArcGIS Dekstop or Server could be using the data and it can still be updated while it is still in use so long as it is not versioned. The users might see the data disappear for a second while updating but if they pan or zoom it will come back. If doing this while no one is connected is an option then that is when I would recommend doing this, but in some cases you might need to update during the day just saying it could be done mid-day.

Glad to hear things might be moving along although I have heard differently from others about how soon this will be addressed, I will keep my fingers crossed thanks for the update.

0 Kudos
MichelleMestrovich1
New Contributor III

I have been testing and I can create a view through AC but what will not work for my users is each time having to answer what the spatial info is and what the unique id is?  If somehow the view could store what I tell it to use as the SRID and Unique ID then it would be fine, but if my users have to answer that question every time they would burn me at the stake.

0 Kudos
KenCarrier
Occasional Contributor III

I have talked to Tom Brown at Esri with the GeoData team and some Esri instructors about this exact same issue you are talking about. I am sorry to say this is how views currently function unless created with sde command line. The workarounds I mentioned above are ways around this and in my opinion might be considered best practice if you do not want to do joins in model builder. If you need any assistance with creating or modifying a script to fit your needs just let me know. I know this sucks as spatial views can make an administrator's life easier but for the time being if performance is key and if you do not want your users to have to answer 2 questions before they can view the data each time, this is the best way I have found to do this and automate the process. Hope it helps!

0 Kudos
ThomasColson
MVP Frequent Contributor

Re: Converting from SDE Views to Regular SQL Views - Any Gotchas? Might help out here: in essence, creating the view via SSMS bypasses all of the dependence on the SDE command line utilities.

0 Kudos