Início / dba / Perguntas / 169048
Accepted
carlo.borreo
Asked: 2017-04-05 00:53:22 +0800 CST

Desempenhos SQL de seleção de registros recentemente modificados Solr

  • 772

Eu tenho um banco de dados SQL Server 2916 e Solr. O indexador Solr geralmente executa essas consultas:

select book_collection from books group by book_collection having max(updated_on) > '2017-04-04 09:50:05'

A coluna updated_on é atualizada por um gatilho na inserção/atualização; a cada execução do indexador incremental Solr (a cada 10 minutos), muitas consultas como a acima obtêm os registros modificados mais recentes e os reindexam. Esta tabela, por exemplo, tem cerca de um milhão de linhas e a cada vez a consulta retornaria no máximo 10-20 linhas. Essas consultas acabam na lista das consultas mais caras executadas no banco de dados, então gostaria de otimizá-las. Minhas perguntas:

1) Uma coluna de carimbo de data/hora teria um desempenho melhor do que uma coluna de data/hora?

2) Se eu alterasse a consulta assim, seria mais eficiente?

select distinct book_collection from books where updated_on > '2017-04-04 09:50:05'

O primeiro plano de consulta é o original, o segundo é modificado por mim. O fato de o segundo plano solicitar um índice e o primeiro não, realmente sugere que a consulta modificada pode usar um índice, se presente, e, portanto, é melhor.

Original

Modificado por mim

sql-server sql-server-2016

2 respostas

    1. No SQL Server, TIMESTAMP não representa uma hora, é apenas um número que representa a versão da linha. Portanto, é improvável que você possa alterar o tipo de dados dessa coluna por motivos de desempenho.
    2. Sim, isso provavelmente terá um desempenho melhor, porque pode usar o índice sobre updated_on, algo que a função MAX() proíbe na consulta original. Tanto quanto posso ver, o resultado deve ser idêntico.
    • 3
  2. Best Answer

    Como a timestampé 8 bytes e a datetimeé 8 bytes, não haverá diferença apreciável entre os dois em termos de desempenho.

    Adicionar um índice apropriado fará uma diferença muito maior no trabalho necessário para o SQL Server atender à consulta SOLR.

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

    USE tempdb;
GO
IF OBJECT_ID(N'dbo.Books', N'U') IS NOT NULL
DROP TABLE dbo.Books;
CREATE TABLE dbo.Books
(
    BookID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Books
        PRIMARY KEY CLUSTERED
    , Book_Collection varchar(30) NOT NULL
    , updated_on datetime NOT NULL
    , Pad varchar(100) NOT NULL
) ON [PRIMARY];

;WITH Nums AS (
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
INSERT INTO dbo.Books (Book_Collection, updated_on, Pad)
SELECT v1.Num + v2.num * 10 + v2.num * 100
    , DATEADD(MINUTE, 0 - (v2.Num * 10 + v3.Num * 100), GETDATE())
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
FROM Nums v1
    CROSS JOIN Nums v2
    CROSS JOIN Nums v3
    CROSS JOIN Nums v4;
GO

    O acima insere 10.000 linhas em 1.000 book_collectionvalores, com valores variados updated_on.

    Meu exemplo de consulta SOLR:

    SELECT b.Book_Collection
FROM dbo.Books b
GROUP BY b.Book_Collection
HAVING MAX(b.updated_on) > DATEADD(MINUTE, 0 - 3, GETDATE())

    O plano para esta consulta:

    insira a descrição da imagem aqui

    Estatísticas para esta consulta:

    Tempo de análise e compilação do SQL Server:
    tempo de CPU = 16 ms, tempo decorrido = 26 ms.

    Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, > leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
    Tabela 'Arquivo de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
    Tabela 'Livros'. Contagem de varredura 1, leituras lógicas 164, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server:
    tempo de CPU = 15 ms, tempo decorrido = 57 ms.

    Se adicionarmos esse índice e executarmos novamente a mesma consulta:

    CREATE INDEX IX_Books_Collection
ON dbo.Books (Book_Collection, updated_on);

    Vemos o seguinte plano e estatísticas:

    insira a descrição da imagem aqui

    Tempo de análise e compilação do SQL Server: tempo
    de CPU = 8 ms, tempo decorrido = 8 ms.

    Tabela 'Livros'. Contagem de varredura 1, leituras lógicas 33, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server:
    tempo de CPU = 0 ms, tempo decorrido = 6 ms.

    A E/S é substancialmente reduzida, assim como o tempo de CPU e o tempo decorrido. Claramente, adicionar um índice útil tornará a consulta mais eficiente.

    Se recriarmos o experimento usando uma timestampcoluna no lugar da datetimecoluna, veremos resultados muito semelhantes:

    IF OBJECT_ID(N'dbo.Books', N'U') IS NOT NULL
DROP TABLE dbo.Books;
CREATE TABLE dbo.Books
(
    BookID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Books
        PRIMARY KEY CLUSTERED
    , Book_Collection varchar(30) NOT NULL
    , updated_on timestamp NOT NULL
    , Pad varchar(100) NOT NULL
) ON [PRIMARY];

;WITH Nums AS (
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
INSERT INTO dbo.Books (Book_Collection, Pad)
SELECT v1.Num + v2.num * 10 + v2.num * 100, CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
FROM Nums v1
    CROSS JOIN Nums v2
    CROSS JOIN Nums v3
    CROSS JOIN Nums v4;
GO

    Estatísticas da consulta sem o índice em vigor:

    Tempo de análise e compilação do SQL Server:
    tempo de CPU = 15 ms, tempo decorrido = 19 ms.

    Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
    Tabela 'Arquivo de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
    Tabela 'Livros'. Contagem de varredura 1, leituras lógicas 164, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server:
    tempo de CPU = 16 ms, tempo decorrido = 3 ms.

    E com o índice no lugar:

    Tempo de análise e compilação do SQL Server: tempo
    de CPU = 0 ms, tempo decorrido = 1 ms.

    Tabela 'Livros'. Contagem de varredura 1, leituras lógicas 33, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server:
    tempo de CPU = 0 ms, tempo decorrido = 5 ms.

    • 3

relate perguntas