我们有一个包含应用程序中对象的所有多边形(几何/地理)的表格。这是因为大多数多边形需要具有同一多边形的“几何”( RDNew ) 和“地理”( WGS84 ) 版本并且需要重复使用(大多数多边形用于多个对象)。
为了确保将正确的引用添加到对象(基于需要与该对象关联的多边形),我们首先使用查询在多边形表中查找多边形:
declare @__geometry_0 sys.geometry -- filled by a polygon value
SELECT TOP(1) [g].[Id], [g].[AangemaaktOp], [g].[OorspronkelijkeCoordinaatSysteem], [g].[RDNewGeo], [g].[RDNewOppervlakte], [g].[WGS84Geo], [g].[WGS84Oppervlakte]
FROM [GeoData] AS [g]
WHERE [g].[RDNewGeo].STEquals(@__geometry_0) = CAST(1 AS bit)
ORDER BY [g].[Id]
我们将 MS SQL Server 作为 Azure“服务”运行。我们遇到的问题是这些搜索操作每次大约需要 2 秒(此时表中有 250.000 多条多边形记录)。
所以我们想减少定位一个(一个)特定多边形所需的时间。
起初,我们注意到没有空间索引。所以我们添加了一个,看看它能加快搜索速度。
CREATE SPATIAL INDEX IX_SPATIAL_GeoData_RDNewGeo ON dbo.GeoData(RDNewGeo)
WITH( BOUNDING_BOX = ( xmin = 0.0, ymin = 300000.0, xmax = 280000.0, ymax = 625000.0) )
但是,运行上面的查询时,仍然没有使用索引(查看实际执行计划时)。
所以其次,我们修改了查询 NOT 使用语句top (1)
,现在使用空间索引(根据执行计划)。但是,现在搜索更慢了!
编辑:重新测试后,我们不再注意到速度下降,相反,(本地)性能提升很小 => 请参阅此问题的答案以获取完整解释。
关于如何加快执行搜索一个特定多边形的查询的任何想法(或者我们只是走错了路)?
附加信息:
SQL 服务器:Microsoft SQL Azure (RTM) - 12.0.2000.8
建表语句
CREATE TABLE [dbo].[GeoData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AangemaaktOp] [datetime2](7) NOT NULL,
[OorspronkelijkeCoordinaatSysteem] [smallint] NOT NULL,
[RDNewGeo] [geometry] NOT NULL,
[RDNewOppervlakte] [float] NOT NULL,
[WGS84Geo] [geography] NOT NULL,
[WGS84Oppervlakte] [float] NULL,
CONSTRAINT [PK_GeoData] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
示例多边形
POLYGON ((128864.429 449604.427, 128863.868 449605.748, 128863.48 449605.51, 128862.98 449605.33, 128862.47 449605.24, 128861.94 449605.25, 128861.43 449605.37, 128861.16 449605.49, 128860.11 449605.46, 128858.84 449605.43, 128856.44 449604.98, 128777.65 449582.27, 128692.42 449557.94, 128628.68 449540.22, 128547.49 449517.35, 128487.491 449500, 128443.71 449487.34, 128443.43 449487.36, 128443.22 449487.42, 128443.03 449487.51, 128442.85 449487.65, 128442.71 449487.81, 128442.6 449488, 128442.598 449488.006, 128433.38 449485.18, 128433.41 449484.9, 128433.39 449484.62, 128433.32 449484.35, 128433.19 449484.1, 128433.02 449483.87, 128432.81 449483.68, 128432.58 449483.54, 128427.179 449481.36, 128425.795 449480.801, 128427.467 449475.951, 128427.11 449473.74, 128427.51 449473.46, 128427.84 449473.11, 128428.12 449472.7, 128438.58 449441.28, 128438.98 449440.73, 128439.48 449440.25, 128440.07 449439.88, 128440.71 449439.62, 128441.4 449439.48, 128442.09 449439.48, 128442.79 449439.61, 128473.49 449448.58, 128474.623 449448.963, 128474.775 449448.997, 128474.931 449449.016, 128475.087 449449.018, 128475.243 449449.005, 128475.396 449448.975, 128475.546 449448.93, 128475.69 449448.87, 128475.828 449448.795, 128475.957 449448.707, 128476.076 449448.606, 128476.185 449448.494, 128476.281 449448.371, 128476.365 449448.239, 128476.434 449448.099, 128476.489 449447.952, 128476.507 449447.884, 128482.689 449449.572, 128482.612 449450.178, 128482.773 449450.367, 128483.043 449450.519, 128483.6 449450.72, 128483.72 449450.76, 128538 449466.25, 128587.57 449480.08, 128634.44 449494.08, 128655.143 449500, 128670.6 449504.42, 128708.1 449515.01, 128748.61 449526.58, 128778.5 449535.25, 128831.15 449550.08, 128866.91 449560.22, 128867.21 449560.24, 128867.51 449560.2, 128867.79 449560.09, 128868.05 449559.94, 128868.27 449559.74, 128868.45 449559.5, 128868.58 449559.229, 128875.66 449561.255, 128875.65 449561.67, 128875.72 449562.08, 128875.87 449562.47, 128876.03 449562.73, 128876.35 449563.92, 128876.42 449565.18, 128876.22 449566.43, 128865.22 449597.56, 128865.18 449597.95, 128865.21 449598.34, 128865.32 449598.71, 128865.5 449599.05, 128865.74 449599.35, 128866.03 449599.6, 128866.37 449599.79, 128866.395 449599.797, 128865.961 449600.819, 128864.429 449604.427))