我正在使用SQL server 2008
进行查询,该查询查找距离正在移动的中心点一定距离内的用户,这意味着我不断地访问数据库(需要将新结果添加到现有集合中,并删除距离外的结果),保存的每一毫秒都是有价值的。
ATM我们使用以下查询(使用Id是因为它们是索引ATM,在尝试速度时很好):
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
选择前1k条记录的当前速度为00:00:00-097
如何进一步优化速度
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
您可以存储预先计算的值
SIN([Id] * PI() / 180)
和
COS([Id] * PI() / 180)
在DB中,例如,每次插入或更新Id
时都使用INSERT/UPDATE触发器。
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