Will registering a view with an enterprise geodatabase improve performance?

[RyanDavis1]

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

[George_Thompson]

Adding a broader audience; Geodatabase‌  Enterprise GIS‌

[ChristianWells · Accepted solution]

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.

[RyanDavis1]

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

[ChristianWells]

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

[VincentLaunstorfer]

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]

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.

[VincentLaunstorfer]

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...

[DonSjoboen]

You can write your SQL statement (in the SELECT line) to include the following...

SELECT  ISNULL(CAST(ROW_NUMBER() OVER

         (

                   ORDER BY (SELECT NULL)

          ) AS int), -1) AS vwObjectID [you can call the OID whatever you like]

what this SQL statement does is use the databases Row Number function, Is Null function and Cast to assign a unique/not null integer row value for each record in the view.

Hope this helps!?