ufeliciano

Performance issues when accessing non-spatial data from remote database -- My experiences with XY Event Layers

Blog Post created by ufeliciano on Sep 11, 2014

This post is created for personal documentation and tracking...maybe it could help others or promote further discussion that allow some of us to understand more about these issues and improve the tasks describe.

 

Scenario:

  • Geodatabase 1
    • SQL Server 2008 R2 
    • ArcGIS 10.2.2
  • Geodatabase 2
    • Oracle 11.2.0.3
    • ArcGIS 10.2
  • Remote database
    • SQL Server 2008

 

Task:

  • Create a feature class in the geodatabase from a view stored in the remote database that contains coordinates in WGS84 and reproject to NAD83.

 

Procedure:

  • Make XY Event Layer to create the virtual layer from coordinates in the remote database.
  • Feature Class to Feature Class to create a feature class in the geodatabase from the virtual layer.
  • Project to create a new feature class in the required projection (State Plane NAD83).

 

Issues:

  • The view stored in the remote database have aproximately 1,364,979 records, but only 56,347 records have coordinates.
  • Creating the XY Event Layer takes about 2-3 minutes, but running the Feature Class to Feature Class tools takes about 5 hours to finish, which is not acceptable.
  • The perfomance is the same either using the tools or using Python.
  • This performance issue occurs in both available geodatabases.

 

Workaround:

  • To be able to obtain the desired results in a short time, we changed our approach from using ArcGIS tools to use spatial methods in the remote database.  We create a new spatially enabled table in SQL Server (geodatabase 1) from the view stored in the remote database using the geometry methods.
  • Adding this spatially enabled table to ArcGIS allows to finish the task in approximately 10 minutes.  The Feature Class to Feature Class step dramatically reduced the time originally consumed.  The specific sql statement used to create the new table is:

 

select
ID =IDENTITY(int,1,1),
shape =geometry::Point(xPoint,yPoint,4326),
column1,column2,column3,xPoint,yPoint
into squema.SpatialVIEW
from server1.database.view
where xPoint is not null;

 

Final thoughts:

  • Altought no specific issues were detected in the servers (memory and cpu resources or other events) during the execution of the steps initially mentioned, using the spatial capabilities of the back-end database suggestively provides better performance execution.  It looks like the issue is caused by the XY event Layer, but I can't quite get why. Feel free to comments and provide your suggestions.

Outcomes