Spatial Join with st_geometry

[forestknutsen1]

I would like to do a spatial join between points and polygons with st_geometry this is what I have come up with:

select sp.OBJECTID, p.parcel_num from servicepoint_evw sp
join parcels p
on (sde.st_intersects(sp.shape, p.shape)) = 1

This does appear to work but it is supper slow... I took over a minute to return one row.

[DavidPike]

How big is the dataset?  One thing to check is that you have a spatial index, it is much faster if so.  Maybe the 'disjoint' test is faster.

When was the last time the database was compressed (I'm guessing versioned as you have _evw)?  Is it going to state 1 or 0?  Otherwise the versioned view query is quite expensive to work through - checking on the delta tables and the base table differences.

[forestknutsen1]

1. yes, it has a spatial index
2. yes, we are versioned and no we never get back a low state count and we do have a large number of delta rows. So this could be the issue. (and yes we know this is a problem... I will spare you the details)
3. I will give disjoint a try

Thanks for the feedback

[DavidPike]

Might also be worth testing against the base table rather than evw to confirm this as the likely cause of the inefficiency (appreciate record count would differ however).

[forestknutsen1]

That is a great idea. I just did it against the base table and it is only 10% faster. So, the versioning is playing a role. But it is not the main issue.