CODEWITHSUNDEEP
sql-serversql-server-2008optimizationquery-optimizationgeospatial
Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61469

SQL server Coordinate distance optimization

I am working with SQL server 2008 on query that looks for users within certain distance from central point that is moving - which means I am constantly hitting database (new results need to be added to the existing set and ones out of distance to be removed) and every millisecond saved is of value.

ATM we are using following query (Id's are used cause they are indexed ATM and good when trying for speed):

declare @lat int = 500,
@lon int = 700

  SELECT @lat, @lon, id, journeyid,  ((ACOS(SIN(@lat * PI() / 180) * SIN([Id] * PI() / 180) + 
         COS(@lat * PI() / 180) * COS([Id] * PI() / 180) * COS((@lon - [JourneyId]) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515) as dist
FROM [OnlineLegal_dev1].[dbo].[Customer] 
         group by [Id], [JourneyId], ((ACOS(SIN(@lat * PI() / 180) * SIN([Id] * PI() / 180) + 
         COS(@lat * PI() / 180) * COS([Id] * PI() / 180) * COS((@lon - [JourneyId]) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515)
HAVING ((ACOS(SIN(@lat * PI() / 180) * SIN([Id] * PI() / 180) + 
         COS(@lat * PI() / 180) * COS([Id] * PI() / 180) * COS((@lon - [JourneyId]) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515)<=10000

  ORDER BY ((ACOS(SIN(@lat * PI() / 180) * SIN([Id] * PI() / 180) + 
         COS(@lat * PI() / 180) * COS([Id] * PI() / 180) * COS((@lon - [JourneyId]) * 
         PI() / 180)) * 180 / PI()) * 60 * 1.1515) ASC

Current speed to select top 1k records is 00:00:00.097

How could I optimize this further for speed?

Upvotes: 0

Views: 72

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112602

You could store the pre-calculated values

SIN([Id] * PI() / 180)

and

COS([Id] * PI() / 180)

In the DB, for instance with an INSERT / UPDATE trigger each time an Id is inserted or updated.

CREATE TRIGGER dbo.tiuCustomer ON dbo.Customer
    FOR INSERT, UPDATE
AS BEGIN
   UPDATE dbo.Customer
   SET
       cos_id = COS(Inserted.Id * PI() / 180),
       sin_id = SIN(Inserted.Id * PI() / 180)
   FROM Inserted
   WHERE 
      dbo.Customer.CustomerID = Inserted.CustomerID -- Use the PK to link your table
                                                    -- with Inserted.
END

Upvotes: 1

Devart
Devart

Reputation: 122002

DECLARE @lat INT = 500,
        @lon INT = 700

DECLARE @lat_s FLOAT = SIN(@lat * PI() / 180),
        @lat_c FLOAT = COS(@lat * PI() / 180)

SELECT DISTINCT @lat, @lon, *
FROM (
    SELECT
        id,
        journeyid,
        ((ACOS(@lat_s * SIN([id] * PI() / 180) + @lat_c * COS([id] * PI() / 180) * COS((@lon - [JourneyId]) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS dist
    FROM dbo.Customer
) t
WHERE dist <= 10000
ORDER BY dist

Upvotes: 1

Related Questions