Open table with sorted join — Logs show redundant queries sent to DB

Bud — Wed, 26 Apr 2023 14:01:22 GMT

ArcGIS Pro 3.1.1; mobile geodatabase

I have a standalone table called TEST_TABLE (14,538 rows) that is joined to a database view:

--test_table_nt_flag_vw
select 
    objectid,
    case when type = 'NT' then 1 else 0 end as flag
from 
    test_table

I've sorted on the view's FLAG field in the attribute table.

Use case:

When opening the joined/sorted attribute table, I noticed that the Diagnostic Monitor logs show that redundant queries were sent to the database.

Video:

(view in My Videos)

I've taken the logs from Diagnostic Monitor and pivoted/filtered them in the attached Excel spreadsheet (in the Pivoted and Filtered worksheet). That makes it easier to compare the queries against each other to see which ones are redundant. Source

Observations about the queries in the spreadsheet (each row in the spreadsheet summarizes a query from the logs):

Rows 23 and 24 are exact copies. Why do we need both? Further, the OBJECTIDs in those queries are already included in row 3 and 4. So rows 23 and 24 seem unnecessary.
Row 2 is also redundant. Rows 3-22 already do everything that row 2 does. (While only a portion of the records are queried in rows 2-33, that's just because I haven't navigated to the bottom of the attribute table yet. Once I do that, all records will be accounted for in those queries.)
To me, only rows 3-22 seem necessary.
- Those rows seem to take some sort of pagination approach.
- In comparison, when a table (no join or sort) is opened in ArcGIS Pro, the queries are paginated in that case too (and they're the only queries shown in the logs -- ~~no redundant queries~~). So that makes me think paginated queries are standard in ArcGIS Pro.
- Therefore, I suspect the paginated queries in rows 3-22 are appropriate and should be kept. Rows 2, 23, and 24 seem unnecessary and could be eliminated.

Question:

Why does opening a table with a sorted join produce redundant queries?

I'm investigating this simple mobile geodatabase example in hopes that I can solve performance issues when doing the same thing, but with larger Oracle tables, as a way of coming up with a workaround for Virtual Attributes (ad hoc) that performs well.

Re: Open table with sorted join — Logs show redundant queries sent to DB

Bud — Wed, 26 Apr 2023 07:32:00 GMT

Here's the SQLite query I used to pivot and filter the logs: Copy Diagnostic Monitor logs as Excel table, not as vertical tags

topic Open table with sorted join — Logs show redundant queries sent to DB in ArcGIS Pro Questions

Open table with sorted join — Logs show redundant queries sent to DB

Re: Open table with sorted join — Logs show redundant queries sent to DB