topic Re: Attribute table join (and sort) between two EGDB tables: How are the join and sort performed? (DB vs. application) in ArcGIS Enterprise Questions

Attribute table join (and sort) between two EGDB tables: How are the join and sort performed? (DB vs. application)

Bud — Mon, 27 Jun 2022 09:54:19 GMT

Oracle 18c 10.7.1 EGDB; ArcGIS Pro 2.6.8:

In the attribute table, if I join between one table to another, how is the join performed? (DB vs. application)

Does a single query get sent to the DB that returns ALL columns in a single resulset?
- Select the rows from the first table, do an SQL JOIN to the second table, for each row - retrieve the first row from the second table that's matched, return the whole thing as a single resultset?
- Or maybe a subquery is used (for each row in the first table) to get a row from the second table?
Or does the application do the joining?
- Select the rows from the first table. Select the rows from the second table. Use the application to match them up?
Or something else?

Likewise, if I sort the columns in the attribute table, does the DB do the sorting, or does the application do the sorting?

Re: Attribute table join (and sort) between two EGDB tables: How are the join and sort performed? (DB vs. application)

Bud — Sun, 26 Jun 2022 01:02:27 GMT

The reason I ask: I'm trying to figure out why sorting is so slow in this workaround:

Question: Sorting a field in the attribute table: Specify how nulls are handled (nulls first or last)

Workaround:

1. Create a query layer with a NULL_FLAG field:

select
    objectid,
    last_edited_date,
    case 
        when last_edited_date is not null then 1 
        else 0 
    end as null_flag
    --Source: https://modern-sql.com/concept/null#order-by
from
    events

2. In the Attribute Table, join from the table to the query layer via the OBJECTID.

3. Do an advanced sort using the fields from the query layer:
- NULL_FLAG descending.
- LAST_EDITED_DATE descending.

Now, the latest dates are at the top and the nulls are at the bottom. Which is what I want.

Unfortunately, it's far too slow to use. It takes roughly a minute to do the sorting, whereas sorting was instantaneous without the workaround (and sorting was instantaneous when I tested the query in SQL Developer too). So, something about sorting on the query layer in the attribute table is going awry.

Re: Attribute table join (and sort) between two EGDB tables: How are the join and sort performed? (DB vs. application)

Bud — Wed, 19 Jun 2024 14:52:44 GMT

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