Piores práticas – Utilização do operador NOT IN

Boa Madrugada Pessoal,

Estou aqui enrolado em uma pleno sábado (na verdade já é domingo) trabalhando em um projeto de migração de uma única base de dados. A base em si é bem tranqüila (195MB só), o problema é que há integrações via Linked Servers, pacotes SSIS, COM+ e muitos executáveis não mapeados. Por conta dessa migração, estou aqui aguardando que certos procedimentos sejam executados para verificar se tudo aparentemente funcionou (com certeza na segunda-feira aparecerão outros problemas). Não tive tempo de escrever aqueles imensos artigos que posto semanalmente então hoje falarei de algo mais suscinto. Estou postando um artigo da categoria "piores práticas" que certamente é bem conhecida.

Antes de relatar os pontos negativos da utilização do NOT IN, é necessário como de costume fazer um script de demonstração.

— Cria uma tabela T1
CREATE TABLE T1 (ID INT, NOME CHAR(400))

— Popula a tabela T1 com 1 milhão de registros
DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
    INSERT INTO T1 VALUES (@i,REPLICATE(CHAR(ABS(CHECKSUM(NewID())/10000000)),400))
    SET @i = @i + 1
END

— Cria uma tabela T2 com 950 mil de registros aleatórios com base em T1
SELECT TOP(1950000) * INTO T2 FROM T1 ORDER BY NewID()

Se T1 possui um milhão de registros, se T2 possui 950.000 registros e T1 possui tudo que T2 possui, então T1 possui 50.000 registros que T2 não possui. Para descobrirmos que registros são esses, basta realizar uma query que nos diga o que há em T1 que não tenha em T2. A utilização do NOT IN é certamente a escolha mais natural possível.

SELECT ID, NOME FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

No meu notebook essa instrução demorou 7 segundos para terminar e um péssimo plano de execução. Tenho de reconhecer que é uma consulta muito simples e bastante intuitiva, pois, entre outras coisas ela simplesmente retorna todos os registros em T1 cujo o ID não esteja em T2. Vejamos algumas alternativas para o uso do NOT IN e seus respectivos tempos.

— Uso do NOT EXISTS
SELECT ID, NOME FROM T1
WHERE NOT EXISTS (
    SELECT ID FROM T2
    WHERE T1.ID = T2.ID)

— Uso do LEFT OUTER JOIN
SELECT T1.ID, T1.NOME FROM T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL

— Uso do EXCEPT (Somente 2005 e superiores)
SELECT ID, NOME FROM T1 WHERE ID IN (
    SELECT ID
FROM T1 EXCEPT SELECT ID FROM T2)

O tempo de consulta de todas as alternativas foi superior ao do NOT IN e se considerássemos todos os 4 comandos em um único BATCH (100%), obteríamos estimativas próximas às estimativas abaixo:

Comando

% do Batch

Tempo

% de Tempo

NOT IN

49%

7s

41%

NOT EXISTS

14%

3s

18%

LEFT OUTER JOIN

14%

3s

18%

EXCEPT

23%

4s

23%

A grande maioria iria argumentar que o NOT IN tem um comportamento mais lento, pois, para cada linha em T1 será necessário ler várias vezes as linhas em T2. Isso não deixa de ser verdade e explicar em boa parte a causa de tanta lentidão, uma vez que as demais construções terão um algoritmo um pouco mais inteligente. A presença de um índice nas colunas IDs irá mudar esse comportamento do NOT IN. Uma vez que o índice seja organizado, não será necessário para cada linha em T1 varrer todas as linhas em T2. Para tornar a situação ainda mais performática, os índices serão clusterizados.

— Cria Índices Cluster em ID
CREATE CLUSTERED INDEX IXT1 ON T1 (ID)
CREATE CLUSTERED INDEX IXT1 ON T2 (ID)

Se repetirmos as consultas, teremos os seguintes resultados:

Comando

Tempo (1ª execução)

Tempo (2ª execução)

NOT IN

18s

3s

NOT EXISTS

2s

2s

LEFT OUTER JOIN

4s

3s

EXCEPT

2s

2s

Todos os comandos foram executados duas vezes porque a primeira vez o plano de execução não está em memória e naturalmente o acesso é mais lento. Na segunda execução, por se tratar exatamente do mesmo comando, o plano de execução já está em memória (cachê) e o acesso é mais rápido. Mesmo assim, embora o NOT IN tenha empatado com o LEFT OUTER JOIN (que foi um dos acessos mais rápidos na ausência do índice) ainda ficou entre os mais lentos sendo 50% (1 segundo) mais lento que os mais rápidos. Isso significa que na melhor das hipóteses, se houver índices, o NOT IN ainda tende a ficar mais lento.

Alguns podem achar que ainda assim, a lentidão não é razão suficiente para deixar de utilizar o NOT IN já que mesmo mais lento, ele apresentou um desempenho aceitável. Não acho isso um argumento muito válido, visto que as demais construções também são bem simples e que se cada consulta rodar um pouco mais lenta que as demais, um sistema com múltiplos usuários irá ter seu desempenho total reduzido. Se essa não for uma razão forte o bastante, vejamos outras limitações do uso do NOT IN.

Os scripts anteriores foram apenas para mensurar o desempenho das consultas com um volume razoável de registros. Os próximos scripts não levaram necessariamente o desempenho, por isso utilizarei outras tabelas. As tabelas anteriores podem ser excluídas para liberar recursos (opcionalmente o serviço pode ser reiniciado).

— Cria as tabelas necessárias
CREATE TABLE T1 (ID INT, NOME VARCHAR(50))
CREATE TABLE T2 (ID INT, NOME VARCHAR(50))

— Insere os registros
INSERT INTO T1 (ID, NOME) VALUES (1, ‘Sandro’)
INSERT INTO T1 (ID, NOME) VALUES (2, ‘Dimitri’)
INSERT INTO T1 (ID, NOME) VALUES (3, ‘Gilvan’)

INSERT INTO T2 (ID, NOME) VALUES (1, ‘Sandro’)
INSERT INTO T2 (ID, NOME) VALUES (2, ‘Dimitri’)

Visivelmente podemos perceber que T1 possui um registro a mais que T2 cujo nome é Gilvan. Aplicar o NOT ou qualquer uma das demais alternativas deve retornar esse nome, pois, é o único registro que está em T1 e não está em T2. De fato, uma consulta com o NOT IN irá retornar (como já era esperado) o nome Gilvan. O que aconteceria no entanto, se aparecesse um registro com o ID Null em T2 ?

INSERT INTO T2 (ID, NOME) VALUES (NULL, ‘Ninguém’)

— Uso do NOT IN
SELECT ID, NOME FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

— Uso do NOT EXISTS
SELECT ID, NOME FROM T1
WHERE NOT EXISTS (
    SELECT ID FROM T2
    WHERE T1.ID = T2.ID)

— Uso do LEFT OUTER JOIN
SELECT T1.ID, T1.NOME FROM T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL

— Uso do EXCEPT (Somente 2005 e superiores)
SELECT ID, NOME FROM T1 WHERE ID IN (
    SELECT ID FROM T1 EXCEPT SELECT ID FROM T2)

Ao contrário do esperado, o NOT IN não retornou nenhum registro enquanto que as demais alternativas retornaram o registro Givan conforme o esperado. Qual seria a razão para que o NOT IN não retornasse o nome Gilvan já que visivelmente ele não está em T2 ? A razão é bem simples. Toda vez que o NULL é comparado (salvo raríssimas exceções), a comparação é avaliada como falsa. Comparar um valor nulo com qualquer outro valor retornará falso. O valor nulo é algo desconhecido e se compararmos qualquer coisa com algo desconhecido não saberemos se o resultado é ou não verdadeiro (e por isso que o padrão é falso). Assim sendo, ao comparar não somente o nome Gilvan como os demais com o valor NULL a comparação é falsa e por isso nenhum registro é retornado.

Enquanto o NOT IN pode potencialmente retornar resultados indesejados, o mesmo não acontece com as demais alternativas. Seus métodos de comparação passam por etapas e avaliações que não utilizaram a mesma lógica do NOT IN e o nome Gilvan será retornado em todas as demais alternativas. Isso significa que além do desempenho, o NOT pode retornar resultados não esperados (não podemos dizer "errados" já que é uma questão de interpretação da lógica de três valores (verdadeiro, falso e desconhecido)).

Alguns argumentaram que esse tipo de situação é bastante raro visto que dificilmente aparecerá um ID nulo. Concordo que é uma situação difícil de aparecer visto que na maioria das vezes o NOT IN será realizado contra colunas NOT NULL (tipicamente as chaves primárias), mas a situação descrita é uma preocupação a mais ao se utilizar o operador NOT IN enquanto que as demais alternativas dispensam essa preocupação. Mostrarei a seguir outra limitação do uso do NOT IN. As tabelas T1 e T2 podem ser eliminadas.

— Cria tabelas de empregados
CREATE TABLE Empregados (
    IDDepartamento INT,
    IDEmpregado INT,
    Nome VARCHAR(50),
    CONSTRAINT PK_EMP PRIMARY KEY(IDDepartamento, IDEmpregado))

CREATE TABLE EmpregadosDesligados (
    IDDepartamento INT,
    IDEmpregado INT,
    Nome VARCHAR(50),
    CONSTRAINT PK_EMPDesl PRIMARY KEY(IDDepartamento, IDEmpregado))

— Insere os registros necessários
INSERT INTO Empregados VALUES (1,11,‘Rogério’)
INSERT INTO Empregados VALUES (11,1,‘Débora’)
INSERT INTO Empregados VALUES (1,2,‘Júlio’)
INSERT INTO Empregados VALUES (1,3,‘Yves’)

INSERT INTO EmpregadosDesligados VALUES (1,11,‘Rogério’)

Se há uma tabela contendo todos os empregados e uma tabela contendo os empregados desligados, para descobrir os empregados atuais basta apenas realizar uma consulta de todos os registros que estejam em empregados que não estejam em empregados desligados. Assim, todo empregado que não esteja desligado representa uma empregado do quadro da empresa. O operador NOT IN seria uma escolha certa, mas agora há uma limitação. O operador NOT IN é utilizado sobre um campo específico e nesse caso, como a chave primária consiste em duas colunas, ele é limitado. Alguns SGBDs conseguem transpor essa limitação através de extensões. Ex:

SELECT Nome FROM Empregados
WHERE (IDDepartamento, IDEmpregado)
    NOT IN (
        SELECT IDDepartamento, IDEmpregado
        FROM EmpregadosDesligados)

Cabe ressaltar que a construção acima é proprietária e não é ANSI Compliance. Isso significa que há uma chance enorme do comando acima não funcionar na maioria dos SGBDs. O SQL Server por exemplo não contempla a possibilidade acima e nesse caso a limitação do NOT IN em trabalhar com chaves compostas, ou melhor dizendo mais de uma coluna fica ainda mais aparente. A solução normal é tentar a concatenação.

SELECT Nome FROM Empregados
WHERE (
    CAST(IDDepartamento AS VARCHAR(2)) +
    CAST(IDEmpregado AS VARCHAR(2))) NOT IN (
        SELECT
            CAST(IDDepartamento AS VARCHAR(2)) +
            CAST(IDEmpregado AS VARCHAR(2))       
        FROM EmpregadosDesligados)

Ao executar essa consulta, apenas os empregados Júlio e Yves foram recuperados. De fato Rogério havia sido desligado e não deveria ser mostrado, mas e quanto a Débora ? Ela não está cadastrada na tabela de empregado desligados, mas ainda não assim não foi recuperada junto com os demais. Isso se deve ao fato de que a combinação Departamento e Empregado de Rogério (1, 11) resulta na string "111" e a combinação Departamento e Empregado de Débora (11, 1) também resulta na string "111" e como as strings são iguais, Débora não será recuperada. Essa é mais é um preocupação que o NOT IN necessita e que é dispensada pelas demais construções (O EXCEPT também possui a mesma limitação visto que depende do operador IN).

— Uso do NOT EXISTS
SELECT Nome FROM Empregados AS E
WHERE NOT EXISTS (
    SELECT * FROM EmpregadosDesligados AS ED
    WHERE E.IDDepartamento = ED.IDDepartamento AND
    E.IDEmpregado = ED.IDEmpregado)

— Uso do LEFT OUTER JOIN
SELECT E.Nome FROM Empregados AS E
LEFT OUTER JOIN EmpregadosDesligados AS ED
    ON E.IDDepartamento = ED.IDDepartamento AND
    E.IDEmpregado = ED.IDEmpregado
WHERE
    ED.IDDepartamento IS NULL AND ED.IDEmpregado IS NULL

Uma forma de contornar a possível ambiguidade existente no NOT IN é utilizar um delimitador. Ex:

SELECT Nome FROM Empregados
WHERE (
    CAST(IDDepartamento AS VARCHAR(2)) + ‘_’ +
    CAST(IDEmpregado AS VARCHAR(2))) NOT IN (
        SELECT
            CAST(IDDepartamento AS VARCHAR(2)) + ‘_’ +
            CAST(IDEmpregado AS VARCHAR(2))       
        FROM EmpregadosDesligados)

Dessa vez, como esperado, os empregados Júlio, Yves e Débora foram retornados. O uso do caractér "_" desfez a ambiguidade, visto que a string de Rogério é "1_11" e a de Débora é "11_1". Uma vez que essas strings são diferentes, Débora não será excluída da consulta. O problema é que uma construção dessas dificilmente será performática, pois, para cada registro em Empregados será necessário fazer a concatenação para posterior comparação. O mesmo será feito em EmpregadosDesligados. Em outras palavras se Empregados possui X registros e Empregados Desligados possuir Y registros será necessário X * Y comparações. Mesmo que existam índices sobre IDDepartamento e IDEmpregado, os mesmos serão ignorados, pois, o que é comparado é a concatenação e não as colunas. A última vez que presenciei isso, o tempo de execução de uma consulta aumentou de 14 segundos para 5 minutos (foi no Access, mas o problema era exatamente o mesmo).

Acredito que essa limitação do NOT IN aliada ao problema do NULL juntamente com os problemas de desempenho sejam razões suficientes para simplesmente não utilizar esse operador em consultas de alto desempenho que retornem os dados de forma "esperada". As construções NOT EXISTS e LEFT OUTER JOIN contornam os problemas e limitações além de terem na esmagadora maioria dos casos um desempenho superior. O operador EXCEPT sobre certas circunstância também pode ser utilizado para todos os casos (há limitações, mas o conhecimento de como esse operador funciona pode superá-las).

E quando usar o NOT IN então ?

O único ponto positivo do NOT IN em relação às demais construções é que ele é de entendimento muito simples (praticamente óbvio). Eu consideraria utilizá-lo em duas únicas situações: Consultas Seletivas e utilização de Valores Fixos (de preferência as duas em conjunto).

Quando uma consulta já possui outras condições na cláusula WHERE que sejam bastante seletivas, ou seja que retornem poucos valores, a perda de desempenho do NOT IN não fará diferença. E nesse caso (desde que não incorra nos problemas de resultados inesperados ou nas comparações de chaves compostas) o NOT IN pode ser utilizado. No exemplo abaixo, se Campo1 e Campo2 já filtrarem bastante os registros, o NOT IN pode ser utilizado.

SELECT <Campos> FROM Tabela
WHERE Campo1 = <Valor> AND Campo2 = <Valor> AND
Campo3 NOT IN (SELECT Campo FROM Tabela)

O uso do NOT IN para valores fixos também é uma utilização que pode ser considerada. Ex:

SELECT <Campos> FROM Tabela
WHERE Campo NOT IN (1,2)

Se o NOT IN só possuísse desvantagens era bem provável que fosse retirado ou extremamente desaconselhado na documentação. Isso não ocorre porque ele possui seus pontos positivos e é importante saber explorá-los. A recomendação não é simplesmente evitá-lo, mas sim conhecer suas claras desvantagens e evitar sua utilização indiscriminada (principalmente com subqueries).

Agora vou para casa finalmente…

[ ]s,

Gustavo

6 Respostas para “Piores práticas – Utilização do operador NOT IN

  1. Awe gustavo, tenho uma duvida pra tirar contigo…te add no msn..qlq coisa posso postar aki…vlw!

  2. Pingback: Dica rápida – Removendo NOT IN | Thiago Carlos de Alencar

  3. Roberson Ferreira

    Legal, Gustavo. Obrigado.

  4. Excelente Post Gustavo! Já sabia que o NOT IN não era aconselhável em grande parte dos casos, mas você explicou extremamente bem as alternativas pra contornar a necessidade de sua utilização. Parabéns!

  5. Muinto Bom,Obrigado mesmo.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s