Select to view content in your preferred language

SDO_GEOMETRY functions are 20x faster than SDE.ST_GEOMETRY functions (startpoint XY)

2516
9
07-06-2022 02:29 PM
Bud
by
Honored Contributor

(edited)
Oracle 18c 10.7.1 EGDB:

I thought I'd share a test I did recently — for anyone who might be trying to decide between SDE.ST_GEOMETRY and SDO_GEOMETRY.


Select the startpoint X & Y from 14,000 single-part lines:


SDE.ST_GEOMETRY:

select
sde.st_x(sde.st_startpoint(shape)) as startpoint_x,
sde.st_y(sde.st_startpoint(shape)) as startpoint_y
from
sidewalks

STARTPOINT_X STARTPOINT_Y
------------ ------------
668287.112 4869366.88
...

Time: 20 seconds

 

SDO_GEOMETRY

select
    sdo_util.get_coordinate(shape,1).sdo_point.x as startpoint_x,
    sdo_util.get_coordinate(shape,1).sdo_point.y as startpoint_y
from
    sidewalks

STARTPOINT_X STARTPOINT_Y
------------ ------------
668287.112 4869366.88
...

Time: 0.9 seconds

 

Result: Getting the startpoint XY from SDO_GEOMETRY is 20x faster than SDE.ST_GEOMETRY in this case.

That's consistent with the testing I've done over the last couple of years. I've found ST_GEOMETRY functions to be slower than SDO_GEOMETRY in general. Of course, there are benefits to ST_GEOMETRY too, such as it being easier to learn than SDO_GEOMETRY (in my experience).

 

I just thought I'd share those results in case they're useful to anyone.

 

9 Replies
VinceAngelo
Esri Esteemed Contributor

That's not exactly a common use pattern, and therefore probably shouldn't be used as final arbiter for storage selection. 

I've had cases where SDO_GEOMETRY was 15-20% faster, and cases where ST_GEOMETRY was 40-120% faster, though I've mostly been using PostgreSQL in the past decade, with PostGIS geometry, exclusively (which has been 1-2 orders of magnitude faster returning millions of rows against some very disadvantaged Oracle configurations).

Best practice is to benchmark apples-to-apples for an entire solution, then evaluate from there, not just rely on a single datapoint.

- V

by Anonymous User
Not applicable

(I'm not an Oracle user.)

I got curious to look at the docs on these to see if the queries are equivalent...

Apparently the get_coordinates() is undocumented?

Is it possible to lookup SDE.ST_GEOMETRY by index?

It seems hard to make any real claims about performance without testing more here. The time difference just looks too large for these to be equivalent queries.

0 Kudos
Bud
by
Honored Contributor

Yes, get_coordinates() is undocumented. I've talked to folks on the Oracle Spatial team about that. They plan to document it in the next release.

If for some reason we didn't want to use get_coordinates(), maybe since it's undocumented, then there are other ways to get the startpoint X&Y from SDO_GEOMETRY.

The performance of the alternative techniques is roughly the same as get_coordinates(), although slightly more complicated:

select
    v.x,
    v.y
from
    sidewalks
cross join
    sdo_util.getvertices(shape) v
where
    v.id = 1

 Or

with 
function 
sdo_startpoint_x(shape sdo_geometry) return number is
begin
    return shape.sdo_ordinates(1);
end;    
function sdo_startpoint_y(shape sdo_geometry) return number is
begin
    return shape.sdo_ordinates(2);
end;
select
    sdo_startpoint_x(shape) as startpoint_x,
    sdo_startpoint_y(shape) as startpoint_y
from
    sidewalks

Or

select 
    json_value((shape).Get_GeoJson(),'$.coordinates[0][0]' returning number) as x,
    json_value((shape).Get_GeoJson(),'$.coordinates[0][1]' returning number) as y
from
    sidewalks

There is also:

sdo_lrs.geom_segment_start_pt

But that just returns a LRS geometry. We'd still need to get the XY from the SDO_ORDINATES_ARRAY, which can be done via cross join sdo_util.getvertices(shape). But that's more clunky than I'd like.

Related: Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)

 

The nice thing about ST_GEOMETRY is that it's easy to use, well documented, and there aren't several weird ways to do a thing...there's usually just one way to do it. But with SDO_GEOMETRY, as I demonstrated above, it's messier.

 

Is it possible to lookup SDE.ST_GEOMETRY by index?

I don't think so. That's part of the problem with ST_GEOMETRY. The ordinates are hidden in a blob. Whereas SDO_GEOMETRY ordinates are in plain sight in an array in an object attribute (easily accessible by index number).

Note: With ST_GEOMETRY, we can get a specific point/vertex via the point index number: SDE.ST_PointN. But that's just as slow as SDE.ST_StartPoint. And I assume you weren't really referring to that.

 

It seems hard to make any real claims about performance without testing more here. The time difference just looks too large for these to be equivalent queries.

Yes, the "startpoint function test" was just one test for a specific use case. I haven't done any official benchmarking with dozens of documented tests or anything like that.

But the test I did was legitimate & correct. And it's a good example of how slow ST_GEOMETRY functions can be when used on a medium-sized dataset. I stand by my statement that ST_GEOMETRY functions are generally slower. I've heard the same from others, including Esri staff.

by Anonymous User
Not applicable

Thank you for this great write up! I wish I could give more than 1 up vote - this type of post makes the community that much more valuable.

Bud
by
Honored Contributor

I stand by my statement that ST_GEOMETRY functions are generally slow compared to SDO_GEOMETRY functions. I've done lots of testing over the past couple of years. And the results are always the same. 

But I should clarify that my testing has been with constructor, accessor, and geometry functions. Not relational functions. I've tested dozens of constructor, accessor, and geometry functions, and I consistently find that they're slower than I need them to be.

And to be fair, ST_GEOMETRY has been perfectly fast when simply selecting the geometry column from a FC in a map. And the spatial indexing seems to work fine too. But that's not what I was referring to in this post...it's the functions that are slower than SDO_GEOMETRY.

I'd be very interested in seeing reproducible examples of ST_GEOMETRY constructor, accessor, or geometry functions being faster than SDO_GEOMETRY. I've not experienced that yet. 

Here's what I did to make sure the data was apples-to-apples:

  1. Created two new line FCs via Catalog with no extra columns, just the OBJECTID and SHAPE.
    1. One FC with the ST_GEOMETRY datatype.
    2. And one with the SDO_GEOMETRY datatype.
  2. Loaded features from an existing FC into the new FCs via the Catalog load tool.
  3. Tested equivalent functions for each datatype in queries in SQL Developer.
    1. Hit CTRL+END to select all rows in the query, not just the first 50 rows that are selected by default.

To me, that seems like a good way to do objective testing for functions.

Some additional thoughts on testing:

As mentioned, I'd be interested to see cases where ST_GEOMETRY functions (constructor, accessor, or geometry) are faster than SDO_GEOMETRY functions. They could be out there...and I just haven't seen them yet.

Bud
by
Honored Contributor

For what it's worth, SDO_Geometry’s SPATIAL_VECTOR_ACCELERATION is disabled in my database (18c), since it's only enabled by default in 21c+.

Oracle Spatial Vector Acceleration: Flick the Switch

I tried checking to see if it's enabled (from a non-DBA connection) in SQL Developer, but got errors:

Check if SPATIAL_VECTOR_ACCELERATION is enabled (as a non-dba)


I don't know much about SPATIAL_VECTOR_ACCELERATION. But it sounds like it will make SDO_Geometry even faster in some cases.

0 Kudos
Bud
by
Honored Contributor

I did a related test recently (Oracle):

Select road right-of-way polygons (~3,000) that have at least one intersecting point (road midpoints; ~3,000). Only select one row per polygon; I want a unique list of polygons that intersect at least one point.


1. ArcGIS Pro Select By Location tool (processes in RAM): 1 second


2. SDO_GEOMETRY database query: 10 seconds

  SELECT poly_objectid,
         pnt_objectid
    FROM (
          SELECT poly.objectid as poly_objectid,
                 pnt.objectid  as pnt_objectid,
                 row_number() over(partition by poly.objectid order by null) rn
            FROM polygons poly
      CROSS JOIN points pnt
           WHERE sdo_anyinteract(poly.shape, pnt.shape) = 'TRUE'
         )
   WHERE rn = 1

The above query would likely be faster if SPATIAL_VECTOR_ACCELERATION parameter was enabled in the database.
A related post: Select polygons that intersect points and a parallel post here.


3. SDE.ST_GEOMTERY database query: 50 seconds

  SELECT poly_objectid,
         pnt_objectid
    FROM (
          SELECT poly.objectid as poly_objectid,
                 pnt.objectid as pnt_objectid,
                 row_number() over(partition by poly.objectid order by null) rn
            FROM polygons poly
      CROSS JOIN points pnt
           WHERE sde.st_intersects (poly.shape, pnt.shape) = 1
         )
   WHERE rn = 1


It surprises me that my mediocre office computer with ArcGIS Pro —> Select By Location outperforms the enterprise Oracle database by such a wide margin. Why are databases so bad at spatial queries?

 

Related:

Bud
by
Honored Contributor

Using different data, but still relevant:

4. SDO_GEOMETRY database query (different query from #2)

I got an answer from @kpatenge on Stack Overflow that blows my previous SDO_GEOMETRY query out of the water.

It run instantly: 0.3 seconds.

select objectid from (
    select /*+ ordered use_nl (a,b) use_nl (a,c) */ 
        c.objectid as objectid,
        row_number() over(partition by c.objectid order by null) rn
    from 
        table(sdo_join('PNT','SHAPE','PLY','SHAPE')) a,
        pnt b,
        ply c
    where 
        a.rowid1 = b.rowid
        and a.rowid2 = c.rowid
        and sdo_geom.relate (b.shape, 'ANYINTERACT', c.shape, 1.0) = 'TRUE'
) where rn = 1;

 

0 Kudos
Bud
by
Honored Contributor

Again, using different data, but still relevant:

5. Mobile geodatabase ST_GEOMETRY database query: 45 seconds
The mobile gdb was stored on my computer's C drive.

   SELECT objectid
    FROM (SELECT ply.objectid,
                 row_number() over(partition by ply.objectid order by null) rn
            FROM ply
      CROSS JOIN pnt
           WHERE st_intersects(ply.shape, pnt.shape) = 1
         )
   WHERE rn = 1

Load ST_GEOMETRY .dll extension into SQLite/mobile geodatabase — Error: "not authorized"

0 Kudos