超過 9900 萬條帶有地理列和空間索引的記錄 - 非常非常非常慢的查詢！

我已經使用 SQL Server 很長時間了，但直到最近才遇到使用空間數據的需要。我進入了一個大量使用它的環境，我的第一個真正挑戰是讓查詢執行得更快（並停止超時）對具有地理數據類型列（和索引）的表。

我們使用一個查詢來標識該表中在多邊形和多多邊形中找到的所有地理點。這張表中有超過 9900 萬條記錄，我完全不知道如何調整這個野獸的性能！我已經確定聚集索引比必要的大一點，並打算添加一個標識列來做兩件事：1）減小聚集索引的大小。2）消除插入的頁面拆分。雖然我希望從這樣做中得到一些緩解，但我並不樂觀地認為它會對空間查詢有很大幫助。

鑑於我幾乎完全缺乏空間數據的知識/經驗，我無法做得更好。

Example query:

   Declare @OrgID int
   Declare @Geog geography

   Set @OrgID =100011

   /* This will return a multi polygon */
   SELECT @Geog = geog 
   FROM Organization
   WHERE orgid= @orgid

   Select count(*)
   FROM ProblemChild WITH (INDEX(IDX_geog))  
   WHERE Geog.STIntersects(@geog) = 1) 

   Table Design:
   CREATE TABLE [dbo].[ProblemChild](
       [Phone] [char](10) NOT NULL,
       [Lat] [float] NOT NULL,
       [Lon] [float] NOT NULL,
       [Geog] [geography] NOT NULL,
       [Recordsource] [varchar](2) NOT NULL
    CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED 
   ([Phone] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO

Index design:

   CREATE SPATIAL INDEX [IDX_geog] ON [dbo].[[ProblemChild]]
   ([Geog]
   )USING  GEOGRAPHY_GRID 
   WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = MEDIUM), 
   CELLS_PER_OBJECT = 20, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   GO

With:

   Number of records:  99,155,267

   MinLat      MinLon      MaxLat      MaxLon
   18.957356   -166.512394 71.292528   -66.967883

查看估計的查詢計劃，並確保正在使用索引。

此外，MultiPolygon 的複雜性可能是一個重要因素。如果您將索引想像為 MultiPolygon 上的一系列網格，則會有一些網格要麼完全被 MultiPolygon 覆蓋，要麼完全不被 MultiPolygon 覆蓋。落入這些網格的 ProblemChild 點很容易。當網格僅被您的 MultiPolygon 部分覆蓋時，它將深入到下一個級別並嘗試相同的操作。

當沒有更多的網格級別可供鑽取時，複雜性就真正開始了。如果您有一條特別起皺的線，並且您需要確定特定點在該線的哪一側，則需要檢查很多線段並圍繞這些進行數學運算。如果您可以大大簡化 MultiPolygon，它將顯著加快您的查詢速度。您可以使用 .Reduce() 方法執行此操作，但是您需要注意正確性，以防您在邊界附近有點，如果簡化的話，這些點將位於線的另一側。

通過簡化，我的意思是減少它。想像一個八角形，有八個點。如果用六個點或一個四點正方形表示，那麼形狀會變得不同，並且角落附近的東西可能會隨著點數的減少而向內或向外移動。

編輯：我在http://sqlblog.com/blogs/rob_farley/archive/2015/04/29/tuning-slow-spatial-queries-in-sql-server.aspx寫過關於這個主題的部落格

嘗試先執行它，然後將其選擇到臨時表中：

INSERT INTO #TempProbChild
Select *
FROM ProblemChild WITH (INDEX(IDX_geog))  
WHERE Geog.Filter(@geog) = 1)

然後在該子集上執行您的 .STIntersects。

Select count(*)
FROM #TempProbChild
WHERE Geog.STIntersects(@geog) = 1)

我發現在非常大的數據集（數億行空間數據）上，您別無選擇，只能使用性能更高的空間方法，例如 .Filter。根據我的經驗，.STIntersects 在相當數量的記錄上通常會慢 7-15 倍。這應該可以幫助您使用更小、更易於管理的數據集將數據至少快速放入臨時表中，您可以在該數據集上應用您選擇的更準確但更佔用資源的空間方法。

引用自：https://dba.stackexchange.com/questions/96916