Will registering a view with an enterprise geodatabase improve performance?

599
7
Jump to solution
05-12-2020 07:17 AM
RyanDavis1
New Contributor III

Hi everyone - Pretty basic question.  Will registering a view that was created outside of ArcGIS improve rendering performance?  I see here a mention that "having spatial information stored in geodatabase system tables improves performance times when adding the table or view to a map."  But, that's all I can find on the subject.  Could someone briefly explain the mechanics behind this?

 

The situation is that we have analysts creating web apps and dashboards directly from SQL Server views.  Our group makes extensive use of SQL views but does not take advantage of registering them.  They have complained about the rendering performance, and I'm wondering if registering the view will make any noticeable difference.

 

I'm just looking for some information that I can take to my colleagues rather than try to have the DBO user run a series of tests.  (I personally don't have the ability to register tables and views on this particular database.)

 

Thanks,

Ryan

0 Kudos
1 Solution

Accepted Solutions
ChristianWells
Esri Contributor

Behind the scenes, an unregistered view will be nested within a subquery. The reason that this happens is so the software can evaluate the SQL statement for metadata about the data in the view. Here is an illustration of what you might see in the SQL database trace:

SELECT OID, SHAPE FROM (SELECT * FROM MY.VIEW) WHERE OID = 1;

 By registering the view with the geodatabase, the metadata gets stored within the geodatabase repository so that subsequent calls do not require ArcGIS to gather the metadata. The act of registering the view will remove the need for the nested query. Therefore, the execution plan in the database will have fewer steps and performance should be comparably better than the unregistered view.

View solution in original post

7 Replies
George_Thompson
Esri Frequent Contributor

Adding a broader audience; Geodatabase‌  Enterprise GIS

--- George T.
ChristianWells
Esri Contributor

Behind the scenes, an unregistered view will be nested within a subquery. The reason that this happens is so the software can evaluate the SQL statement for metadata about the data in the view. Here is an illustration of what you might see in the SQL database trace:

SELECT OID, SHAPE FROM (SELECT * FROM MY.VIEW) WHERE OID = 1;

 By registering the view with the geodatabase, the metadata gets stored within the geodatabase repository so that subsequent calls do not require ArcGIS to gather the metadata. The act of registering the view will remove the need for the nested query. Therefore, the execution plan in the database will have fewer steps and performance should be comparably better than the unregistered view.

View solution in original post

RyanDavis1
New Contributor III

Thank you Christian.  Your reply makes a lot of sense.

ChristianWells
Esri Contributor

You're welcome - I'm glad this was helpful!

0 Kudos
VincentLaunstorfer
Occasional Contributor III

Hi,

Knowing that performance is improved by registering spatial table or view with enterprise geodatabase is good to know... However, is it possible to register a table or view directly with a sde command in a third party SQL client? ...instead of the regular Register with Geodatase tool in the toolbox... This is just to improve workflows...

Thanks

VincentLaunstorfer
Occasional Contributor III

Hi,

Question about registering a view in an enterprise GDB: how is it possible to have a NOT NULLABLE ObjectID field to register a view into an Enterprise GDB?

Usually, a view will inherit settings from the base table, in the case of a table with an ObjectID field NOT NULLABLE, the ObjectID field in the view should/will be NOT NULLABLE.

On the other hand, if the view is built-up with a third-party SQL client, with an ObjectID built from a CAST() SQL statment, how to specify that this ObjectID field will be NOT NULLABLE?

I need this NOT NULLABLE ObjectID field in my view in order to register the view with an Enterprise GDB, as explained here:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/register-a-table-or-view-wi...

So far, I managed to built an ObjectID field as an Integer in Oracle, recognized as Long in ESRI ArcGIS. But I still get an error when trying to register the view in Enterprise GDB... and I suspect with NOT NULLABLE ObjectID field in my view...

Any help really appreciated.

0 Kudos
VincentLaunstorfer
Occasional Contributor III

In addition to my post above, I tried to Register the same view, but created as a table and I still receive the "unexpected error... The view's registered base layer was not found." It does not make sense because it is a table now...

 

0 Kudos