ArcGIS Pro 3.1.1
Background:
I have a standalone table called TEST_TABLE that is joined to a database view called TEST_TABLE_NT_FLAG_VW:
--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:
Inconsistent querying techniques/performance for different DB types:
When I look at the Diagnostic Monitor —> Logs tab, I see the following SQL queries are sent to the database when I open the attribute table:
Mobile Geodatabase:
select
main.test_table.objectid,
main.test_table_nt_flag_vw.flag
from
main.test_table
left outer join
main.test_table_nt_flag_vw
on main.test_table.objectid = main.test_table_nt_flag_vw.objectid
where
main.test_table.objectid in (158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257)
The query uses a join. The attribute table opens quickly. Good.
Oracle 18c 10.7.1 EGDB:
select
objectid
from
infrastr.test_table
where
(objectid in (10089,10090,10091,10092,10093,10094,10095,10096,10097,10098,10099,10100,10101,10102,10103,10104,10105,10106,10107,10108,10109,10110,10111,10112,10113,10114,10115,10116,10117,10118,10119,10120,10121,10122,10123,10124,10125,10126,10127,10128,10129,10130,10131,10132,10133,10134,10135,10136,10137,10138,10139,10140,10141,10142,10143,10144,10145,10146,10147,10148,10149,10150,10151,10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163,10164,10165,10166,10167,10168,10169,10170,10171,10172,10173,10174,10175,10176,10177,10178,10179,10180,10181,10182,10183,10184,10185,10186,10187,10188))
select
objectid,
flag
from
(select objectid,flag from infrastr.test_table_nt_flag_vw) a
where
objectid in (10089,10090,10091,10092,10093,10094,10095,10096,10097,10098,10099,10100,10101,10102,10103,10104,10105,10106,10107,10108,10109,10110,10111,10112,10113,10114,10115,10116,10117,10118,10119,10120,10121,10122,10123,10124,10125,10126,10127,10128,10129,10130,10131,10132,10133,10134,10135,10136,10137,10138,10139,10140,10141,10142,10143,10144,10145,10146,10147,10148,10149,10150,10151,10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163,10164,10165,10166,10167,10168,10169,10170,10171,10172,10173,10174,10175,10176,10177,10178,10179,10180,10181,10182,10183,10184,10185,10186,10187,10188)
The query doesn't use a join. Instead, it executes two separate queries: one for the table and one for the view. I believe the join is performed after the fact in the application (ArcGIS Pro). The attribute table opens slowly.
Likewise, in an Oracle 18c database (non-GDB):
select
objectid
from
testgisuser.test_table
where
objectid in ( 158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257)
select
objectid,
flag
from
testgisuser.test_table_nt_flag_vw
where
objectid in ( 158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257)
Similarly, the query doesn't use a join. It executes two separate queries: one for the table and one for the view. The join is performed in the application after the fact; the attribute table opens slowly.
The database is stored locally (same computer as ArcGIS Pro), so it should be just as fast as the mobile GDB (and does perform well when the query has a join; executed in a SQL client).
I think ArcGIS Pro should query all database types using the technique that the mobile GDB uses: a SQL join when the datasets are in the same workspace. That would improve performance. Otherwise, attribute table join performance is surprisingly poor for other database types like Oracle -- when using large tables. The equivalent query with a join performs much faster in other applications like SQL clients.
Could that behavior be improved?
Thanks.
