我们有一个包含应用程序中对象的所有多边形(几何/地理)的表格。这是因为大多数多边形需要具有同一多边形的“几何”( 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))
我不知道我们之前所做的测试有什么问题,但我们又重新运行了几次测试(没有
top(1)
和有“空间索引”)。这一次,我们没有再(本地)看到性能下降,甚至看到了轻微的性能提升!
因此,我们对代码进行了调整,以确保使用空间索引并将空间索引添加到我们的预生产环境(也托管在 Azure 上),并在性能方面看到了一些真正的改进。一些任务的总体执行增益为 60%,我们注意到数据库使用率 (DTU/CPU) 在查询期间显着下降。
结论:
添加空间索引并确保不使用任何
top x
语句,这将使 MS-SQL 不使用您的空间索引!