Spatial Index Inefficient

[User35489]

Dear Admins,

I imported a layer containing polygon features around 25000 records from Geodatabase (9.3) to Geodatabase(10.1), it created a layer, attribute index and spatial index as usual.
I found that the layer is much more faster in 9.3 than in 10.1, for sure it is not because of version change.
For tuning i tried Analyse, Rebuild Spatial Index too
I was wondering what measures could help me to improve layer process faster ?
How to create a spatial index in an efficient way ? Is it OK if we do not create a spatial index ?


9.3 Environment: RHEL 5, Oracle 11.2.0.2.0, ArcSDE 9.3 SP1
10.1 Environment: RHEL 6, Oracle 11.2.0.3.0, ArcSDE 10.1 SP1


Waiting eagerly for replies
Thanks
Abdullah

[VinceAngelo]

Please provide the necessary details about the layers in question.
'sdelayer -o describe_long' output from each instance would be a
good start.  Some sort of timing information would be useful
as well.

- V

[User35489]

FYR

It takes 7 Sec in 9.3 and 17 Sec in 10.1

ArcSDE 9.3  for Oracle11g Build 546 Thu Sep 18 12:35:50  2008
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: TEST
Table Name ...........: TABLEABC
Spatial Column .......: SHAPE
Layer Id .............: 8930
SRID .................: 406
Minimum Shape Id .....: 1
Offset ...............:
  falsex:     -10000.000000
  falsey:     -10000.000000
System Units .........:  819200000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.00002
Spatial Index ........:
  parameter:    SPIDX_GRID,GRID0=0.29,GRID1=1.16,FULL
  exist:        Yes
  array form:   0.29,1.16,0
Layer Envelope .......:
  minx:        31.09743,        miny:        14.53603
  maxx:        55.69328,        maxy:        34.39108
Entities .............: nac+
Layer Type ...........: SDE
Creation Date ........: Wed Sep 26 09:55:23 2012
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_Ain_el_Abd_1970",DATUM["D_Ain_el_Abd_1970",SPHEROID["International_1924",6378388.0,297.0]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: DEFAULTS

ArcSDE 10.1  for Oracle11g Build 768 Thu Aug 23 15:57:13  2012
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: TEST
Table Name ...........: TABLEABC
Spatial Column .......: SHAPE
Layer Id .............: 219
SRID .................: 5
Auth SRID.............: 4204
Minimum Shape Id .....: 1
Offset ...............:
  falsex:       -400.000000
  falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
  parameter:    SPIDX_GRID,GRID0=0.29,FULL
  exist:        Yes
  array form:   0.29,0,0
Layer Envelope .......:
  minx:        31.09743,        miny:        14.53603
  maxx:        55.69328,        maxy:        34.39108
Entities .............: nac+
Layer Type ...........: Extended SQL Type/ST_GEOMETRY
Creation Date ........: Wed May 15 09:15:05 2013
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_Ain_el_Abd_1970",DATUM["D_Ain_el_Abd_1970",SPHEROID["International_1924",6378388.0,297.0]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: TEST_DEFAULT



Thanks for your time
Abdullah

[VinceAngelo]

7 vs 17 seconds to do what?  Draw the entire layer?

- V

[EmadAl-Mousa]

are the geomtery storage identical in both layers ?

[RobertBorchert]

you should also recreate your mxd in 10.1

Export everything as a layer file and add them to a new 10.1 mxd

or select all elements in the layout and paste them into your new 10.1 mxd

[User35489]

7 vs 17 seconds to do what?  Draw the entire layer?

- V

It takes 7 seconds in 9.3 to draw entire layer and takes 17 seconds in 10.1. I tried analyse, rebuilding Spatial indexes etc. It did not worked out.

-AS

[User35489]

are the geomtery storage identical in both layers ?

It differs, 9.3 has SDEBINARY and 10.1 has ST_GEOMETRY.

-AS

[VinceAngelo · Accepted solution]

So your subject line is a non-sequitur, because the spatial index is never used
in  a full-extent query.  In fact, if you forced a full-extent query to use the spatial
index, it would be much slower.

All you're seeing is the difference between a LONG RAW query and a LOB query.
Oracle deprecated LONG RAW use more than a decade ago, and it fails frequently
at some sites, so you really shouldn't be using it.

You can gain some of that perfromance back by proactively managing your
coordinate reference parameters -- Instead of defaulting to the -400,-400,1billion
XY offsets and scale, try using -400,-400,1million.  Lopping off those extra digits
will cut the storage requirements (at the cost of going from sub-millimeter to
sub-decimeter precision).

- V

[User35489]

So your subject line is a non-sequitur, because the spatial index is never used
in  a full-extent query.  In fact, if you forced a full-extent query to use the spatial
index, it would be much slower.

All you're seeing is the difference between a LONG RAW query and a LOB query.
Oracle deprecated LONG RAW use more than a decade ago, and it fails frequently
at some sites, so you really shouldn't be using it.

You can gain some of that perfromance back by proactively managing your
coordinate reference parameters -- Instead of defaulting to the -400,-400,1billion
XY offsets and scale, try using -400,-400,1million.  Lopping off those extra digits
will cut the storage requirements (at the cost of going from sub-millimeter to
sub-decimeter precision).

- V

Great tip vince, I tried with -400, -400, 1 million. it drastically changed the access speed. Your inputs are really benificial. Do you suggest any more changes ?

With Best Regards,
-AS