Perform join at the database level, not the application level, when the datasets are in the same workspace

Bud — Tue, 06 Feb 2024 19:26:52 GMT

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.

Re: Perform join at the database level, not the application level

MarceloMarques — Fri, 26 May 2023 19:54:36 GMT

an alternative is to use

Query layers—ArcGIS Pro | Documentation
or

Views in an enterprise geodatabase—ArcGIS Pro | Documentation

Re: Perform join at the database level, not the application level

Bud — Fri, 26 May 2023 19:57:18 GMT

@MarceloMarques Yes, although the fields from the FC in the query layer or database view are not editable.

Re: Perform join at the database level, not the application level

MarceloMarques — Fri, 26 May 2023 20:15:59 GMT

@Bud - yes, it will not be editable, but you can get the join done very fast, for very large tables, the query layers might not be the best solution, and instead a materialize database view will be the best alternative, and if use database table partitioning and index partitioning then the query layer and the database view performance will improve, this way can use the query layer and database view only for reporting or to create a new map, and keep the data with a geodatabase relationship class for example to edit in another map document.

Re: Perform join at the database level, not the application level, when the datasets are in the same workspace

Bud — Sun, 17 Dec 2023 23:49:24 GMT

Esri Case #03356933 - Join not performed at database level when datasets are in the same workspace (Oracle)

New bug:
BUG-000160320: Slow performance in Pro when interacting with the attribute table of data which participates in a join.

Existing bug:
BUG-000137891: In ArcGIS Pro 2.6.3 and above, slowness in a display of ‘Properties’ of the spatial views occurs when the query definition consists of the CAST function, CASE function, and the storage type is 'GEOGRAPHY'.

Re: Perform join at the database level, not the application level, when the datasets are in the same workspace

Bud — Thu, 07 Mar 2024 19:53:33 GMT

Related performance issues with join: Relate from table to table joined to query layer

idea Perform join at the database level, not the application level, when the datasets are in the same workspace in ArcGIS Pro Ideas

Perform join at the database level, not the application level, when the datasets are in the same workspace

Re: Perform join at the database level, not the application level

Re: Perform join at the database level, not the application level

Re: Perform join at the database level, not the application level

Re: Perform join at the database level, not the application level, when the datasets are in the same workspace

Re: Perform join at the database level, not the application level, when the datasets are in the same workspace