English

Problem: Display performance of ArcSDE spatial views could be slow when the definition of join fields is different in participating feature classes and tables

Description

Spatial Views can be created by joining a feature class and a table using the 'sdetable -o create_view' command.

The join is based on a common field that should ideally have the same definition in both the feature class and table.

In certain cases, the join can be based on 'text' fields as seen from ArcGIS, and the definition of these fields could be different at the database level.
In those cases, the performance of spatial views are found to be very slow.

Cause

Beginning with ArcGIS 9.2, character data types in ArcSDE geodatabases are enabled for Unicode, which makes it easier to work with multiple languages.

By default with ArcGIS 9.2 and 9.3, character data is created with Unicode encoding in SQL Server and Oracle databases unless the DBTUNE parameter UNICODE_STRING is set to false under the DEFAULTS configuration keyword.

The text fields created in ArcGIS are mapped to nvarchar data types in Oracle and Sql Server 2005 due to UNICODE Support.

When attempting to create a Spatial View by joining a feature class and a table through a common field defined as 'text' in ArcGIS, the definition of this field could be different in the database level in certain scenarios as described below.

For example, the feature class is created in ArcSDE 9.2 or 9.3 having UNICODE enabled and the join is going to be based on a field defined as 'text' in ArcGIS. This field is defined as nvarchar in the database (Oracle10g, Sql Server 2005).

If the participating table is created through database tools, the definition of the common field will remain as varchar.

In this scenario, the database tools and ArcSDE will not report any message while creating a spatial view; however, the display performance of such spatial views are found to be slow.

The following is an example of the scenario described above:

1)A feature class created in ArcSDE 9.2 or 9.3 using ArcGIS 9.2 or 9.3 having UNICODE enabled. Note that PARID field is defined as SE_NSTRING in ArcSDE. In ArcGIS, it's defined as 'Text'.

C:\Kundu\ArcSDE\ora10gexe\bin>sdetable -o describe -t paparcel_uni -u paul -p paul -i 9292

ArcSDE 9.2 for Oracle10g Build 1173 Thu May 31 12:02:22 2007
Attribute Administration Utility
-----------------------------------------------------

Table paparcel_uni:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
OBJECTID SE_INT32 NOT NULL 10 SDE Set
PARID SE_NSTRING NULL 17
CTY SE_NSTRING NULL 2
RNG SE_NSTRING NULL 2
TWP SE_NSTRING NULL 2
SEC SE_NSTRING NULL 2
SUBD SE_NSTRING NULL 2
BLK SE_NSTRING NULL 3
LOT SE_NSTRING NULL 4
LOCID SE_NSTRING NULL 17
UTFLAG SE_NSTRING NULL 1
MRFLAG SE_NSTRING NULL 1
GDSSTRU SE_NSTRING NULL 32
DISPCOMMENT SE_NSTRING NULL 196
STATUSFLAG SE_NSTRING NULL 1
CREATEDATE SE_DATE NULL 0
HISTORYDATE SE_DATE NULL 0
TAXYEAR SE_INT16 NULL 5
ENDTAXYEAR SE_INT16 NULL 5
ISVISIBLE SE_NSTRING NULL 1
ACRES SE_FLOAT64 NULL 38,8
AREA SE_FLOAT64 NULL 38,8
LEN SE_FLOAT64 NULL 38,8
SHAPE SE_SHAPE NULL 0

2) This is a table created in Oracle, registered with ArcSDE. Please note that PARID is defined as SE_STRING in ArcSDE; whereas, it is defined as 'Text' in ArcGIS.
C:\Kundu\ArcSDE\ora10gexe\bin>sdetable -o describe -t land -u paul -p paul -i 9292
ArcSDE 9.2 for Oracle10g Build 1173 Thu May 31 12:02:22 2007
Attribute Administration Utility
-----------------------------------------------------

Table land:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
PARID SE_STRING NOT NULL 30
TAXYR SE_INT16 NOT NULL 4
LTYPE SE_STRING NOT NULL 1
CODE SE_STRING NOT NULL 3
FRONACT SE_FLOAT32 NULL 5,1
DEPTH SE_INT16 NULL 4
DEPTBL SE_INT16 NULL 3
DEPFACT SE_FLOAT32 NULL 3,2
SF SE_INT32 NULL 10
ACRES SE_FLOAT64 NULL 12,4
UNITS SE_FLOAT64 NULL 12,2
OSIZE SE_FLOAT64 NULL 38,10
ORATE SE_FLOAT64 NULL 38,10
BSIZE SE_FLOAT64 NULL 38,10
BRATE SE_FLOAT64 NULL 38,10
BINCR SE_FLOAT64 NULL 38,10
BDECR SE_FLOAT64 NULL 38,10
INFL1 SE_STRING NULL 2
INFL2 SE_STRING NULL 2
INFLU SE_INT16 NULL 3
COMRES SE_STRING NULL 1
PRICE SE_INT32 NULL 10
ADJFACT SE_FLOAT32 NULL 6,4
CLASS SE_STRING NULL 4
AGFLG SE_STRING NULL 1
USER1 SE_STRING NULL 20
LANDCT SE_FLOAT64 NULL 38,10
NBHD SE_STRING NULL 8
LUC SE_STRING NULL 4

3) A spatial view can be created by joining the above layer and table using PARID as the join field. While creating a spatial view, it does not give any warning or error message, as the underlying database supports the same; however, display performance of this spatial view is found to be very slow.

C:\Kundu\ArcSDE\ora10gexe\bin>sdetable -o create_view -T sv_land_nvarchar -t "PAPARCEL_UNI p,LAND l" -c "p.objectid,p.parid,p.shape,l.taxyr,l.ltype,l.code,l.fronact,l.depth,l.deptbl,l.depfact,l.sf,l
cres,l.units,l.osize,l.orate,l.bsize,l.brate,l.bincr,l.bdecr,l.infl1,l.infl2,l.influ,l.landct,l.class,l.user1,l.nbhd,l.luc" -w "p.parid=l.parid(+)" -u paul -p paul -i 9292
ArcSDE 9.2 for Oracle10g Build 1173 Thu May 31 12:02:22 2007
Attribute Administration Utility
-----------------------------------------------------
Successfully created view sv_land_nvarchar.

Solution or Workaround

The definition of the join field must be the same in any participating feature classes and tables.