[GIS] SQL Server 2008 Distance and Performance Problem

querysql serversql-server-spatial

I had been working on PostGIS to do some geographic processes and due to some reasons, I had to start to use SQL Server 2008.
I ported same code for SQL Server but performance is almost dying when I sort according to distances (ORDER BY dist). I dont know if i am missing something on SQL server because i am new on it. 

DECLARE @myPoint geography;
SET @myPoint = geography::STGeomFromText('POINT(28.8801844444445 41.0299561111109)',4326);
SELECT TOP 8 id,
@myPoint.STDistance(geom.ToString()) as dist
FROM MY_DB
ORDER BY dist

This query takes almost 4 seconds on SQL Server while it is taking 120 ms on postgis.

Best Answer

Tested the ToString() as follows 

SET STATISTICS TIME ON

DECLARE @myPoint1 geography, @myPoint2 geography;
SET @myPoint1 = geography::STGeomFromText('POINT(28.8801844444445 41.0299561111109)',4326);
SET @myPoint2 = geography::STGeomFromText('POINT(29.8801844444445 40.0299561111109)',4326);

SELECT @myPoint1.STDistance(@myPoint2.ToString()) as dist 

SELECT @myPoint1.STDistance(@myPoint2) as dist

and 0 ms either way. Is the SQL Server table spatially indexed? If so, can you provide the index definition? If not, try adding an index. Extents must cover your data, for points I recommend something like

CREATE SPATIAL INDEX [name] ON [your table] 
(
    [geometry column]
)USING  GEOMETRY_GRID 
WITH (
     BOUNDING_BOX =(-180, -90, 180, 90)
     , GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH)
     , CELLS_PER_OBJECT = 1, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF
     , ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
     ) ON [PRIMARY]

Use a smaller bounding box if your data is not global. Non-point data I generally use MEDIUM grids and more CELLS_PER_OBJECT.

Related Question