Jozef Podlecki's questions

Há uma função com valor de tabela no SQL Profiler que notei que parece "chamar-se várias vezes" .

A definição também não faz nada estranho, é uma consulta com poucos JOINse WHEREcláusula com o método hierarchyidIsDescendantOf

O que esta acontecendo aqui? É apenas alarme falso?

Quais são seus pensamentos?

desde já, obrigado

SQL:BatchStarting

exec sp_executesql N'SELECT * FROM [dbo].[GetPermissionsForUser](@p0)
',N'@p0 uniqueidentifier',@p0='7A2137C1-A7D4-415C-A5B5-F4C2351217C9'

SP: Iniciando e SP: Concluído

SELECT * FROM [dbo].[GetPermissionsForUser](@p0)

Esquema

CREATE TABLE [dbo].[permission_matrix] (
    [Id] [uniqueidentifier] NOT NULL,
    [TargetId] [uniqueidentifier] NULL,
    [Permission] [nvarchar](35) NOT NULL,
    [GroupId] [uniqueidentifier] NULL,
    [Options] [int] NOT NULL,
 CONSTRAINT [PK_permission] PRIMARY KEY CLUSTERED ([Id] ASC)

CREATE TABLE [dbo].[hierarchy](
    [NodeId] [int] IDENTITY(1,1) NOT NULL,
    [EntityId] [uniqueidentifier] NOT NULL,
    [EntityType] [smallint] NOT NULL,
    [ParentEntityId] [uniqueidentifier] NULL,
    [NodePath] [hierarchyid] NOT NULL,
 CONSTRAINT [PK_hierarchy] PRIMARY KEY NONCLUSTERED ([NodeId] ASC)

CREATE FUNCTION [dbo].[GetPermissionsForUser]
(   
    @userId UNIQUEIDENTIFIER
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        op.Id,
        op.TargetId,
        op.Permission,
        op.GroupId,
        op.Options
    FROM [dbo].[permission_matrix] op
    WHERE EXISTS(
    SELECT
        1
    FROM [dbo].[hierarchy] gh
    JOIN [dbo].[hierarchy] uh
        ON uh.EntityId = @userId
        AND uh.NodePath.IsDescendantOf(gh.NodePath) = 1 
    WHERE gh.EntityId = op.GroupId)
)

Isto é o que mostra no SQL Profiler

-- Editar

A sessão de evento estendida não exibe isso

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