Improve performance of ST_PointN() function (SDE.ST_GEOMETRY in Oracle)

Bud — Sun, 10 Apr 2022 13:24:21 GMT

When troubleshooting performance issues with SDE.ST_GEOMETRY queries in Oracle 12c/18c, I’ve found that the ST_PointN function is very slow when used in bulk (10x slower than many other ST_GEOMETRY functions).

The ST_PointN function doesn’t seem do anything special. It’s not doing complex spatial analysis or anything like that; it just gets a specific point from the SHAPE blob. So I wonder if there might be an issue with how it’s designed.

For example, changing the the function so that it’s DETERMINISTIC might make it faster.

Could Esri investigate/improve the performance of that function?

Re: Improve performance of ST_PointN() function (SDE.ST_GEOMETRY in Oracle)

Bud — Sun, 10 Apr 2022 19:52:21 GMT

For example, a query that generates rows for each vertex (3000 features):

1 SELECT a.ROAD_ID 2 ,b.NUMBERS VERTEX_INDEX 3 ,a.SDE.ST_X(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS X 4 ,a.SDE.ST_Y(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS Y 5 FROM ROAD a 6 CROSS JOIN NUMBERS b 7 WHERE b.NUMBERS <= SDE.ST_NumPoints(a.SHAPE) ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 1 | MERGE JOIN | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 2 | INDEX FULL SCAN | R23715_SDE_ROWID_UK | 30 | 90 | | 1 (0)| 00:00:01 | |* 3 | SORT JOIN | | 3997 | 1018K| 2392K| 261 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL| ROAD | 3997 | 1018K| | 34 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 3 - access(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))" " filter(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))"

That query takes 30 seconds to run, which is far too long. In comparison, pretty much any other query on the FC runs instantly.

Testing/benchmarking:

I created two tables (both contain 10,000 rows): ROADS_BM and ROADS_STARTPOINT_BM. I ran simple queries on the tables using each of the functions that are involved. Here are the results:

Function documentation: ST_X, ST_Y, ST_NumPoints, ST_PointN

The result? ST_PointN is the problem. Its 9.5 second response time is slow compared to the other functions.

So I think that proves that the performance of ST_PointN needs to be improved.

idea Improve performance of ST_PointN() function (SDE.ST_GEOMETRY in Oracle) in ArcGIS Enterprise Ideas

Improve performance of ST_PointN() function (SDE.ST_GEOMETRY in Oracle)

Re: Improve performance of ST_PointN() function (SDE.ST_GEOMETRY in Oracle)