Início / dba / Perguntas / 325862
Accepted
R. Hoek
Asked: 2023-04-11 16:19:33 +0800 CST

Melhorando o desempenho do STEquals

  • 772

Temos uma tabela contendo todos os polígonos (geometria/geografia) dos objetos em nossa aplicação. Isso ocorre porque a maioria dos polígonos precisa ter uma versão 'geométrica' ( RDNew ) e uma 'geografia' ( WGS84 ) do mesmo polígono E precisa ser reutilizada (a maioria dos polígonos é usada em vários objetos).

Para garantir que a referência correta seja adicionada a um objeto (com base no polígono que precisa ser associado a esse objeto), primeiro buscamos o polígono na tabela de polígonos usando a consulta:

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]

Estamos executando o MS SQL Server como um 'serviço' do Azure. O problema que estamos enfrentando é que essas ações de pesquisa levam cerca de 2 segundos cada (neste ponto, há mais de 250.000 registros de polígonos na tabela).

Assim, queremos reduzir o tempo que leva para localizar um (um) polígono específico.

A princípio, notamos que não havia índice espacial. então adicionamos um para ver o quanto isso aceleraria a pesquisa.

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) )

No entanto, ao executar a consulta acima, o índice ainda não é usado (ao visualizar o plano de execução real).

Em segundo lugar, modificamos a consulta para NÃO usar a top (1)instrução e agora o índice espacial é usado (de acordo com o plano de execução). No entanto, agora a busca é ainda mais lenta! EDIT: após o novo teste, não notamos mais a desaceleração, mas, ao contrário, um pequeno ganho de desempenho (localmente) => veja a resposta a esta pergunta para obter a explicação completa.

Alguma ideia de como acelerar a execução de uma consulta que procura por um polígono específico (ou estamos apenas seguindo o caminho errado sobre isso)?

Informação adicional:

Servidor SQL: Microsoft SQL Azure (RTM) - 12.0.2000.8

Criar instrução de tabela

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]

Exemplo de polígono

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))
performance

1 respostas

  1. Best Answer

    Não sei o que havia de errado com os testes anteriores que fizemos, mas repetimos o teste várias vezes (sem top(1)e com o 'índice espacial').

    Desta vez, não vimos mais a degradação do desempenho (localmente) e até vimos um leve ganho de desempenho!

    Então, fizemos os ajustes em nosso código para garantir que o índice espacial fosse usado e adicionamos o índice espacial ao nosso ambiente de pré-produção (que também está hospedado no Azure) e vimos algumas melhorias reais em relação ao desempenho. Algumas tarefas tiveram um ganho geral de desempenho de 60% e notamos que o uso do banco de dados (DTU/CPU) caiu significativamente durante a consulta.

    Conclusão :

    Adicione um índice espacial e certifique-se de NÃO usar nenhuma top xinstrução, o que fará com que o MS-SQL NÃO use seu índice espacial!

    • 1

relate perguntas