Optimizing STIntersects queries

[JensDalsgaard]

A customer of ours (an electric utility company) wants to increase the use of the grid data registered in ArcGIS. Recently their BI department has asked us to help in establishing various reports / queries from outside ArcGIS.

An example of such query is to list substations per maintenance area / sub area. This query delivers the requested list:

Select      Byg.Navn,

            eft.Betegnelse,

            str(eft.Year)

From        ELDB.EL.BYGVAERK_EVW byg,

            ELDB.EL.Eftersynsomraader_evw eft

Where       Byg.BygvaerkArtKode IN (1,2) AND

            Byg.SHAPE.STIntersects(eft.SHAPE) = 1

Order by    eft.Betegnelse,

            str(eft.Year)

(BYGVAERK_EVW is the versioned view of substations and Eftersynsomraader_evw is the versioned view of maintenance areas).

Executing this query in MS SQL Server Management Studio takes 3 minutes.

If in ArcMap I do a spatial join between the two feature classes I am able to present the result in approximately 1½ minutes. If ArcMap is able to deliver the requested answer in 1½ minutes I don't see why asking for the same trough non-ArcGIS queries shouldn't be able to also deliver the answer in 1½ minutes - or even faster.

I have a feeling that the answer to optimizing my query lays in the use of spatial indexes. But how should I approach implementing this?

Any help would be appreciated.

[ThomasDunn]

Hello, Jens.

Have you used the SQL Server Profiler to see the SQL that ArcMap is executing on the database? You will probably find that it is different than the SQL you mentioned above. It may even be multiple SQL statements.

ORDER BY may be the culprit here. You use STR (Transact-SQL)​ on one of the sort attributes. You probably don't have an index built on the function STR(eft.year). So, SQL Server would have to do much more work to perform this ORDER BY.

It is also possible that the spatial index is not being used. The best way to find out is to use SQL Server Profiler.

-- TAD

[JensDalsgaard]

Well - I did take a look at the use of indexes wondering whether spatial indexes were used optimally. Apparently though, as data is versioned, SQL Server insists on using indexes optimizing the deduction of data from base tables, add tables and delete tables. I tried forcing it to use the spatial indexes (WITH(INDEX(S64_idx))) but was told that other hints were preferred.

Omitting the Order By part did not improve performance, though that was actually a plausible assumption.

I then tried leaving out the string conversion (str(eft.Year)) completely - also this had no effect on performance.

So - the question likely is: how can I force the query to make use of the spatial indexes, when SQL Server seems to prefer using the indexes used in the versioned views optimizing aggregating information from the base tables and the corresponding add and delete tables?

I'll try looking at the query that ArcMap generates using the Profiler tool. I am though kind of worries that this simply will reveal extremely complex queries that I am unable to convince (or even teach) any business intelligence department.

[MartinAmeskamp · Accepted solution]

Hi Jens,

we tried to do the same thing on Oracle - using st_intersects on versioned views to create some very similar reports to yours. We found that our queries took a couple of hours to execute and opened a tech support case. After some discussions, the answer basically was: If you want to efficiently use geometry operators on versioned feature classes, you can't use MV views, but you rather have to write your own versioned queries and manually apply the geometry operation to the A-tables. Can be done, has been done, but it's not a way we want to go.

As a workaround, tech support suggested to first create materialized views from the multiversioned views and then use the geometry operations on those. We took a somewhat simpler path and created temporary tables/fcls (create table t as select * from mv_view) and then usd the geometry operators on those. A lot faster!

If you need it, I can dig out the incident number for this call.

Good luck, Martin

[JensDalsgaard]

That's exactly what I thought would be the conclusion, Martin. Thanks for answering my post.

I'd like a link to the incident.

Also - when creating a materialized view (or in SQL Server - a table since materialized views are not support in SQL Server), how do you generate and calculate the spatial indexes. I could look it up myself, but if you have the information by hand and would like to share it, it'd a little easier for me

[MartinAmeskamp]

Jens,

The incident number is 1202365 (we handled this as a PSS call, it still took a couple of months to process, and I finally talked this over with Tom Brown in Palm Springs last year). Also, we didn’t use st_intersect but rather st_within, but the general problem remains: Oracle (at least) isn’t able to push the geometry operators for enough down, so we’re basically doing Cartesian products here.

I have to check how we created the spatial index – at a guess I would say SDE command line tools (this was 10.0) to register the table and create the index, but I’d have to check.

Martin

Von: Jens Dalsgaard

Gesendet: Donnerstag, 26. März 2015 12:12

An: Ameskamp, Dr. Martin

Betreff: Re: - Optimizing STIntersects queries

GeoNet <http://jiveon.jivesoftware.com/wf/click?upn=Dg1s4x8le7Lmxv8KWGaqo8h7SGfRSMkw-2FpvHGF9-2FW3rK-2Bvs1kL9-2FnG6jjf2NZhrLDz0M-2BrY-2By9IaziQEKVk3Hg-3D-3D_GjRFCNGdMNqdt7rSVIqdH0qHqDDgIeyIXpkS4jn6U-2Fr3fh9mo7Hp47Cmfn-2FKfkYVPSbbPhU0zRTPicqVCMlD7FbwMUYhqnW58oFK-2FGfuLhjSWB4mfL0sBiGx7HYT47eWq8mZfgAZuZP3-2BXaicr5JedovF0BI5zamI98mf2jaELKHtg-2BeHEGXvGQ-2F3SHU1ivAyKMKnkqruWe9JFrBMVlODzXCztGjTUSeyDHdBq411mDflUhfYgBMXyxdEQseVC-2Fkai3r6pofXd-2Bwh7RfVZm-2FdSuj33Oq8SAI3EG4Ap6RBgcfV3gynk4iEqtKmkIaZInMfgAAIjvmRzepB-2FWLriy6Pg-3D-3D>

Optimizing STIntersects queries

reply from Jens Dalsgaard<http://jiveon.jivesoftware.com/wf/click?upn=Dg1s4x8le7Lmxv8KWGaqo7A4BXwO9PY1WvQ5cXCtK4-2BSRSZ76VBeXE7uzYUPV67Ik1bjaVUiec4mZMqiIZA-2F25kkQEmSP3BTAo5zzmswJlP235Z0mVMDLF-2Fa0gNgtUkl_GjRFCNGdMNqdt7rSVIqdH0qHqDDgIeyIXpkS4jn6U-2Fr3fh9mo7Hp47Cmfn-2FKfkYVPSbbPhU0zRTPicqVCMlD7FbwMUYhqnW58oFK-2FGfuLhjSWB4mfL0sBiGx7HYT47eWq8mZfgAZuZP3-2BXaicr5JeWoJbnR4C3I3jHGnaH3D2Kj98i9wUlfDOAynWe2UI-2BHAN-2Buio5oBaFRuQefaWgwDGm5vPzISpjU2VbDT4oZtPdlanszaqQ4uvoffc2BjMSCBBd0BkjRzOfOZJ8KuVFP-2Bc5X5gjk9wHgyG-2FEVzCYhW9eynkiGY6eM-2BpRGbjRzUkXqPi8RSazhksyXthz5bQxDKg-3D-3D> in Developers - View the full discussion<http://jiveon.jivesoftware.com/wf/click?upn=Dg1s4x8le7Lmxv8KWGaqo0VQjeJBYpG9HGC8QZBT-2FEzFuAN4zRGmsPhJgokMP-2FGoMTbkFv3OoqtkXlXEWg5Rmv3ERQ1OeE-2FADIPzUj2kjHg-3D_GjRFCNGdMNqdt7rSVIqdH0qHqDDgIeyIXpkS4jn6U-2Fr3fh9mo7Hp47Cmfn-2FKfkYVPSbbPhU0zRTPicqVCMlD7FbwMUYhqnW58oFK-2FGfuLhjSWB4mfL0sBiGx7HYT47eWq8mZfgAZuZP3-2BXaicr5JeQlXx-2FyRuuQiFCqBNq4x7lSLFacAXE1K2A0Dwpw-2F-2BADbH8q-2%20BbikgATY2aVbSdhDtLw-2FyPs-2BokVoYacJAqVVoVstC6NaSG-2B1dOiTTIebu8qNlefbutaZINIoRKjTJJ08o3bPIfEwTONwIw75s3B1ijtyw-2FI-2B5Q30wKtEQ-2F5FJlSC-2FXYtVnSgeESSSU7K1P9YycQ-3D-3D>