尝试查找地理编码范围内的所有点时，空间索引很慢.我该如何加快速度? [英] Spatial index is slow when trying to find all the points within a range of a geocode. How do I make this faster?

问题描述

我在一个表上设置了一个空间索引，该表中有130万条记录都是经过地理编码的.这些值存储在地理数据类型列中.我遇到的问题是，当我查询具有空间索引的此列时，它的速度确实仍然很慢.例如，在一英里内查找所有帐户大约需要20秒.

I've setup a spatial index on a table that has 1.3 million records in it that are all geocoded. These values are stored in a geography data type column. The problem I'm having is that when I query this column that has a spatial index is is really slow still. It's taking about 20 seconds to find all of the accounts within a mile for instance.

以下是运行缓慢的查询的示例:

Here is an example of a query that runs slow:

DECLARE @g Geography;
SET @g = (select ci.Geocode from CustomerInformation ci where ci.CIOI = 372658) 

DECLARE @region geography = @g.STBuffer(1609.344)

Select top 100 ci.Geocode.STDistance(@g), ci.CIOI 
from CustomerInformation ci
where ci.Geocode.Filter(@region) = 1
order by ci.Geocode.STDistance(@g) asc

这是我的创建索引语句:

Here is my create index statement:

CREATE SPATIAL INDEX [IX_CI_Geocode] ON [dbo].[CustomerInformation] 
(
    [Geocode]
)USING  GEOGRAPHY_GRID 
WITH (
 GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = LOW,LEVEL_3 = LOW,LEVEL_4 = LOW), 
CELLS_PER_OBJECT = 128, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

数据是处于单个状态的一部分中的每个房屋.因此，我希望在一英里半径内会达到1000点或更多.我索引正确吗?任何帮助都会很棒.

The data is every house in a portion of a single state. So in a mile radius I expect there to be a 1000 points or more. Am I indexing this properly? Any help would be great.

另一个慢速查询示例:

DECLARE @g Geography;
SET @g = (select ci.Geocode from CustomerInformation ci where ci.CIOI = 372658) 

select top(100) CIOI, (ciFinding.Geocode.STDistance(@g) / 1609.344) as Distance, ciFinding.Geocode.ToString() --ciFinding.Geocode.STDistance(@g) / 1609.344
from CustomerInformation ciFinding
where ciFinding.Geocode.STDistance(@g) is not null and ciFinding.Geocode.STDistance(@g) < 1609.344
order by ciFinding.Geocode.STDistance(@g)

推荐答案

您可能需要使用索引提示(即WITH(INDEX([INDEX_NAME]))，但我认为2008 R2可能已经解决了此问题.

You might need to use an Index hint (i.e. WITH(INDEX( [INDEX_NAME] )) I think 2008 R2 might have resolved this though.

Select top 100 
ci.Geocode.STDistance(@g), ci.CIOI  
from CustomerInformation WITH(INDEX(IX_CI_Geocode))
ci where ci.Geocode.Filter(@region) = 1 
order by ci.Geocode.STDistance(@g) asc 

这篇关于尝试查找地理编码范围内的所有点时，空间索引很慢.我该如何加快速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！