SQL, How to improve the speed of STGeomFromText and STDistance in this query?

Asked Jul 27 '15 at 13:54

Active Jul 27 '15 at 13:54

Viewed 750 times

I am trying to get the geo distance using query but it seems to be very slow (taking about 10 seconds on a table of 15k records). Is there a way to improve this?

select count(contact_id) from matcher_contact
WHERE (GEOGRAPHY::STGeomFromText('POINT(-71.4536835 42.3240042)', 4326).STDistance(Geography) <= 25000)

And Here is the image of the execution plan. It seems that most of the time is spent on filtering in the where clause

Additional info:

matcher_Contact has about 15k records

Geography is one of the columns

Here are some examples of the values in Geography column:

0xE6100000010C5B4645F707B14440E0DAE44B036252C0 0xE6100000010C4E93BEB5B8854440885AE4E83B8552C0 0xE6100000010CB5368DEDB51045400AAB67F71A0554C0 0xE6100000010CA3A5E14A2CBE454008DA2E7EE49F51C0

asked Jul 27 '15 at 13:54

Vibol

1

[Spatial Indexes](https://technet.microsoft.com/en-us/library/bb933796(v=sql.105).aspx) are the obvious. – Damien_The_Unbeliever Jul 27 '15 at 13:59
@Damien_The_Unbeliever Thanks! That did the trick. I've never even heard of spatial index until now. – Vibol Jul 27 '15 at 14:49

SQL, How to improve the speed of STGeomFromText and STDistance in this query?

0 Answers0