How To: Get Maximum Performance with Read-Only Relates and Hash Tables

Procedure

Question

How can performance be maximized with read-only relates to an external DBMS such as Oracle, Ingres, Informix, or Sybase?

Cause

Whenever regular relate syntax is used to access information from a DBMS table associated with a read-only relate, a full table scan of the DBMS table is done, and a hash table is created. This can take quite a while when the DBMS table is very large.

Answer

In order to speed up such relates, first be sure there is an index on both the INFO relate item and the DBMS table's relate column. Then use DBMSSET with the WHERECLAUSE to limit the size of the hash table that is created; thus, reducing the time it takes to retrieve information through the read-only relate. This may take a little experimentation, since the WHERE condition must be provided to include the desired records.

The example below selects records based on values in a related Oracle table. The DBMSSET WHERECLAUSE is used to insure that the hash table created contains only the records which meet the criteria specified in the where statement:

Arcplot: DBMSSET ORACLE WHERECLAUSE REL1 LAST_NAME = 'Jones'
Arcplot: RESELECT COVER POLY REL1//LAST_NAME = 'Jones'

Note:
Hash tables are only used with read-only relates and regular relate syntax. In other words, hash tables are not used with read-write relates, or with native mode queries.  Hash tables were a feature introduced at ArcInfo 7.x.

In many cases, it has been found that using regular relate syntax with the hash tables and the WHERECLAUSE provides faster response time than using native mode queries.

Related Documents

The online ArcDoc contains additional information under Managing tabular data > The relate environment > Tuning read-only relates to external DBMS tables.

Article ID:000001373

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Get maximum performance with read-only relates and hash tables

Procedure

Discover more on this topic