Speed of Oracle ST_GEOMETRY vs. SDE COMPRESSED BINARY in unpacking coordinate data

[danan]

ST_GEOMETRY seems awfully slow at unpacking coordinate data via the stardard SHAPE.points function given the quantity of data. I've performed queries that don't even use the st_envintersects operator (the one causing inappropriate Full Table Scans). I just do a WHERE OBJECTID between, say, 1 and 10. So the access path is via a non-spatial index. None of the features have an excessive number of vertices. Again, simply unpacking coordinates itself seems slow. It's even slower using extproc and the sde.st_astext(shape) function. But my focus is plain vanilla SHAPE.points querying (the type of query submitted by ArcGIS Explorer 1500).

Q1) Has anyone tested performance using the old SDE (COMPRESSED) BINARY--the BLOB spatial data type? I'd at least like to prove that ST_GEOMETRY is the villain (if that's the case)--and that we could switch to SDE BINARY as a work-around. This would be for our publishing SDE instance--one we're not even using exproc SQL spatial functions in. We'd like to keep using ST_GEOMETRY for our editing instance.

Q2) Has anyone temporarily reverted to SDE BINARY due to performance performance problems with ST_GEOMETRY?

Q3) Does anyone have a test query for returning coordinate data from a feature class in SDE BINARY format? Imagine I'd have to join the business table with its associated F-table via the b-table's SHAPE column FK.

With ST_GEOMETRY now being the default spatial data storage type, it's clear this is the focus of present and future development. So I'm confident things will continue to improve. But for now, I have a performance issue to contend with.

I'd love to see if there's any difference in speed executing queries in SQL*PLUS between the same feature class with one in ST_GEOMETRY and one in SDE BINARY. Switching to SDE BINARY alone may be worthwhile if it gets rid of Full Table Scans.

I'm relucant to tweak statistics gathering or modify the st_envintersects operator in order to get around the FTS issue. Could create problems with future upgrades. Although again, with the particular query I'm testing, I'm not getting FTSes. Just "slow" (or what seems slow to me anyway) retrieval of coordinates.

System details:

* We're running ArcSDE 9.3.1 (no service packs yet) on an Oracle 10.2.0.3 instance
* OS is Redhat Linux)
* Our Oracle clients are at 11g
* During testing, Oracle is not breaking a sweat. The slow performance is with a single user querying the instance. The SGA is properly sized--nothing's getting aged-out. Subsequent runs of the test query perform about the same.

Things seem slow even in a best case scenario baseline like I've described above. So could the unpacking of ST_GEOMETRY coordinate data be a sort of "hidden" bottleneck? No other components of the system show any stress.

Hope this all makes sense.

[VinceAngelo]

Comparing ST_GEOMETRY with SDEBINARY is a rigged fight. LONG RAW is much faster
than the LOB family of types. The format of the points BLOB in ST_GEOMETRY and SDELOB/
SDEBINARY is exactly the same, so this isn't an "unpacking" issue, just inherent performance
of the underlying RDBMS BLOB type.

Given that ST_GEOMETRY often out-performs SDELOB (due to elimination of a table and the
potential for inline storage), and given the instability of LONG RAW at 10gR2 (it's deprecated,
so sites with difficulty have no recourse) and lack of availability at 11g, an ST_GEOMETRY
default type makes good sense.

I had similar optimizer issues with SDO_GEOMETRY types, and found that a view against the
table with an indexing hint in the view addressed full table scan issues. I've never reproduced
the optimizer issue with ST_GEOMETRY, so I don't know if the same workaround is applicable.

- V

[danan]

Comparing ST_GEOMETRY with SDEBINARY is a rigged fight. LONG RAW is much faster
than the LOB family of types. The format of the points BLOB in ST_GEOMETRY and SDELOB/
SDEBINARY is exactly the same, so this isn't an "unpacking" issue, just inherent performance
of the underlying RDBMS BLOB type.

Given that ST_GEOMETRY often out-performs SDELOB (due to elimination of a table and the
potential for inline storage), and given the instability of LONG RAW at 10gR2 (it's deprecated,
so sites with difficulty have no recourse) and lack of availability at 11g, an ST_GEOMETRY
default type makes good sense.

I had similar optimizer issues with SDO_GEOMETRY types, and found that a view against the
table with an indexing hint in the view addressed full table scan issues. I've never reproduced
the optimizer issue with ST_GEOMETRY, so I don't know if the same workaround is applicable.

- V

Thanks Vince.

Dana

[ManojGarg]

Comparing ST_GEOMETRY with SDEBINARY is a rigged fight. LONG RAW is much faster
than the LOB family of types. The format of the points BLOB in ST_GEOMETRY and SDELOB/
SDEBINARY is exactly the same, so this isn't an "unpacking" issue, just inherent performance
of the underlying RDBMS BLOB type.

Given that ST_GEOMETRY often out-performs SDELOB (due to elimination of a table and the
potential for inline storage), and given the instability of LONG RAW at 10gR2 (it's deprecated,
so sites with difficulty have no recourse) and lack of availability at 11g, an ST_GEOMETRY
default type makes good sense.

I had similar optimizer issues with SDO_GEOMETRY types, and found that a view against the
table with an indexing hint in the view addressed full table scan issues. I've never reproduced
the optimizer issue with ST_GEOMETRY, so I don't know if the same workaround is applicable.

- V

Hi Vince,

Do we have comparision or recommendation of using storage type between sdebinary, ST_Geometry and SDO_Geometry?
I have to create a SDE GDB with geometric network having millions of features. Which storage type will be good from performance point of view? Will SDO_Geometry be better than ST_Geometry?
Any article or any help you can offer will be of great help.

Thanks
MAnoj

[VinceAngelo]

I'm forbidden from publishing performance comparisons.  "Millions of features"
doesn't really make a difference for most mapping applications, since you
*never* want to draw that many features at one time.

It's safe enough to assume that all LOB-based geometry types will have similar
performance when tuned (for small rowset sizes).  You are welcome to evaluate
the options available, but that is best done with a representative sample
(preferably 100%) for the various operations your application will require
(with a range of scales, and in a number of areas).

Best performance (independent of format) will be found in a table which is not
spatially fragmented (features that are near one another in space are near one
another on disk).  This can be challenging in a heavily edited environment,
but you'll notice a difference if the table is defragmented.

- V

[SimonLynch]

Hello, i thought i may as well post onto the end of this posting seeing the issues are similar.

We have Oracle 11gR2 db on Sun Solaris and ArcGIS 10.1 and have not installed the SDE application but the geodatabase is enabled for SDE geometry. We have placed a couple of large *test* data sets onto the db with a view to test redisplay performance of SDO, SDELOB and ST_GEOMETRY. The database has not been tuned specifically:

Lines (1M records) dataset zoomed into 4 bookmarked areas in ArcGIS10.1. Timing the time to complete display:
DataType         | Area1         | Area2      | Area3 | Area4
SDELOB           | 15 sec | 5 sec | 21 sec |  <1 sec
ST_GEOMETRY | 50 sec | 23 sec | 64 sec |   4  sec
SDO                | 94 sec | 70 sec |185 sec |  11 sec

Polygons (400k records) - as above:
DataType | Area5 | Area6 | Area7 | Area8
SDELOB | 1 sec | 4 sec | 9 sec | 2 sec
ST_GEOMETRY | 8 sec | 30 sec | 36 sec | 11 sec
SDO | 4 sec | 9 sec | 23 sec | 12 sec

My read of performance results indicate that for both datasets, SDELOB was consistently significantly faster (3+> times) than both ST_GEOMETRY and SDO. I wonder if anyone would like to comment on these results, as they are not what i expected. (I expected ST_GEOMETRY to win given what i have read on ESRI Help and Forums). Does this indicate our database settings are not optimised? Any comment or guidance appreciated? Or have i missed something?

Thanks in advance.

[VinceAngelo]

Four areas is a tiny sample set -- My evaluations usually involve scores of extents
at various scales.  Testing without tuning defeats the ability for each format to shine.
Database "temperature", vertex density, LOB segment allocation, and a number of
other aspects should be considered when evaluating results.  In the end, every
dataset is different, and each one may return different results.

- V

[DaleBrooks]

Capturing display times is an important part of your analysis, but can't tell you just what the performance difference is based on.

Unpacking time, as you refer to, is one possibility, but there are many others.

Have you done Oracle traces and PL/SQL profiles in the database while performing the draws?  Recommend separating db time from network time from client time, at a minimum.

Dale