It's more likely that the ST_Intersects() is your performance bottleneck, especially if you have not constructed a spatial index. Personally, I'd do this query as a single statement using a virtual table, and with an ST_Within:
UPDATE parcel_new_pin u
SET zoning = vt.zoning
FROM (
SELECT p.pin,
p.zoning
FROM parcel_new_pin p
JOIN ZONING z2 ON sde.ST_Within(sde.ST_PointOnSurface(p.shape,z2.shape)) = 1
WHERE p.zoning <> z2.zoning_district
AND z2.zoning_district <> 'HB-S MLKO'
) vt
WHERE u.pin = vt.pin;
For this query, I'd make sure that ZONING has an index on the geometry column, and PARCEL_NEW_PIN has an index on PIN.
Additionally, you can test the raw performance of the ST_PointOnSurface query in isolation:
SELECT sde.ST_PointOnSurface((p.shape))
FROM parcel_new_pin p
And the performance of the JOIN without UPDATE:
SELECT p.pin,
p.zoning
FROM parcel_new_pin p
JOIN ZONING z2 ON sde.ST_Within(sde.ST_PointOnSurface(p.shape,z2.shape)) = 1
WHERE p.zoning <> z2.zoning_district
AND z2.zoning_district <> 'HB-S MLKO'
If you haven't built a spatial index on the polygons of ZONING, that should be your first priority, though it's probably easiest to use the ArcPy utility Add Spatial Index (Data Management) via an enterprise geodatabase connection (.sde) file, at least at first, since the parameters can be tricky.
- V