Início / dba / Perguntas / 130969
Accepted
Shnugo
Asked: 2016-02-25 01:07:11 +0800 CST

Desempenho muito estranho com um índice XML

  • 772

Minha pergunta é baseada nisso: https://stackoverflow.com/q/35575990/5089204

Para dar uma resposta, fiz o seguinte cenário de teste.

cenário de teste

Primeiro eu crio uma tabela de teste e a preencho com 100.000 linhas. Um número aleatório (0 a 1000) deve levar a aproximadamente 100 linhas para cada número aleatório. Esse número é colocado em uma coluna varchar e como um valor em seu XML.

Aí eu faço uma chamada como o OP aí precisa com .exist() e com .nodes() com uma pequena vantagem para o segundo, mas ambos demoram de 5 a 6 segundos. Na verdade, faço as chamadas duas vezes: uma segunda vez na ordem trocada e com parâmetros de pesquisa ligeiramente alterados e com "//item" em vez do caminho completo para evitar falsos positivos por meio de resultados ou planos em cache.

Então eu crio um índice XML e faço as mesmas chamadas

Agora - o que realmente me surpreendeu! - o .nodescaminho completo é muito mais lento do que antes (9 segundos), mas .exist()é reduzido para meio segundo, com caminho completo até cerca de 0,10 segundos. (enquanto .nodes()com caminho curto é melhor, mas ainda muito atrás .exist())

Perguntas:

Meus próprios testes resumem: os índices XML podem explodir extremamente um banco de dados. Eles podem acelerar as coisas extremamente (s. edit 2), mas também podem retardar suas consultas. Gostaria de entender como funcionam... Quando se deve criar um índice XML? Por que .nodes()com um índice pode ser pior do que sem? Como alguém poderia evitar o impacto negativo?

CREATE TABLE #testTbl(ID INT IDENTITY PRIMARY KEY, SomeData VARCHAR(100),XmlColumn XML);
GO

DECLARE @RndNumber VARCHAR(100)=(SELECT CAST(CAST(RAND()*1000 AS INT) AS VARCHAR(100)));

INSERT INTO #testTbl VALUES('Data_' + @RndNumber,
'<error application="application" host="host" type="exception" message="message" >
  <serverVariables>
    <item name="name1">
      <value string="text" />
    </item>
    <item name="name2">
      <value string="text2" />
    </item>
    <item name="name3">
      <value string="text3" />
    </item>
    <item name="name4">
      <value string="text4" />
    </item>
    <item name="name5">
      <value string="My test ' +  @RndNumber + '" />
    </item>
    <item name="name6">
      <value string="text6" />
    </item>
    <item name="name7">
      <value string="text7" />
    </item>
  </serverVariables>
</error>');

GO 100000

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_no_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_no_index;
GO

CREATE PRIMARY XML INDEX PXML_test_XmlColum1 ON #testTbl(XmlColumn);
CREATE XML INDEX IXML_test_XmlColumn2 ON #testTbl(XmlColumn) USING XML INDEX PXML_test_XmlColum1 FOR PATH;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_with_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_with_index;
GO

DROP TABLE #testTbl;

EDIÇÃO 1 - Resultados

Este é um resultado com o SQL Server 2012 instalado localmente em um laptop médio Neste teste não consegui reproduzir o impacto extremamente negativo no NodesFullPath_with_index, embora seja mais lento do que sem o índice ...

NodesFullPath_no_index    6.067
ExistFullPath_no_index    6.223
ExistShortPath_no_index   8.373
NodesShortPath_no_index   6.733

NodesFullPath_with_index  7.247
ExistFullPath_with_index  0.217
ExistShortPath_with_index 0.500
NodesShortPath_with_index 2.410

EDIT 2 Teste com XML maior

De acordo com a sugestão de TT, usei o XML acima, mas copiei os item-nodes para chegar a cerca de 450 itens. Deixei o hit-node bem alto no XML (porque acho que .exist()isso pararia no primeiro hit, enquanto .nodes()continuaria)

A criação do índice XML aumentou o arquivo mdf para ~ 21 GB, ~ 18 GB parecem pertencer ao índice (!!!)

NodesFullPath_no_index    3min44
ExistFullPath_no_index    3min39
ExistShortPath_no_index   3min49
NodesShortPath_no_index   4min00

NodesFullPath_with_index  8min20
ExistFullPath_with_index  8,5 seconds !!!
ExistShortPath_with_index 1min21
NodesShortPath_with_index 13min41 !!!
sql-server xml

1 respostas

  1. Best Answer

    Com certeza há muita coisa acontecendo aqui, então teremos que ver aonde isso leva.

    Em primeiro lugar, a diferença de tempo entre o SQL Server 2012 e o SQL Server 2014 se deve ao novo estimador de cardinalidade no SQL Server 2014. Você pode usar um sinalizador de rastreamento no SQL Server 2014 para forçar o antigo estimador e verá o mesmo tempo características no SQL Server 2014 como no SQL Server 2012.

    Comparar nodes()vs exist()não é justo, pois eles não retornarão o mesmo resultado se houver mais de um elemento correspondente no XML para uma linha. exist()retornará uma linha da tabela base independentemente, ao passo que nodes()pode potencialmente fornecer mais de uma linha retornada para cada linha na tabela base.
    Conhecemos os dados, mas o SQL Server não e precisa criar um plano de consulta que leve isso em consideração.

    Para tornar a nodes()consulta equivalente à exist()consulta, você pode fazer algo assim.

    SELECT testTbl.*
FROM testTbl
WHERE EXISTS (
             SELECT *
             FROM XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b)
             )

    Com uma consulta como essa, não há diferença entre usar nodes()ou exist()e isso ocorre porque o SQL Server cria quase o mesmo plano para as duas versões que não usam um índice e exatamente o mesmo plano quando o índice é usado. Isso é verdade tanto para o SQL Server 2012 quanto para o SQL Server 2014.

    Para mim, no SQL Server 2012, as consultas sem o índice XML levam 6 segundos usando a versão modificada da nodes()consulta acima. Não há diferença entre usar o caminho completo ou o caminho curto. Com o índice XML instalado, a versão de caminho completo é a mais rápida e leva 5 ms e usar o caminho curto leva cerca de 500 ms. Examinar os planos de consulta dirá por que há uma diferença, mas a versão resumida é que, quando você usa um caminho curto, o SQL Server procura no índice no caminho curto (uma busca de intervalo usando like) e retorna 700.000 linhas antes de descartar as linhas que não coincidem com o valor. Ao usar o caminho completo, o SQL Server pode usar a expressão de caminho diretamente junto com o valor do nó para fazer a busca e retornar apenas 105 linhas do zero para trabalhar.

    Usando o SQL Server 2014 e o novo estimador de cardinalidade, não há diferença nessas consultas ao usar um índice XML. Sem usar o índice, as consultas ainda levam o mesmo tempo, mas são 15 segundos. Claramente não é uma melhoria aqui ao usar coisas novas.

    Não tenho certeza se perdi completamente a noção do que realmente é sua pergunta, pois modifiquei as consultas para serem equivalentes, mas aqui está o que acredito que seja agora.

    Por que a nodes()consulta (versão original) com um índice XML é significativamente mais lenta do que quando um índice não é usado?

    Bem, a resposta é que o otimizador de plano de consulta do SQL Server faz algo ruim e está introduzindo um operador de spool. Não sei por que, mas a boa notícia é que não existe mais com o novo estimador de cardinalidade no SQL Server 2014.
    Sem índices, a consulta leva cerca de 7 segundos, independentemente do estimador de cardinalidade usado. Com o índice leva 15 segundos com o estimador antigo (SQL Server 2012) e cerca de 2 segundos com o novo estimador (SQL Server 2014).

    Observação: as descobertas acima são válidas com seus dados de teste. Pode haver uma história totalmente diferente para contar se você alterar o tamanho, a forma ou a forma do XML. Não há como saber com certeza sem testar com os dados que você realmente tem nas tabelas.

    Como funcionam os índices XML

    Os índices XML no SQL Server são implementados como tabelas internas. O índice XML primário cria a tabela com a chave primária da tabela base mais a coluna de id do nó, totalizando 12 colunas. Ele terá uma linha por vez element/node/attribute etc., de modo que a tabela pode, é claro, ficar muito grande, dependendo do tamanho do XML armazenado. Com um índice XML primário instalado, o SQL Server pode usar a chave primária da tabela interna para localizar nós XML e valores para cada linha na tabela base.

    Os índices XML secundários vêm em três tipos. Quando você cria um índice XML secundário, há um índice não clusterizado criado na tabela interna e, dependendo do tipo de índice secundário criado, ele terá diferentes colunas e ordens de coluna.

    De CREATE XML INDEX (Transact-SQL) :

    VALUE
    Cria um índice XML secundário em colunas onde as colunas-chave são (valor e caminho do nó) do índice XML primário.

    PATH
    Cria um índice XML secundário em colunas construídas em valores de caminho e valores de nó no índice XML primário. No índice secundário PATH, os valores de caminho e nó são colunas-chave que permitem buscas eficientes ao procurar caminhos.

    PROPRIEDADE
    Cria um índice XML secundário nas colunas (PK, caminho e valor do nó) do índice XML primário onde PK é a chave primária da tabela base.

    Portanto, quando você cria um índice PATH, a primeira coluna desse índice é a expressão de caminho e a segunda coluna é o valor desse nó. Na verdade, o caminho é armazenado em uma espécie de formato compactado e invertido. O fato de ser armazenado invertido é o que o torna útil em pesquisas usando expressões de caminho curto. No caso do caminho curto, você pesquisou //item/value/@string, //item/@namee //item. Como o caminho é armazenado invertido na coluna, o SQL Server pode usar uma busca de intervalo com like = '€€€€€€%onde €€€€€€está o caminho invertido. Quando você usa um caminho completo, não há motivo para usar, likepois todo o caminho é codificado na coluna e o valor também pode ser usado no predicado de busca.

    Suas perguntas :

    Quando alguém deve criar um índice XML?

    Como último recurso, se alguma vez. Melhor projetar seu banco de dados para que você não precise usar valores dentro do XML para filtrar em uma cláusula where. Se você souber de antemão que precisa fazer isso, poderá usar a promoção de propriedade para criar uma coluna computada que poderá indexar, se necessário. Desde o SQL Server 2012 SP1, você também tem índices XML seletivos disponíveis. O funcionamento por trás da cena é praticamente o mesmo que com índices XML regulares, apenas você especifica a expressão de caminho na definição do índice e apenas os nós correspondentes são indexados. Dessa forma, você pode economizar muito espaço.

    Por que .nodes() com um índice pode ser pior do que sem?

    Quando houver um índice XML criado em uma tabela, o SQL Server sempre usará esse índice (as tabelas internas) para obter os dados. Essa decisão é tomada antes que o otimizador tenha uma palavra a dizer sobre o que é rápido e o que não é rápido. A entrada para o otimizador é reescrita de forma que esteja usando as tabelas internas e depois disso cabe ao otimizador fazer o seu melhor como em uma consulta regular. Quando nenhum índice é usado, algumas funções com valor de tabela são usadas em seu lugar. O resultado final é que você não pode dizer o que será mais rápido sem testar.

    Como evitar o impacto negativo?

    teste

    • 33

relate perguntas