FAQ: Performance of Views Containing Join, or Small Subset of Original Feature Class Are S

Answer

** Internal Publish Only! This article may contain information that is not intended for external circulation. **

This has been encountered a number of times. Here are the answers as to why it occurs.

Environment:
ArcSDE 8.3 SP1
Oracle 9.2.0.4
Solaris 9
ArcMap 8.3

How to reproduce:
- Load, for example, the churches dataset from ArcGIS 9 CD using ArcCatalog. Call it "churches" (has about 138,500 features). (This could be any feature class you choose)
- Create a table for later use in the view as a join:

Code:
create table ch_att as
select objected, name
from churches
where objected < 100;

Note:
Make sure to place an index on the objectid column for the join.  (Critical for performance)

. Create a view using sdetable -o create_view:

Code:
sdetable -o create_view -T V_church -t "church,ch_att" -c "shape,church.objectid, ch_att.name " -w "church.objectid=ch_att.objectid" -u gisdba -p gisdba

Select count(*) from V_churches;

Returns 99 as row count

View that is created, as seen in enterprise manager:

Code:
SELECT /*+ FIRST_ROWS */  shape, church.objectid, ch_att.name 
FROM  GISDBA.CHURCH, GISDBA.CH_ATT 
WHERE church.objectid=ch_att.objectid

This is the main clause of the problem - as shown in the trace file:

********************************************************************************

SELECT /*+ INDEX(SHAPE F37_UK1) */ S_.eminx,S_.eminy,S_.emaxx,S_.emaxy, 
  SHAPE.fid,SHAPE.numofpts,SHAPE.entity,SHAPE.points,SHAPE.rowid 
FROM
 (SELECT /*+ INDEX(SP_ S37_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy 
  FROM GISDBA.S37 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3 
  AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= 
  :7 AND SP_.emaxy >= :8) S_,  GISDBA.V_CHURCH , GISDBA.F37 SHAPE WHERE 
  S_.sp_fid = SHAPE.fid AND S_.sp_fid = GISDBA.V_CHURCH.SHAPE 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.08       0.20          0       7745          0          87
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.09       0.25          0       7745          0          87

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 82  

Rows     Row Source Operation
-------  ---------------------------------------------------
     87  NESTED LOOPS  (cr=7658 r=0 w=0 time=201597 us)
     87   NESTED LOOPS  (cr=7482 r=0 w=0 time=200446 us)
   3725    NESTED LOOPS  (cr=7480 r=0 w=0 time=189075 us)
   3725     VIEW  (cr=28 r=0 w=0 time=148248 us)
   3725      SORT UNIQUE (cr=28 r=0 w=0 time=146506 us)
   3725       FILTER  (cr=28 r=0 w=0 time=17088 us)
   3725        INDEX RANGE SCAN S36_IX1 (cr=28 r=0 w=0 time=15270 us)(object id 44993)
   3725     TABLE ACCESS BY INDEX ROWID CHURCH (cr=7452 r=0 w=0 time=32687 us)
   3725      INDEX UNIQUE SCAN A36_IX1 (cr=3727 r=0 w=0 time=16423 us)(object id 44991)
     87    INDEX UNIQUE SCAN C_ATT_OB_IDX (cr=2 r=0 w=0 time=5538 us)(object id 45003)
     87   TABLE ACCESS BY INDEX ROWID F36 (cr=176 r=0 w=0 time=933 us)
     87    INDEX UNIQUE SCAN F36_UK1 (cr=89 r=0 w=0 time=493 us)(object id 44988)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net more data to client                     3        0.00          0.00
  SQL*Net message from client                     1        8.96          8.96
********************************************************************************

This part of the query is doing the spatial search:-

Code:
SELECT /*+ INDEX(SP_ S37_IX1) */ DISTINCT sp_fid,eminx,eminy,emaxx,emaxy 
  FROM GISDBA.S37 SP_   WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3 
  AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= 
  :7 AND SP_.emaxy >= :8)

Roughly speaking, this is where it is in the explain plan:-

Code:
  3725      SORT UNIQUE (cr=28 r=0 w=0 time=146506 us)
   3725       FILTER  (cr=28 r=0 w=0 time=17088 us)
   3725        INDEX RANGE SCAN S36_IX1 (cr=28 r=0 w=0 time=15270 us)(object id 44993)

What do you take away from this?

When you do a zoom, the spatial search is done first, and returns all the features that fall in the spatial area. From the features that are in the spatial area, the where clause is applied last.

For the extent I zoomed to, you can see 3725 features were returned. Out of these, 87 met the where clause criteria.

Another interesting part from the explain plan is the following line:
3725 SORT UNIQUE (cr=28 r=0 w=0 time=146506 us)

Which probably was due to the distinct clause. As soon as you have a distinct clause, there is a sort. If there is not enough room in RAM to do the sort, it will go to disk.

What you can do to increase the performance:
. Make sure you have set Oracle memory allocation
. Restrice the zoom level of the layer, as you would the original data source
. Look at other way to present the data.
Check the value you have for the PGA_AGGREGATE_TARGET in Oracle. You need to have this around the 200M mark. Also make sure you have the workarea_size_policy set to auto so this takes effect. Refer to Oracle for further details.

Also make sure you have the Oracle SGA configured and tuned.

What is the bottom line though?
As it is, this will be as slow, probably slower, than the original feature class. If you zoomed to an area, all the features from the original feature class are retrieved, and then the additional where clause is applied, thus the poor performance.
Work arounds:
. Create a static feature class, that is updated on a regular basis.
You have a materialized view that is updated on a regular bases. Just do the same with the view. Use sdeadmin commands in a batch or shell script, and have it run to create the new feature class at some point in time. This will offer the best performance.

Note:
At ArcSDE 9.0, when you add the view to ArcMap as a feature class, one of the properties of the layer is to set if you want a spatial, or attribute query to be done first.  Therefore, in ArcGIS 9.0, you can get around this performance problem.

Article ID:000006985

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Performance of views containing join, or small subset of original feature class are slow

Answer

Discover more on this topic