SQL Server 2008 Geometry.STBuffer(…) really slow

I’ve got a basic SQL query that looks like:

SELECT TOP 1
[geom].STBuffer(500)
FROM [db].[dbo].[boundaries]

Which essential takes a map boundary from the database and buffers it by 500m. The problem I’m having is that it’s incredibly slow and then the server runs out of memory! I’开发者_如何学运维m sure something must be wrong as a simple operation like this in a GIS program takes seconds to run, whereas this runs for around a minute before giving up.

The boundary is fairly complicated, but it shouldn’t be so complicated as to cause the server to run out of memory, I’m sure of that.

If I reduce the buffer distance to say 100m, it runs and completes within around 14 seconds, which is still too slow to be useful in realtime.

Any idea as to why it might be so slow, and any tips as to how I can speed it up?

Thanks,

---

This is a known limitation with STBuffer in Sql Server 2008 - it is prone to being slow and potentially running out of memory when the distance parameter is larger than the diameter of the object and the object has more than 1000 points. There is a connect item for this issue and it is fixed in Sql Server Denali.

As a workaround, you can try running Reduce on the object before invoking buffer to lower its complexity, and using BufferWithTolerance method to pass in a higher tolerance which will result in a less complex result.