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.