Início / dba / Perguntas / 299984
Accepted
Jozef Podlecki
Asked: 2021-09-23 08:11:26 +0800 CST

É o cenário certo para usar o índice espacial?

  • 772

Eu usei o índice espacial recentemente em uma tabela para melhorar a consulta que filtra os registros por determinado intervalo de datas, identificador de referência e se eles estão dentro do polígono.

Aqui está o exemplo da consulta

DECLARE
    @start DATETIME = '2021-09-01 00:00:00.000',
    @end DATETIME = '2021-09-22 00:00:00.000',
    @shape GEOGRAPHY
    
SET @shape = geography::STPolyFromText('POLYGON((
-5.0537109 55.5783447,
-3.9990234 51.0137547,
3.2299805 50.4295179,
4.0209961 54.8133484,
-5.0537109 55.5783447
))', 4326);

SELECT
    *
FROM [dbo].[getUsersByArea](@start, @end, @shape);

eu tenho notado isso

  • Às vezes, o índice espacial nem é usado.
  • Se aparecer no plano de consulta, o desempenho ainda é ruim.

A user_locationtabela tem cerca de 70 milhões de registros.

A referencetabela tem cerca de 100k registros e geralmente há alguns milhares user_locationde registros referenciando uma tabela.

A usertabela tem 100k registros.

Eu também brinquei com os métodos de geografia OGC e tentei usar STIntersects, STWithin( STContainssurpreendentemente se comporta da mesma forma que STWithin) e Filter, mas não conseguiu aliviar nenhum problema mencionado ( STIntersectsde alguma forma, parecia pior nos planos de consulta).

É a abordagem correta para usar esse tipo de índice? É um quebra cabeça.

Me diga o que você acha.

Desde já, obrigado.

Aqui estão as definições de esquema

CREATE TABLE [dbo].[user_location] (
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ReferenceId] [uniqueidentifier] NOT NULL,
    [RecordedOn] [datetime] NOT NULL,
    [Lat] [float] NULL,
    [Lng] [float] NULL,
    [Location] AS (case when [Lat] IS NULL OR [Lng] IS NULL then NULL else [geography]::Point([Lat],[Lng],(4326)) end) PERSISTED,
    CONSTRAINT [PK_dbo_user_location] PRIMARY KEY CLUSTERED([Id] ASC)
)

CREATE TABLE [dbo].[reference]
(
    [Id] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_dbo_reference] PRIMARY KEY CLUSTERED([Id] ASC)
)

CREATE TABLE [dbo].[user]
(
    [Id] [uniqueidentifier] NOT NULL,
    [FullName] [nvarchar](100) NULL,
    [EmailAddress] [nvarchar](255) NULL,
    CONSTRAINT [PK_dbo_user] PRIMARY KEY CLUSTERED([Id] ASC)
) 

CREATE NONCLUSTERED INDEX [IX_reference_userId] ON [dbo].[reference]
(
    [UserId] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

CREATE NONCLUSTERED INDEX [IX_user_location_referenceId_recordedOn] ON [dbo].[user_location]
(
    [ReferenceId] ASC,
    [RecordedOn] ASC,
) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

CREATE SPATIAL INDEX [SPIX_user_location] ON [dbo].[user_location]
(
    [Location]
)USING  GEOGRAPHY_AUTO_GRID 
WITH (
CELLS_PER_OBJECT = 12, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

CREATE FUNCTION [dbo].[getUsersByArea]
(
    @start DATETIME,
    @end DATETIME,
    @shape GEOGRAPHY
)
RETURNS TABLE
AS
RETURN
(
    SELECT DISTINCT
        us.Id,
        us.FullName,
        us.EmailAddress
    FROM [dbo].[user_location] ul
    JOIN [dbo].[reference] rf
        ON ul.ReferenceId = rf.Id
    JOIN [dbo].[user] us
        ON us.Id = rf.UserId
    WHERE 
        ul.RecordedOn < @end
        AND ul.RecordedOn >= @start 
        AND ul.[Location] IS NOT NULL 
        AND @shape.STContains(ul.[Location]) = 1
)

Também uma coisa interessante, quando você tenta recuperar metadados de índice espacial com sys.sp_help_spatial_geography_indexele executa infinitamente.

EXEC sys.sp_help_spatial_geography_index
    @tabname = 'user_location',
    @indexname = 'SPIX_user_location',
    @verboseoutput = 1,
    @query_sample = 'POINT (0 0)'
sql-server index

1 respostas

  1. Best Answer

    Acho que você deve tentar armazenar a variável @shape em outra tabela base (tabela temporária?) e adicionar um índice espacial a isso também

    Exemplo

    CREATE PROCEDURE [dbo].[getUsersByArea]
    @start DATETIME,
    @end DATETIME,
    @shape GEOGRAPHY
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Shapes
    (
        [Id] [uniqueidentifier] NOT NULL,
        Shape GEOGRAPHY NOT NULL,
        CONSTRAINT [PK_Shapes] PRIMARY KEY CLUSTERED([Id] ASC)
    )

    INSERT INTO #Shapes
    VALUES
    (NEWID(), @shape)

    CREATE SPATIAL INDEX [SPIX_TMP_Shape] ON #Shapes ([Shape])
    -- YMMV
    USING GEOGRAPHY_GRID 
    WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4= 
    HIGH),
    CELLS_PER_OBJECT = 16)

    SELECT DISTINCT
        us.Id,
        us.FullName,
        us.EmailAddress
    FROM [dbo].[user_location] ul
    JOIN [dbo].[reference] rf
        ON ul.ReferenceId = rf.Id
    JOIN [dbo].[user] us
        ON us.Id = rf.UserId
    JOIN #Shapes sh
        ON sh.Shape.STContains(ul.[Location]) = 1
    WHERE ul.[Location] IS NOT NULL
        ul.RecordedOn < @end
        AND ul.RecordedOn >= @start
)
    • 2

relate perguntas