Will registering a view with an enterprise geodatabase improve performance?

314
5
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

Reply
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

5 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!

Reply
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