SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Kermit

Asked: 2013-07-17 15:56:40 +0800 CST2013-07-17 15:56:40 +0800 CST 2013-07-17 15:56:40 +0800 CST

função trava com operação de caso nulo

772

Criei uma função que aceita data inicial e final, sendo a data final opcional. Em seguida, escrevi um CASEno filtro para usar a data de início se nenhuma data de término for passada.

CASE WHEN @dateEnd IS NULL
    THEN @dateStart
    ELSE @dateEnd
END

Quando eu chamo a função para o mês mais recente dos dados:

SELECT * FROM theFunction ('2013-06-01', NULL)

... a consulta trava. Se eu especificar a data final:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

... o resultado é retornado normalmente. Tirei o código da função e executei-o bem dentro de uma janela de consulta. Também não consigo duplicar o problema no violino. Uma consulta como:

SELECT * FROM theFunction ('2013-04-01', '2013-06-01')

... também funciona bem.

Existe alguma coisa na consulta (abaixo) que pode estar causando o travamento da função quando um NULLé passado para a data final?

SQL Fiddle

Plano de execução paraSELECT * FROM theFunction ('2013-06-01', '2013-06-01')
Plano estimado paraSELECT * FROM theFunction ('2013-06-01', NULL)

2 respostas

Voted

Martin Smith · Answer 1 · 2013-07-18T13:03:31+08:00

Parte de sua consulta inicial é a seguinte.

  FROM   [dbo].[calendar] a
          LEFT JOIN [dbo].[colleagueList] b
            ON b.[Date] = a.d
   WHERE  DAY(a.[d]) = 1
          AND a.[d] BETWEEN @dateStart AND COALESCE(@dateEnd,@dateStart)

Essa seção do plano é mostrada abaixo

insira a descrição da imagem aqui

Sua consulta revisada BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart)tem isso para a mesma junção

insira a descrição da imagem aqui

A diferença parece ser que ISNULLsimplifica ainda mais e, como resultado, você obtém estatísticas de cardinalidade mais precisas indo para a próxima junção. Esta é uma função com valor de tabela embutida e você a está chamando com valores literais para que ela possa fazer algo como.

 a.[d] BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) 
 a.[d] BETWEEN '2013-06-01' AND ISNULL(NULL,'2013-06-01') 
 a.[d] BETWEEN '2013-06-01' AND '2013-06-01'
 a.[d] = '2013-06-01'

E como há um predicado de junção equi b.[Date] = a.d, o plano também mostra um predicado de igualdade b.[Date] = '2013-06-01'. Como resultado, a estimativa de cardinalidade das 28,393linhas provavelmente será bastante precisa.

Para a versão CASE/ COALESCEquando @dateStarte @dateEndsão o mesmo valor, simplifica OK para a mesma expressão de igualdade e fornece o mesmo plano, mas quando @dateStart = '2013-06-01'e @dateEnd IS NULLsó vai até

a.[d]>='2013-06-01' AND a.[Date]<=CASE WHEN (1) THEN '2013-06-01' ELSE NULL END

que também se aplica como um predicado implícito em ColleagueList. O número estimado de linhas desta vez é 79.8linhas.

A próxima junção é

   LEFT JOIN colleagueTime
     ON colleagueTime.TC_DATE = colleagueList.Date
        AND colleagueTime.ASSOC_ID = CAST(colleagueList.ID AS VARCHAR(10))

colleagueTimeé uma 3,249,590tabela de linhas que é (novamente) aparentemente uma pilha sem índices úteis.

Essa discrepância nas estimativas afeta a escolha de união usada. O ISNULLplano escolhe uma junção de hash que verifica a tabela apenas uma vez. O COALESCEplano escolhe uma junção de loops aninhados e estima que ainda precisará apenas escanear a tabela uma vez e ser capaz de processar o resultado e reproduzi-lo 78 vezes. ou seja, estima que os parâmetros correlacionados não serão alterados.

Pelo fato de que o plano de loops aninhados ainda estava em andamento após duas horas, essa suposição de uma única verificação colleagueTimeparece ser altamente imprecisa.

Quanto ao motivo pelo qual o número estimado de linhas entre as duas junções é muito menor, não tenho certeza sem poder ver as estatísticas nas tabelas. A única maneira de distorcer tanto as contagens de linhas estimadas em meus testes foi adicionando uma carga de NULLlinhas (isso reduziu a contagem de linhas estimadas, embora o número real de linhas retornadas permanecesse o mesmo).

A contagem de linhas estimada no COALESCEplano com meus dados de teste estava na ordem de

number of rows matching >= condition * 30% * (proportion of rows in the table not null)

Ou em SQL

SELECT 1E0 * COUNT([Date]) / COUNT(*) * ( COUNT(CASE
                                                  WHEN [Date] >= '2013-06-01' THEN 1
                                                END) * 0.30 )
FROM   [dbo].[colleagueList]

mas isso não condiz com seu comentário de que a coluna não tem NULLvalores.

Kermit · Answer 2 · 2013-07-18T05:07:28+08:00

Parece que houve um problema com os tipos de dados. ISNULLcorrigiu o problema (obrigado ypercube ). Depois de algumas pesquisas, COALESCEé o equivalente à CASEdeclaração que eu estava usando:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

Paulo White explica que:

COALESCE( expression [ ,...n ] )retorna o tipo de dados da expressão com a precedência de tipo de dados mais alta.

ISNULL(check_expression, replacement_value)retorna o mesmo tipo que check_expression.

Para evitar problemas de tipo de dados, parece ISNULLser a função apropriada a ser usada para lidar com apenas duas expressões.

Trechos do Plano XML

plano XML usando CASE, a expressão 2 é NULL:

SELECT * FROM theFunction ('2013-06-01', NULL)

<ScalarOperator ScalarString="CASE WHEN (1) THEN '2013-06-01' ELSE NULL END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Const ConstValue="(1)"/>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="'2013-06-01'"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

Plano XML usando CASE, a expressão 2 é uma data:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
      </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

plano XML usando ISNULL, a expressão 2 é NULL:

SELECT * FROM theFunction ('2013-06-01', NULL)

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

Plano XML usando ISNULL, a expressão 2 é uma data:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

função trava com operação de caso nulo

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Conceder acesso a todas as tabelas para um usuário

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

função trava com operação de caso nulo

2 respostas

relate perguntas