Início / dba / Perguntas / 199945
Accepted
Andrew Williamson
Asked: 2018-03-12 17:20:30 +0800 CST

Como posso melhorar o desempenho de uma junção ordenada?

  • 772

Parece uma pergunta tão comum, eu entenderei se estiver fechado, mas se estiver, sugira um lugar melhor para eu perguntar. Tenho as seguintes duas tabelas de interesse:

CREATE TABLE [dbo].[Sessions]
(
    [Id] [int] PRIMARY KEY,
    [DateConnected] [datetime] NOT NULL,
    [Origin] [nvarchar](max) NULL,
    [TrackerId] [int] NULL,
    [Imei] [nvarchar](max) NULL,
    [Sim] [nvarchar](max) NULL,
    [ProtocolVersion] [tinyint] NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
    [Id] [int] PRIMARY KEY,
    [RequestId] [int] NULL,
    [SessionId] [int] NOT NULL,
    [DateProcessed] [datetime] NOT NULL,
    [Direction] [int] NOT NULL,
    [Sequence] [int] NOT NULL,
    [Acknowledgement] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Version] [tinyint] NOT NULL,
    [Command] [tinyint] NOT NULL,
    [Flags] [tinyint] NOT NULL,
    [Checksum] [tinyint] NOT NULL,
    [Data] [varbinary](max) NULL
)

CREATE NONCLUSTERED INDEX [IX_TrackerId_DateConnected] ON [dbo].[Sessions]
(
    [TrackerId] ASC,
    [DateConnected] ASC
)

CREATE NONCLUSTERED INDEX [IX_SessionId_DateProcessed] ON [dbo].[PacketTransmissions]
(
    [SessionId] ASC,
    [DateProcessed] ASC
)
INCLUDE ([Direction], [Sequence], [Acknowledgement], [Command])

Minha consulta mais comum e mais cara (muitas vezes expira agora) envolve listar todas as transmissões de pacotes para um rastreador específico.

DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
FROM [Sessions]
JOIN [PacketTransmissions] ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
WHERE [Sessions].[TrackerId] = @TrackerId
AND [PacketTransmissions].[DateProcessed] > @StartDate
AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC

Isso foi bom no começo, mas agora há muitos dados, diminuiu a velocidade. Minha tentativa de obter o plano de consulta hoje levou 2 minutos e mostra que ele estará usando um table scan , em vez do índice que criei. Mesmo quando eu forço o index , ainda é muito lento.

Em comparação, se eu escolher uma sessão primeiro e pesquisar apenas as transmissões de pacotes gravadas nessa sessão, a consulta usará o índice e será incrivelmente rápida.

Minha tentativa mais bem-sucedida de acelerar a consulta foi ordenar os resultados primeiro por id de sessão, depois por data de processamento, para corresponder à ordem do índice. Embora isso nem sempre seja tecnicamente correto, é aceitável. No entanto, mesmo isso começou a expirar, e sinto que há algo errado com minha compreensão de como fazer o JOINmais rápido.

O que posso fazer para melhorar o desempenho desta consulta?

Consultar com DATETIMEvariáveis ​​em vez de DATETIME2simplificou o plano de consulta, porém ainda é muito lento.

  • As sessões têm 265.929 linhas

  • PacketTransmissions tem 32.916.233 linhas

    Isso resulta em 123,7 pacotes por sessão, em média.

  • Algumas das sessões são para dispositivos não registrados, então eles criam uma sessão, enviam entre um e três pacotes e, em seguida, a sessão é rejeitada pelo servidor.

  • Normalmente estarei depurando um dispositivo registrado, então o número real de pacotes por sessão é consideravelmente maior, entre 300 e 5000 pacotes por sessão

  • Alguns rastreadores podem manter a mesma sessão por um mês de cada vez se tiverem conectividade

No passado, tive uma experiência ruim ao alterar o índice clusterizado para usar uma chave não sequencial. Isso resulta em muitas gravações fora de ordem e divisões de página, e o desempenho da inserção cai significativamente.

O problema com os planos de execução reais é que não quero executar o banco de dados no máximo DTU por até uma hora e, potencialmente, ter inserções falhando nesse meio tempo.

performance join

4 respostas

  1. Best Answer

    Talvez isso seja loucura, mas eu gosto de tentar um pouco de pensamento azul de vez em quando, então eu consideraria adicionar a TrackerIdcoluna à dbo.PacketTransmissionstabela para evitar completamente a junção. Obviamente, isso significa que você precisa modificar o procedimento de inserção de linha para a tabela, o que pode ou não ser viável.

    No entanto, essa mudança, combinada com um índice simples:

    CREATE INDEX IX_PacketTransmissions ON dbo.PacketTransmissions
(
    TrackerId ASC
    , DateProcessed ASC
) 
INCLUDE (Id); --not strictly required, since the primary key 
              --is always included in every non-clustered index
              --I include them just to be explicit

    cria um plano de consulta usando uma busca de índice comum, combinada com uma pesquisa de chave para cada linha retornada. Como em:

    insira a descrição da imagem aqui

    Para testar isso, criei um exemplo verificável minimamente completo:

    USE tempdb;

IF OBJECT_ID(N'dbo.Sessions', N'U') IS NOT NULL
DROP TABLE dbo.[Sessions];
IF OBJECT_ID(N'dbo.PacketTransmissions', N'U') IS NOT NULL
DROP TABLE dbo.PacketTransmissions;
GO

CREATE TABLE [dbo].[Sessions]
(
      [Id] int 
        CONSTRAINT PK_Sessions
        PRIMARY KEY CLUSTERED
    , [DateConnected] datetime NOT NULL
    , [Origin] nvarchar(max) NULL
    , [TrackerId] int NULL
    , [Imei] nvarchar(max) NULL
    , [Sim] nvarchar(max) NULL
    , [ProtocolVersion] tinyint NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
      [Id] int 
        CONSTRAINT PK_PacketTransmissions 
        PRIMARY KEY CLUSTERED
    , [RequestId] int NULL
    , [SessionId] int NOT NULL
    , [DateProcessed] datetime NOT NULL
    , [Direction] int NOT NULL
    , [Sequence] int NOT NULL
    , [Acknowledgement] int NOT NULL
    , [DateRecorded] datetime NOT NULL
    , [Version] tinyint NOT NULL
    , [Command] tinyint NOT NULL
    , [Flags] tinyint NOT NULL
    , [Checksum] tinyint NOT NULL
    , [Data] varbinary(max) NULL
    , [TrackerId] int NULL
)
GO

INSERT INTO dbo.[Sessions] (Id, DateConnected, Origin, TrackerId, Imei, Sim, ProtocolVersion)
SELECT ROW_NUMBER() OVER (ORDER BY sc1.id)
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(nvarchar(max), CRYPT_GEN_RANDOM(128))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(tinyint, CRYPT_GEN_RANDOM(1))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

INSERT INTO dbo.PacketTransmissions (Id, RequestId, SessionId, DateProcessed, Direction, Sequence, Acknowledgement, DateRecorded, Version, Command, Flags, Checksum, Data, TrackerId)
SELECT ROW_NUMBER() OVER (ORDER BY s.Id)
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(3))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(2))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CRYPT_GEN_RANDOM(128)
    , s.TrackerId
FROM dbo.[Sessions] s
    CROSS JOIN (SELECT v.n
    FROM (VALUES (0), (1))v(n)) v;
GO

    No meu sistema, isso cria cerca de 700.000 linhas de sessão e o dobro desse número de linhas de transmissão.

    A consulta então se torna:

    DECLARE @TrackerId int = 100;
DECLARE @StartDate datetime = '2017-03-10';
DECLARE @EndDate   datetime = '2017-03-12';

SELECT [PacketTransmissions].*
FROM [PacketTransmissions] 
WHERE [PacketTransmissions].[TrackerId] = @TrackerId
    AND [PacketTransmissions].[DateProcessed] > @StartDate
    AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC;
    • 3

  2. Se, como no seu exemplo, as variáveis ​​de data e hora forem seletivas, ou seja. não muito distantes, os seguintes índices devem melhorar o desempenho. Você deve verificar se seus índices atuais são necessários para outras seleções, caso contrário, remova-os.

    CREATE NONCLUSTERED INDEX [IX_PT_DateProcessed]
  ON [dbo].[PacketTransmissions] ( [DateProcessed] ASC )
  INCLUDE ([SessionId])

CREATE NONCLUSTERED INDEX [IX_S_TrackerSession]
  ON [dbo].[Sessions] ( [TrackerId] ASC, [Id] ASC)

    Eles permitirão selecionar as linhas que você precisa sem acesso à tabela - o que no final ainda será necessário, afinal, você precisa de "*" de ambas as tabelas.

    • 2

  3. Sou desenvolvedor Oracle. Do plano de execução infiro que para cada registro de sessão, o SQL busca em média mais de 130 registros de PacketTransmissions. Embora essa seja uma contagem insignificante, se esses registros (130k no total) estiverem dispersos ( ClusteringFactor ) em todas ou na maioria das páginas do IC, o otimizador favorecerá o FTS ou o CI Scan em relação ao CI Seek.

    Uma opção é reconstruir o IC em PacketTransmissions usando (SessionID, DateProcessed) como a chave do IC, desde que sejam exclusivos. Mas isso pode exigir reconstrução frequente.

    Outra opção é, se estiver disponível no Azure, use o Particionamento de Tabela de Hash em (SessionID, DateProcessed). Para ambas as opções, leve em consideração os outros SQLs que acessam a tabela PacketTransmissions e como o desempenho desses SQLs pode ser afetado.

    Observe que o fator de agrupamento é apenas um dos possíveis motivos.

    • 1

  4. Esse link não indica uma verificação de tabela. Isso é uma varredura de índice . Com um intervalo de datas estreito, a data é mais seletiva do que a junção, portanto (corretamente) a data é a primeira.

    Mantenha o índice clusterizado PK nos dois IDs

    Tenha três índices separados

    [PacketTransmissions].[SessionId]   
[PacketTransmissions].[DateProcessed]  
[Sessions].[TrackerId]

    Ou

    [PacketTransmissions].[SessionId], [PacketTransmissions].[DateProcessed]   
[PacketTransmissions].[DateProcessed], [PacketTransmissions].[SessionId]  
[Sessions].[TrackerId]

    Apenas minha formatação - mesma consulta 

    DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
  FROM [Sessions]
  JOIN [PacketTransmissions] 
    ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
   AND [Sessions].[TrackerId] = @TrackerId
   AND [PacketTransmissions].[DateProcessed] > @StartDate
   AND [PacketTransmissions].[DateProcessed] < @EndDate
 ORDER 
    BY [PacketTransmissions].[DateProcessed] DESC
    • -2

relate perguntas