topic Re: SQL Query Performance Issues in Data Management Questions

SQL Query Performance Issues

JoshBillings — Wed, 08 May 2024 16:48:09 GMT

We are setting up a new Oracle enterprise geodatabase. This set up involves an external procedure that runs on a weekly basis to update data on a table.

Part of this external procedure has a SQL query (below) to select a subset of data based on a spatial relationship using the ST_Intersects function. In our current environment, this statement takes less than a minute to run. In our new environment, we are encountering performance issues on the "AND t1.field2 != t2.field3" portion of this statement - to the tune of it never completing. If we take this statement out of the query, the query runs fine, so we have been able to narrow this performance issue to this particular statement.

Both tables (Table1 and Table2 in the query below) have spatial indexes and attribute indexes associated with them.

Any ideas on what we could try to get this to successfully run?

SELECT t1.field1, t1.field2, t2.field3 FROM Table1 t1, Table2 t2 WHERE sde.st_intersects(t2.shape, sde.st_pointonsurface((t1.shape))) = 1 AND t1.field2 != t2.field3 AND t2.field3 != 'Attribute';

Thanks - Josh

Re: SQL Query Performance Issues

MarceloMarques — Wed, 08 May 2024 16:58:11 GMT

@JoshBillings - compare the Query Execution Plan in the old server with the new server to determine what might be causing the problem.

Make sure the new server has the esri st_geometry library properly installed and check if it is working.

Configure extproc to access ST_Geometry in Oracle—ArcGIS Pro | Documentation

You need to use the version of the esri st_geometry library that matches the version of the geodatabase arcsde repository.

Re: SQL Query Performance Issues

VinceAngelo — Wed, 08 May 2024 18:56:33 GMT

Placing join tables in the FROM list hasn't been best practice since Oracle 8.
I strongly urge you to use JOIN syntax. And "!=" best written "<>".

The optimizer might prefer placing the point in the first argument, but if Table2
is indexed on t2.field3, and that has higher selectivity, you might want to specify
the non-spatial constraints first (though NOT is usually an awful JOIN constraint).

- V

Re: SQL Query Performance Issues

MarceloMarques — Wed, 08 May 2024 21:19:01 GMT

@JoshBillings

Oracle INNER JOIN Demonstrated with Practical Examples (oracletutorial.com)

Oracle LEFT JOIN By Practical Examples (oracletutorial.com)

RIGHT OUTER JOIN in Oracle By Practical Examples (oracletutorial.com)

Re: SQL Query Performance Issues

Bud — Wed, 08 May 2024 21:49:48 GMT

Edit: Never mind. The syntax is the same as your query.

As a last resort, you could try using the Select Layer by Location — Intersect (DBMS) geoprocessing tool which creates a spatial SQL query in a database view. Maybe the syntax or performance would be different. You could steal the SQL from the view to use in your query.