Arc Pro connection to SQL - very slow

[AmyGwin2]

Hi! I'm looking for best practice ideas for connection to SQL tables in ArcPro. 

I need to connect to a new very large SQL tables of points on a weekly basis. I've connected to the database in a pro project. However, each time I reopen the project I have to wait for it to index all the contents of the SQL database. This can take all day! Each table could contain a query of 1million+ points.

Is there a better way to maintain this when I close the project out? #connection issues‌ #sql servers‌ #pro is slow‌ 

[MarcoBoeringa]

How are you actually adding the data to Pro? I recently opened another discussion thread regarding similar issues accessing data stored in a non-geodatabase spatial database in PostgreSQL through Query Layers in Pro.

https://community.esri.com/thread/216224-fundamental-flaw-when-opening-attribute-tables-in-arcgis-pr...

Like you, I was working on really large datasets (In my case even >65 up to 165M records), using Query Layers to access the data, and I am pretty much convinced ESRI never tested this functionality at scale using similar sized datasets given the issues I experienced. I also saw bad performance accessing such data. One thing that has become clear, is that ArcGIS attempts to calculate the spatial extent of any spatial table you access, causing full table scans on the data for an apparently trivial cause / reason.

This is one crucial difference with geodatabases, where the maximum spatial extent is probably registered in the geodatabase system tables, and thus only needs to be determined once. I am deeply convinced though, that ESRI should review the entire access of non-geodatabase spatial databases, and allow it to scale. E.g., if the spatial extent calculation is so important for ArcGIS, but can't scale with the current implementation, why not take a random sample of e.g. 1% of all records to get an estimate of the extent? Since ArcMap allows you to override the spatial extent calculation once you add a Query Layer to ArcMap using either a user provided exent, or the spatial reference maximum valid extent, the exact size of the spatial extent doesn't seem to be relevant, and an estimate probably is enough.

One big tip though when accesing such data:

Make sure you always include the entire selection clause in the WHERE clause of the Query Layer. Do not add part of the SQL in the Definition Query property of the layer. This is much more efficient than having part of the WHERE clause as Definition Query.

E.g.

"SELECT * FROM <TABLE_NAME>" as Query Layer + "<FIELD> = X" as Definition Query

is NOT the same as / equivalent to:

"SELECT * FROM <TABLE_NAME> WHERE <FIELD> = X" as Query Layer

from a performance point of view in the current ArcGIS implementation of accessing non-geodatabase spatial tables. The latter is much faster if the WHERE condition is selective, as the former is in essence considered one giant table without any selection.

[AmyGwin2]

Marco, 

I am just 'adding the database connection' in the pro project. It seems to loose the connection when I close the project and open it again. I thought if I allowed it to generate each table (1/2 a day) it would keep those tables available the next time I opened the project, but they were not saved.

Maybe I should also clarify my purpose. I'm connecting to spatial query tables already completed by another use, and I need to map the data, splitting it into a few different symbology based on definition queries. Nothing too fancy, just connect & map.

Thanks in advance.

[ThomasColson]

I feel your pain, Pro <-> SDE is painfully slow in every environment I use it in. Unfortunately, it's not going to get solved on the internet, and you're going to have to call tech support. I have a current Pro/SDE performance case open, and we're really down in the weeds with SQL Traces and Wire Shark logs.

[MichaelVolz]

Thomas:

Are you having slowness issues in Pro from SDE using different databases (e.g. SQL Server, Oracle, Postgres, other)?

Besides performance issues with SDE, are you by any chance using query layers to connect non-SDE databases that are spatially enabled with ST_Geometry libraries?

Although not a query layer from a SQL Server database, in conjunction with ESRI technical support I did find fundamental issues with query layers and Oracle spatially enabled databases, so you might be encountering a similar problem with query layers from your SQL Server database:

"As a recap of our most recent call:

I created a table for simple point data, assigned the OBJECTID field as NUMBER(38) field type during initial creation in Oracle from a SQL query. This gets casted as NUMBER(38,0) at the oracle table level. When I create a view from that table directly in oracle (SELECT *), the OBJECTID field in the view is assigned NUMBER(38).

When I compare the view and the table in ArcCatalog - the table's NUMBER(38,0) field is interpreted as an "Object ID" field. The view's NUMBER(38) field is interpreted as a long integer.

Even if I cast the precision when creating the view OBJECTID still gets assigned as NUMBER(38):
 

CREATE VIEW city_points_VIEW_num38_0 AS
  SELECT CAST("OBJECTID" AS NUMBER(38,0)) AS "OBJECTID","CITY_NAME","LATITUDE","LONGITUDE","SHAPE"
  FROM city_points 
 

If this were a geodatabase, we would be able to register the view with the geodatabase and the OBJECTID field would be considered as an "Object ID" field rather than interpreting and referencing a long integer field as an Object ID field. This seems to be a limitation of the software when interpreting unique ID fields from an oracle view from a spatially enabled table, and would be most likely a defect."

ArcMap software does not handle the OBJECTID field properly which causes all sorts of downstream issues in performance and basic query functionality failing.

[ThomasColson]

All SQL 2014 Enterprise, multiple database instances, some have query layers and spatial views, using ST_GEOGRAPHY on everything. 

[MichaelVolz]

Have you been able to determine the root cause of SDE slowness in the Pro projects with the help of technical support, database traces, and WireShark?

[ThomasColson]

Getting there. I have a conf. call with some support folks next week. 

[MarcoBoeringa]

Thomas, Did you ever get a step further with this? Also considering the UC is long past.

[Waan]

Seconding Marco's request--did you ever make progress on this issue?