Spatial Join with st_geometry

Question

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.

forestknutsen1 · Answer

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.

DavidPike · Answer

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 · Answer

* yes, it has a spatial index
* 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)
* I will give disjoint a try

Thanks for the feedback