1

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.

5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.