6

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.

  • Which verison/build Can you do a select @@version; and put the result in your question? – Jay Cummins Jun 20 '13 at 14:48
  • Quick and easy test: try putting a OPTION (MAXDOP 1) at the end of your query to see if you are being affected by the MAXDOP issue (gis.stackexchange.com/q/63357/31). – Jay Cummins Jun 20 '13 at 14:52
  • is it the exactly same data set? – Nicklas Avén Jun 20 '13 at 15:04
  • If it not is PostGIS beta (2.1) that you have been using it is just raw brutal force calculation in PostGIS which SQL Server ought to be on pair with. I do not know the SQL Server syntax either, but it seems like you cast the geometry to string in the query, why? That can be very expensive if then the distance function auto casts back to geography. – Nicklas Avén Jun 20 '13 at 15:07
  • Yes, pass geom to STDistance, not geom.ToString(). Unless geom is type geometry instead of geography in which case you should change the type in MY_DB. – Russell at ISC Jun 20 '13 at 16:18
1

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.

|improve this answer|||||
  • Thanks for answer but i have created spatial index and bounding box (exactly fit to my data) before asking here. It works fine when i compare only 2 different points but it suffers when i try to find the closest 8 point on table (arround 20 000 row) – None Jun 21 '13 at 7:16
  • Have you tried running the query in SMSS with "show actual execution plan" turned on to see if it's actually using the index? If it isn't, try a "with(index(...))" hint – Russell at ISC Jun 24 '13 at 13:37
  • Sorry for late response. I had left project away for a while and now need to work on it again. I have checked my query by "Execution Plan" and saw that it is not used. Then, i used "with(index())" and my index name in same query. This affected "Execution Plan" grafics but not query time. I need to thank you for this however it doesnt work like i want. I decided to add a "WHERE geom.STDistance(@myPoint) < 100" condition till i find a better solution. Query takes beetween 0.2-0.3 seconds now. – None Jul 10 '13 at 14:42

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.