Select to view content in your preferred language

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

863
5
05-26-2023 08:00 AM
Status: Open
Labels (1)
Bud
by
Honored Contributor

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.

5 Comments
Bud
by

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

MarceloMarques

@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.

Bud
by

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'.

Bud
by

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