Desafio: Erros gerados em consulta indexada

  • Article

 

No desafio anterior, comentamos sobre a restrição de uso do ORDER BY dentro de uma View. Dessa vez, o desafio está relacionado a uma consulta que passa a gerar erros após a criação de índices.

Imagine uma tabela composta pelos campos (ID, Nome, Idade) como na figura abaixo e uma consulta para determinar o número de pessoas com mais de 18 anos.

image

 SELECT Menores = COUNT(*) FROM vwLista
WHERE campoNome = 'Idade' and CAST(campoValor as INT) > 18


 Menores
-----------
5

(1 row(s) affected)

Os dados foram expostos a partir de uma view, definida sobre as tabelas ListaItem, ColunaItem e DetalheItem.

 CREATE VIEW vwLista 
AS
SELECT l.listaId, c.campoNome, i.campoValor 
FROM dbo.ListaItem l 
    INNER JOIN dbo.DetalheItem i ON l.listaId = i.listaId 
    INNER JOIN dbo.ColunaItem c ON c.colId = i.colunaId

Com o objetivo de melhorar o desempenho, foram criados os seguintes índices:

 CREATE INDEX idxColId ON ColunaItem(colId)
CREATE INDEX idxColName ON ColunaItem(campoNome)
CREATE INDEX idxValor ON DetalheItem(campoValor)
CREATE INDEX idxLista ON ListaItem(listaId)
CREATE INDEX idxColNameId ON ColunaItem(campoNome,colId)

Após essas mudanças, a query começou a retornar erros.

 SELECT Menores = COUNT(*) FROM vwLista
WHERE campoNome = 'Idade' and CAST(campoValor as INT) > 18


 Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value 
'Admin' to data type int.

 

Perguntas:

1) Por que a query começou a falhar após a criação de índices?

2) Qual o problema na forma que foi escrita a query? Qual a forma correta de escrevê-la? (erro conceitual)

3) Como reescrever a View de forma a evitar esse tipo de erro?

4) Existe alguma alternativa para evitar esse tipo de erro sem alterar o código existente?

 

Escreva sua resposta nos comentários. Estou curioso para saber quais serão as soluções propostas.

 

Script para criação de tabela e dados:

    CREATE TABLE ListaItem    (listaId INT, nome VARCHAR(10), itemId INT)   CREATE TABLE ColunaItem    (colId INT, campoNome VARCHAR(10), campoTipo VARCHAR(10))   CREATE TABLE DetalheItem    (itemId INT IDENTITY(1,1),     listaId INT, colunaId INT, campoValor VARCHAR(256))   INSERT ListaItem (listaId, nome) VALUES     (1,'ADM'), (2,'USR1'), (3,'USR2'), (4,'USR3'), (5,'USR4')   INSERT ColunaItem (colId, campoTipo, campoNome) VALUES    (1,'CHAR','Nome'), (2,'INT','Idade')   INSERT DetalheItem (listaId, colunaId, campoValor) VALUES     (1,1,'Admin'), (1,2,'31'), (2,1,'User A'), (2,2,'25'),         (3,1,'User B'), (3,2,'26'), (4,1,'User C'), (4,2,'19'),         (5, 1, 'User D'), (5, 2, '21')       CREATE INDEX idxColunaId ON DetalheItem(colunaId)   GO      CREATE VIEW vwLista    AS   SELECT l.listaId, c.campoNome, i.campoValor FROM dbo.ListaItem l       INNER JOIN dbo.DetalheItem i ON l.listaId = i.listaId       INNER JOIN dbo.ColunaItem c ON c.colId = i.colunaId   GO   -- Criacao dos indices adicionais: a query para de funcionar   CREATE INDEX idxColId ON ColunaItem(colId)   CREATE INDEX idxColName ON ColunaItem(campoNome)   CREATE INDEX idxValor ON DetalheItem(campoValor)   CREATE INDEX idxLista ON ListaItem(listaId)   CREATE INDEX idxColNameId ON ColunaItem(campoNome, colId)

 

desafio.sql

Comments

  • Anonymous
    April 29, 2010
    Vamos la....
  1. Por que a query começou a falhar após a criação de índices? Voltando um pouco no cenário,na query que funciona sem a criação dos indices,quando é feita a query,o query optimizer faz um table scan na tabela colunaItem,seguido de um nesteed loops fazendo um SEEK no indice idxColunaId da tabela DetalheItem,no indice nonclustered,temos o RID,ou seja ele vai apontar somente para a pagina e slot aonde esta o registro,não precisando passar pelos campos do tipo varchar,trazendo somente o registro que precisa,no caso > 18,se criarmos os indices idxColId e idxColNameId o query optmizer vai mudar a ordem dos joins, e quando estimamos um plano vemos um table scan na tabela DetalheItem ,ocasionando o problema. Uma consideração,os indices idxColName,idxValor e idxLista criados não implicam no resultado da query.
  2. Qual o problema na forma que foi escrita a query? Qual a forma correta de escrevê-la? (erro conceitual) Poderia ser escrita assim? SELECT Menores = COUNT(*) FROM vwLista
    WHERE campoNome = 'Idade' and campoValor > '18'
  3. Como reescrever a View de forma a evitar esse tipo de erro? Uma alternativa que eu achei é forçando o indice idxColunaId alter VIEW vwLista
    AS
    SELECT  l.listaId, c.campoNome, i.campoValor
    FROM dbo.ListaItem l INNER JOIN dbo.DetalheItem i with(index = idxColunaId )
    ON l.listaId = i.listaId
    INNER JOIN dbo.ColunaItem c ON c.colId = i.colunaId
  4. Existe alguma alternativa para evitar esse tipo de erro sem alterar o código existente? Quando voce fala em mudar o codigo voce quer dizer a view ou a maneira de fazer o segundo select? Daaaaa hr esses tipos de artigos..rsrs...escreve mais....rsrss... Abraço.....

  • Anonymous
    May 03, 2010
    Ótima resposta Fernando!!! Já matou dois problemas. A resposta 1 está CORRETA. O problema foi na forma que o Query Optimizer decidiu avaliar a ordem das tabelas: ColunaItem ou DetalheItem. A resposta 3 está CORRETA. Uma das possibilidades é utilizar um hint, fazendo o Optimizer a adotar um caminho forçado. Existem outras soluções para esse item!!! (Dica: tente reescrever uma view com PIVOT) As demais questões continuam em aberto.

  • Anonymous
    March 17, 2011
    A questão 2 pode ser resolvida com a seguinte query: SELECT Menores = COUNT(*) FROM vwLista WHERE campoNome = 'Idade' and ISNUMERIC(campoValor) = 1 and CAST(campoValor as INT) > 18 pois o campo valor é varchar e tem valor não número inserido, então verificado se o valor é número elimina o erro. Abs.

  • Anonymous
    March 17, 2011
    Obrigado por compartilhar o seu exemplo! Fiquei um tempo testando aqui as possibilidades. Bom palpite Rafael, chegou muito próximo. Mas veja que existem casos similares que ele falham: a) SELECT Menores = COUNT(*) FROM vwLista WHERE campoNome = 'Idade' and ISNUMERIC(campoValor)1 = 1 and CAST(campoValor as INT) > 18 b) SELECT Menores = COUNT() FROM vwLista WHERE campoNome = 'Idade' and CAST(campoValor as INT) > 18 and ISNUMERIC(campoValor) = 1 Você sabe dizer quais são as diferenças que provocam esse tipo de comportamento?

  • Anonymous
    March 23, 2011
    Olá Fabrício, O erro ocorre porque o plano de execução inverte a ordem da consulta. Olha o predicate que ele gerou. CONVERT(int,[ANEEL_SRI_TEST].[dbo].[DetalheItem].[campoValor] as [i].[campoValor],0)>(18) AND isnumeric([ANEEL_SRI_TEST].[dbo].[DetalheItem].[campoValor] as [i].[campoValor])=(1) Na query antiga, o predicate tinha a função isnumeric executando antes do convert, por isso não dava erro: SELECT Menores = COUNT(*) FROM vwLista WHERE campoNome = 'Idade' and ISNUMERIC(campoValor) = 1 and CAST(campoValor as INT) > 18 Agora eu não sei porque no exemplo A ele inverte a ordem. E no exemplo B você invertou a ordem, fazendo com que o plano de execução fizesse primeiro o Convert gerando o erro. Gostaria que explicasse porque isso ocorre.

  • Anonymous
    March 31, 2011
    Olá Rafael! O problema ocorre porque na linguagem SQL não existe uma ordem de precedência entre as condições. Isso significa que o Otimizador é livre para decidir qual condição será avaliada primeira. No caso, a solução seria adotar uma construção CASE WHEN. SELECT Menores = COUNT(*) FROM vwLista WHERE campoNome = 'Idade' AND CASE WHEN ISNUMERIC(campoValor) = 1 THEN  CAST(campoValor as INT)  END > 22 Abraços, Fabricio

  • Anonymous
    April 05, 2011
    Interessante! Mas com a utilização do Case When a performance não poderia ser afetada? Neste exemplo a diferença é muito pequena. Utilizando o Case When temos o Estimated Subtree Cost 0,0035993 contra um Estimated Subtree Cost de 0,0033917 sem o Case When no Index Seek de ColunaItem.idxColNameId. Utilizando o Case When temos o Estimated Subtree Cost 0,003396 contra um Estimated Subtree Cost de 0,0033184 sem o Case When no Index Seek de ListaItem.idxLista. É sabido que a segunda opção pode não funcionar já que o sql não utiliza uma ordem de precedência entre as condições, mas em relação a custo x benefício essa é a melhor solução? Utilizar vários CASE When na Cláusula  WHERE pode ficar extremamente lenta a query. Já li em foruns para evitar o seu uso e também já tive problemas de performance com este recurso. Então, quais são as boas práticas em relação ao uso de Case When nas Cláusulas WHERE? Abraços. Rafael Krisller

  • Anonymous
    April 06, 2011
    Olá Rafael, você tem total razão quanto ao aumento no custo estimado. Por mais que esse custo seja pequeno, quando multiplicamos por múltiplas linhas, ele se torna considerável. Antes de continuar, deixo adicionar as considerações:

  1. A utilização do CASE WHEN é obrigatória nesse caso para garantir a sintaxe correta. Caso contrário, podemos ter uma reordenação do compilador e obter mensagens de erro. Enfim, temos que aceitar esse aumento de consumo. O lado bom é que esse aumento será de poucos milissegundos.
  2. A utilização do CASE WHEN na cláusula WHERE pode tornar sua query extremamente lenta!!! Sim, você escreveu correto. Antes de continuar falando disso, vamos falar sobre o motivo disso: Compilador é limitado no processo de otimizar o comando CASE WHEN e, em muitos casos, ele deixa de utilizar o índice correto. No exemplo acima, o otimizador utiliza o índice associado a coluna CampoNome (veja que esse filtro não possui CASE WHEN). É difícil de escrever de forma simples. Talvez tenha ficado um pouco mais claro. Abraços, Fabricio
  • Anonymous
    April 06, 2011
    Exemplo de uma query com CASE WHEN ruim: SELECT Menores = COUNT(*) FROM vwLista WHERE CASE WHEN campoNome = 'Idade'                    THEN CAST WHEN (campoValor as INT) > 22 THEN 1 END END = 1 Infelizmente o cenário desse desafio não ajuda muito a ilustrar a diferença. Em linhas gerais: o otimizador ignora a maioria das colunas que são usadas em função ou CASE WHEN. Nesse caso, praticamente nenhum índice poderia ser utilizado. Nesse caso específico, a modelagem de escala não é apropriada para boa performance. Então qualquer exemplo vai apresentar um resultado médio para ruim.