topic Re: Spatial Index Inefficient in Data Management Questions

Spatial Index Inefficient

User35489 — Wed, 15 May 2013 06:07:12 GMT

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

Re: Spatial Index Inefficient

VinceAngelo — Wed, 15 May 2013 10:59:52 GMT

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

Re: Spatial Index Inefficient

User35489 — Wed, 15 May 2013 12:03:09 GMT

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

Re: Spatial Index Inefficient

VinceAngelo — Wed, 15 May 2013 12:10:10 GMT

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

- V

Re: Spatial Index Inefficient

EmadAl-Mousa — Wed, 15 May 2013 19:04:47 GMT

are the geomtery storage identical in both layers ?

Re: Spatial Index Inefficient

RobertBorchert — Wed, 15 May 2013 19:07:29 GMT

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

Re: Spatial Index Inefficient

User35489 — Sat, 18 May 2013 06:05:27 GMT

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

Re: Spatial Index Inefficient

User35489 — Sat, 18 May 2013 06:06:36 GMT

are the geomtery storage identical in both layers ?

It differs, 9.3 has SDEBINARY and 10.1 has ST_GEOMETRY.

-AS

Re: Spatial Index Inefficient

VinceAngelo — Sat, 18 May 2013 14:57:30 GMT

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

Re: Spatial Index Inefficient

User35489 — Sun, 19 May 2013 06:58:57 GMT

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

Re: Spatial Index Inefficient

VinceAngelo — Mon, 20 May 2013 13:01:01 GMT

I did an experiment at a user site that was having network issues a few years ago.
We tried splitting the table storage into independent disks, spatial defragmentation
changing the spatial index size (both smaller and larger), and coordinate reference
scale reduction. I would have thought that they all would have significant benefit,
but the big winner was coordref change. All of the changes had some give, though --
you might have been able to get away with a 10m or 5m XY scale (10x or 2x of the
1m precision), just as a 10-20% difference in single grid size produced no significant
change.

- V

Re: Spatial Index Inefficient

BenArmstrong — Wed, 22 May 2013 19:49:42 GMT

Is there a similar way to do this in SQL?

Re: Spatial Index Inefficient

VinceAngelo — Wed, 22 May 2013 22:00:03 GMT

Sure, I guess. Unless you're in a situation where it isn't possible.
Or wouldn't matter.

What is your situation?

- V

Re: Spatial Index Inefficient

MichaelSnook — Thu, 15 Aug 2013 18:26:55 GMT

Hi Vince,

We're experiencing terribly slow drawing performance with our parcel fabric and I'm wondering if this could be our issue. Is it possible to 'downgrade' the default coordinate reference parameters as below?

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

Thanks,
Mike

Re: Spatial Index Inefficient

VinceAngelo — Thu, 15 Aug 2013 20:09:59 GMT

You didn't post it here, but elsewhere you indicated you were using SQL-Server GEOMERTY
storage, which would be completely oblivious to ArcSDE precision (only SDEBINARY, SDELOB
and ST_GEOMETRY storage size would be impacted the coordinate reference changes).

You probably want to contact Tech Support about your parcel fabric issue.

- V