I'm trying to use SQL Server Spatial to work with data related to geographic regions defined by a third party, and am having trouble getting acceptable performance.
The following query takes quite a long time to run, and doesn't use any parallel operators:
UPDATE tblRegions SET RG_Geography = RegGeo FROM tblRegions CROSS APPLY ( SELECT geography::UnionAggregate(RGD_Geography) AS RegGeo FROM tblRegionDetail WHERE RGD_RG_ID = RG_ID ) agg
If I convert that update to a select, it takes around 25 seconds to get a single region, and pegs one CPU core while doing so. If I take out the aggregate, the select finishes almost instantly. That makes it pretty clear that the aggregate itself is the problem.
Is there anything I can do to hint to the optimizer that the aggregate will have a high CPU cost? In addition to that, is there anything I can do to get this update to run faster? Adding parallel query hints has helped some, but hasn't gotten performance to anywhere near acceptable.