Fundamental flaw when opening attribute tables in ArcGIS Pro?
Can someone from ESRI please have a good look at this. @Kory Kramer, @George Thompson, @Vince Angelo
For some background:
- ArcGIS Pro 2.1.3 on Windows 10
- PostgreSQL 9.6 on Ubuntu 18.04 running on a Virtualbox VM executed on the Windows host
Situation:
A line layer with a valid Definition Query set present in the TOC. The layer is an ArcGIS Query Layer (so a spatial database but not an enterprise geodatabase) and references a huge Europe OpenStreetMap extract containing +/- 65M records in the line table. This line table has both valid spatial index (GIST) and attribute indexes. The display performance of the layers in Pro's Map window is adequate, and actually surprisingly fast given the huge data source.
The Definition Query set on the layer, see image, references an indexed field, osm_piste_58_type:
If I insert the Definition Query in DBeaver IDE, and prepend it with the necessary SELECT...FROM... that is missing in the Definition Query itself, and run an execution plan, the query runs in less than 3 seconds (2163ms) and returns 38697 rows, so just under 40k records. The execution plan shows the database correctly using the available index ("Index Scan"):
The problem:
When I attempt to open the Attribute Table in Pro by right clicking the layer and choosing "Attribute Table", Pro, instead of showing the records near instantly as it should based on the 3 seconds execution plan, instead takes > half an hour to open the Attribute Table. This is the time needed for a full table scan. When it is finished, it does show the proper 40k records, so the final selection was made.
When I enable logging in PostgreSQL and check the created log, I can see the SQL generated the moment I open the attribute table. Much to my surprise, even though the Query Layer had a Definition Query set, this Definition Query is not set on the SQL send to the database to restrict the binary cursor being created, as a consequence, the entire table seems subsequently to start being scanned in chunks of 1000 records, see the FETCH FORWARD 1000 statements in the log. This seems to go on the entire half hour, until a full table scan of all the millions of records is done, and the final table is created. As I wrote above, the final result in the Attribute Table, does correspond to the Definition Query, and is only the 38697 rows.
To inspect this better, I copied both the cursor statement that is initially used to create the layer (which is fast!), and compared it with the above statement representing the opening of the Attribute Table in an OpenOffice document, see the below screenshots. Notice how the first screenshot, which represent the moment the layer is created when the Pro Map document is opened and the TOC layer generated, does contain the Definition Query in the WHERE statement. This WHERE statement is omitted though, in the second screenshot that represents the opening of the Attribute Table:
- Creation of Query Layer: 
- Opening Attribute Table:
Am I missing something here, or fundamentally misunderstanding what is going on and misinterpreting this data? Why is there no WHERE statement submitted to the database when opening the Attribute Table of a (Query) Layer?
I think I have another interesting observation to share, that also reveals some of the "inner workings" of Query Layers, and their intertwinement with what ESRI used to call SDE but is actually the foundation of the access to databases in ArcGIS products.
If you look at the screenshot, you see again two SQL statements, this time run against a much smaller PostgreSQL database than the original one I posted about, but a comparable situation with a Query Layer. The first highlighted SQL statement was issued to the database once I clicked the layer visible in the TOC, and shows actually two WHERE clauses: one I added to the Query Layer's SQL statement directly: WHERE osm_highway = 'secondary', and the other one I added as a Definition Query of the same layer: where ((osm_name = 'Rua Nova')).
Further down highlighted in blue is the SQL statement that was fired when the I opened the attribute table. There are in fact four records that have satisfy the osm_name = 'Rua Nova' clause, however, interestingly, this clause is not included as the WHERE statement, but instead the actual OBJECTIDs of the records corresponding to the clause mentioned.
It appears, once a layer is created, all the OBJECTIDs corresponding to the layer's defining SQL statements, are likely kept in memory and submitted to the database in one long IN (...) statement. That is the "inner workings" I meant. So Pro does submit a WHERE clause in this case, even though it is not the one I expected.
Even more puzzling of course, is that this seems to work for this small layer, but failed on the giant 65M record layer. Now I am pretty sure, and there is of course no reason for this, that Pro won't attempt to build an IN (...) clause for 65M records all at once, which would probably fail, but instead likely only needs to build such IN (...) statement for the first 2000 records or so that an attribute table usually displays once it opens (you can't show 65M records on a screen at once...). But even this likely limited operation seems to fail in the case of this giant layer.
For comparison, I have now also looked at what happens in ArcMap, and there does seem to be some vital differences.
What I did:
- Drag & drop the huge table in ArcMap (You can't actually do this yet in Pro by the way!) from ArcMap's Catalog window. ArcMap then opens a dialog about the need to calculate the "spatial extent". This is also a slow operation in ArcMap, which also seems linked to a full table scan (which I also do not fully understand, can't this information be derived from the spatial index much faster?). The SQL of the resulting Query Layer is equivalent to the SELECT * FROM TABLE_NAME of the last post, albeit with the * wildcard replaced by a full list of all the database columns of the source table.
- Without any Definition Query set, I opened up the Attribute Table in ArcMap. This is still a slightly costly operations, taking one or a few minutes, but not nearly as bad as in Pro taking half an hour in the same situation.
Now, when I look at the PostgreSQL logs, I see at least one difference with Pro. Opening the attribute table seems to happen in two steps. Notice that when the first BINARY CURSOR is created, the SQL statement contains a WHERE clause with what appears to be a spatial constraint. I think I saw this step happening in Pro as well.
Anyway, when you look at the second highlighted BINARY CURSOR being created, which represent the moment ArcMap really starts creating the actual contents for the Attribute Table of the Query Layer, you will notice (highlighted in blue) that ArcMap actually does restrict the record set to the first 100 records or so, by inserting their OBJECTIDs as part of the a large IN (...) statement.
I also did another observation regarding ArcGIS Pro: when I open the Attribute Table the first time, it takes half an hour. Any subsequent opening of the Attribute Table is much faster, and seems equivalent to ArcMap.
As a consequence, I am now getting convinced that the behavior I am seeing is actually NOT A BUG, but a change in how ESRI decided to handle Query Layers and specifically the calculation of the spatial extent of such Query Layer:
- In ArcMap, when you drag a spatial database in the TOC, a dialog opens up explicitly warning you that the "spatial extent" (Calculate Extent is the title of the small dialog) is being calculated and that this may be a costly operation (likely due to the full table scan taking place in the background), and even offers override options by allowing you to manually enter them, or use the Spatial Reference's maximum extent.
- In ArcGIS Pro, no such dialog pops up. Instead, it seems to leave a kind of "unitialized" layer in the TOC. What then happens is that Pro appears to start calculating this spatial extent the first time you open up the Attribute Table or use some geoprocessing tool on the layer, like I have witnessed with the "Apply Symbology From Layer" tool that takes forever to open up when such a Query Layer layer is present. Afterwards, this information seems cached, and being re-used the next time you e.g. open the Attribute Table of the Query Layer.
While Pro's implementation takes away the "annoying" spatial extent calculation dialog that pops up in ArcMap, the ArcMap method, with an explicit calculation when you add the data, is far more desirable for large layers, as it won't lock up the application in an unexpected manner for long periods of time without the user knowing why.
If I am right, I also think the calculation of the spatial extent of Query Layers really needs to be reviewed by ESRI and find out if there isn't a much more efficient way to do this, e.g. by taking a "random sample" of e.g. 1% of the records in case of large tables. It now seems to require a full table scan both in ArcMap and Pro, which is outrages in the context of database tables of tens of millions of records. I do not know if there are restrictions I don't understand, that ESRI has to abide by, that cause the current implementation in ArcMap and Pro regarding this, but it would be highly desirable to have a much more efficient method for this, thus taking away a performance bottleneck on Query Layers based on millions of records.
