CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Sivakumar Mohanraj
Sivakumar Mohanraj

Reputation: 41

How to optimize the sql table containing the geography column

We have stored geography data in "Location" table and based on this column we are searching nearest location for the given input. Below query is used to get the nearest locations within 25 miles and this query takes more than 4 seconds to retrieve 4000 records. We have even created spatial index on location field.

DECLARE @Distance INT

SET @Distance =25

DECLARE @h sys.GEOGRAPHY

SET @h =CONVERT(sys.GEOGRAPHY, 0xE6100000010C92B06F27119D4140111AC1C6F53554C0)

SELECT CenterLocationId,
       [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) AS Distance
FROM   [dbo].[CenterLocation]
WHERE  [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) <= @Distance
       AND IsDeleted = 0
ORDER  BY [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53))

Can anyone suggest how to improve this query performance in sql server 2014?

Upvotes: 4

Views: 179

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239794

Two things, not guaranteed to fix the issue, but can certainly help. They're both related:

SELECT CenterLocationId,
       [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) AS Distance
FROM   [dbo].[CenterLocation]
WHERE  [Location].Stdistance(@h) <= @Distance / Cast(0.000621371 AS FLOAT(53))
       AND IsDeleted = 0
ORDER  BY [Location].Stdistance(@h)

That is, prefer to not perform maths in the WHERE clause on expressions that depend on column values - you're forcing the server to perform that maths for each row and destroying any potential for using an index.

Also, similarly, it's pointless to perform a multiplication in the ORDER BY clause, since multiplication (by a positive number) doesn't change ordering.

Upvotes: 2

Related Questions