none
Uso de OR e ISNULL X Query Dinâmica RRS feed

Respostas

  • Boa Tarde,

    O uso de ISNULL na cláusula WHERE representa uma facilidade enorme para os desenvolvedores principalmente em consultas com muitos parâmetros opcionais. Entretanto, dada a semântica da consulta e a impossibilidade de saber que parâmetros serão previamente informados, o plano de execução será ruim (pior ainda se ficar dentro de uma SP). O script abaixo demonstra o quanto o uso do ISNULL pode ser prejudicial nesse caso:

    -- Cria uma tabela
    CREATE TABLE T (ID INT IDENTITY(1,1), Nome CHAR(8000) DEFAULT REPLICATE('a',8000))
    
    -- Cria um índice sobre a coluna ID
    CREATE CLUSTERED INDEX IX ON T (ID)
    
    -- Insere 10.000 registros
    INSERT INTO T DEFAULT VALUES
    GO 10000
    
    -- Ativa as estatísticas de IO
    SET STATISTICS IO ON
    
    -- Faz uma consulta procurando o ID 2319
    SELECT ID, Nome FROM T WHERE ID = 2319
    -- Foram lidas seis páginas e o índice foi usado
    
    -- Faz uma consulta com o uso de ISNULL
    DECLARE @ID INT
    SET @ID = 2319
    SELECT ID, Nome FROM T WHERE ID = ISNULL(@ID,ID)
    -- Foram lidas 10020 páginas e foi feita uma varredura de tabela
    

    As leituras de páginas e os planos de execução apresentados mostram claramente o quanto ISNULL em cláusula WHERE pode ser ruim (como qualquer outra função que use uma coluna como argumento). Se há necessidade de informar parâmetros opcionais, você pode montar vários IFs (trabalhoso porém eficiente) ou optar por instruções SQL dinâmicas (menos seguro porém eficiente). Se for colocar esse tipo de construção dentro de uma SP, recomendo o uso da cláusula WITH RECOMPILE. O livro Inside T-SQL Querying (2005 ou 2008) explica em detalhes o porquê desse comportamento.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar quarta-feira, 27 de outubro de 2010 13:30
    • Marcado como Resposta CEFL quarta-feira, 27 de outubro de 2010 18:01
    quarta-feira, 27 de outubro de 2010 13:30

Todas as Respostas

  • Carlos,

    O que você deseja fazer, antes de tentarmos analisar qual seria a melhor solução?

    Existem diversas considerações que devemos analisar, inicialmente o volume de dados, formas de acesso, sempre tendo como base o plano de execução e o query processing do SQL Server.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    sexta-feira, 22 de outubro de 2010 13:24
  • em querys para consulta de dados nas tabelas.

    sempre uso o or e isnull no where, se for enviado um valor ele consulta do contrário não.

    quarta-feira, 27 de outubro de 2010 11:44
  • Boa Tarde,

    O uso de ISNULL na cláusula WHERE representa uma facilidade enorme para os desenvolvedores principalmente em consultas com muitos parâmetros opcionais. Entretanto, dada a semântica da consulta e a impossibilidade de saber que parâmetros serão previamente informados, o plano de execução será ruim (pior ainda se ficar dentro de uma SP). O script abaixo demonstra o quanto o uso do ISNULL pode ser prejudicial nesse caso:

    -- Cria uma tabela
    CREATE TABLE T (ID INT IDENTITY(1,1), Nome CHAR(8000) DEFAULT REPLICATE('a',8000))
    
    -- Cria um índice sobre a coluna ID
    CREATE CLUSTERED INDEX IX ON T (ID)
    
    -- Insere 10.000 registros
    INSERT INTO T DEFAULT VALUES
    GO 10000
    
    -- Ativa as estatísticas de IO
    SET STATISTICS IO ON
    
    -- Faz uma consulta procurando o ID 2319
    SELECT ID, Nome FROM T WHERE ID = 2319
    -- Foram lidas seis páginas e o índice foi usado
    
    -- Faz uma consulta com o uso de ISNULL
    DECLARE @ID INT
    SET @ID = 2319
    SELECT ID, Nome FROM T WHERE ID = ISNULL(@ID,ID)
    -- Foram lidas 10020 páginas e foi feita uma varredura de tabela
    

    As leituras de páginas e os planos de execução apresentados mostram claramente o quanto ISNULL em cláusula WHERE pode ser ruim (como qualquer outra função que use uma coluna como argumento). Se há necessidade de informar parâmetros opcionais, você pode montar vários IFs (trabalhoso porém eficiente) ou optar por instruções SQL dinâmicas (menos seguro porém eficiente). Se for colocar esse tipo de construção dentro de uma SP, recomendo o uso da cláusula WITH RECOMPILE. O livro Inside T-SQL Querying (2005 ou 2008) explica em detalhes o porquê desse comportamento.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar quarta-feira, 27 de outubro de 2010 13:30
    • Marcado como Resposta CEFL quarta-feira, 27 de outubro de 2010 18:01
    quarta-feira, 27 de outubro de 2010 13:30