Cách tăng tốc SQL Server 2014 các hàm Spatial

Question

Tôi có một số bảng chuẩn hóa với khoảng 1,2 triệu bản ghi mà tôi thu thập trong một dạng xem trong cơ sở dữ liệu SQL Server 2014.

Lưu ý rằng tôi đã đơn giản hóa tên của các bảng và cột.

Để minh họa, tôi đã tạo truy vấn này trên các bảng nguồn:

SELECT geom, dateFrom, dateTo 
FROM requestsTable 
INNER JOIN geomTable ON requestsTable.requestId = geomTable.requestId 
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

Yêu cầu này trả về 54 hàng trong vòng 1 giây.

Vấn đề là ngay khi tôi mở rộng truy vấn này với các hàm không gian, truy vấn sẽ chậm đi.

Ví dụ 1: Đã thêm bộ lọc không gian:

SELECT geom, dateFrom, dateTo 
FROM requestsTable 
INNER JOIN geomTable ON requestsTable.requestId  = geomTable.requestId 
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')
AND geom.Filter(geometry::STGeomFromText('POLYGON ((-791308.53524129 5350657.6892069, -791308.53524129 6762958.489206901, 2357696.2647587 6762958.489206901, 2357696.2647587 5350657.6892069, -791308.53524129 5350657.6892069))', 25832)) = 1

Yêu cầu này trả về 54 hàng giống nhau, nhưng bây giờ mất 23 giây.

Kế hoạch thực hiện hiển thị 67% ở tìm kiếm chỉ mục được phân nhóm trên PK của bảng hình học.

https://www.brentozar.com/pastetheplan/?id=S1l1ivsBv

Ví dụ 2: Ngay cả khi chỉ thêm SRID cũng mất thêm rất nhiều thời gian:

SELECT geom, dateFrom, dateTo, geom.STSrid 
FROM requestsTable 
INNER JOIN geomTable ON requestsTable.requestId  = geomTable.requestId 
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

Yêu cầu này trả về 54 hàng giống nhau, nhưng bây giờ trong 12 giây.

https://www.brentozar.com/pastetheplan/?id=BJS8swsSw

Hình học trong geomTable có chỉ mục không gian:

CREATE SPATIAL INDEX [spatial_idx] 
ON [geomTable] ([geom]) 
USING GEOMETRY_GRID 
WITH (BOUNDING_BOX =(440000, 6040000, 900000, 6410000), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

Câu hỏi đặt ra là: làm cách nào để tăng tốc các chức năng không gian?

CẬP NHẬT

Tôi đã đơn giản hóa câu hỏi một chút để dễ hiểu hơn. Các bảng thực có rất nhiều chỉ số có thể làm rối tung trình tối ưu hóa truy vấn. Đây là bảng và định nghĩa chỉ mục:

https://pastebin.com/8GkSkN2h

Và truy vấn:

SELECT the_geom, graveperiode_fra, graveperiode_til FROM graveforespoergsel
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id = graveforespoergsel.graveforespoergsel_id 
WHERE 
 ("graveperiode_til" >= '2020-01-10 00:00:00' AND "graveperiode_fra" <= '2020-11-05 00:00:00') 
 -- Adding the filter below adds a lot of time to the query
 AND "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527.96559412003 5928957.9586343, 434527.96559412003 6282033.158634299, 1214815.9655941 6282033.158634299, 1214815.9655941 5928957.9586343, 434527.96559412003 5928957.9586343))', 25832)) = 1

Answer 1

1. Có vẻ như bạn cần thêm cột date_to để lập chỉ mục trên cột date_from (tạo chỉ mục nhiều cột vào ngày tháng)
2. chỉ mục hình học của bạn không được sử dụng trong cả hai kế hoạch thực thi (có thể vì các ranh giới trong bộ lọc nằm ngoài ranh giới trong chỉ mục). Nhưng có vẻ như với chỉ mục đa cột chính xác, bạn không cần chỉ mục không gian này

Chúng ta hãy cố gắng này:

SELECT graveforespoergsel_id, graveperiode_fra, graveperiode_til 
INTO #t
FROM graveforespoergsel
WHERE "graveperiode_til" >= '2019-01-10 00:00:00' AND "graveperiode_fra" <= '2022-10-10 00:00:00'

SELECT "the_geom", graveperiode_fra, graveperiode_til 
FROM #t
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id =  #t.graveforespoergsel_id 
WHERE "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527 5928957, 434527 6282033, 1214815 6282033, 1214815 5928957, 434527 5928957))', 25832)) = 1

DROP TABLE #t