Início / dba / Perguntas / 30345
Accepted
JM Hicks
Asked: 2012-12-14 01:53:44 +0800 CST

NEWID() em tabela virtual unida causa comportamento não intencional de aplicação cruzada

  • 772

Minha consulta de trabalho real era uma junção interna, mas este exemplo simples com junção cruzada parece quase sempre reproduzir o problema.

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT NEWID() TEST_ID
) BB ( B )

Com minha junção interna, eu tinha muitas linhas para as quais adicionei a cada um um GUID usando a função NEWID () e, para cerca de 9 de 10 dessas linhas, a multiplicação com a tabela virtual de 2 linhas produziu os resultados esperados, apenas 2 cópias de o mesmo GUID, enquanto 1 em 10 produziria resultados diferentes. Isso foi inesperado para dizer o mínimo e me deu muita dificuldade tentando encontrar esse bug no meu script de geração de dados de teste.

Se você der uma olhada nas consultas a seguir usando também as funções getdate e sysdatetime não determinísticas, não verá isso, de qualquer maneira - sempre vejo o mesmo valor de data e hora em ambas as linhas do resultado final.

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT GETDATE() TEST_ID
) BB ( B )

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT SYSDATETIME() TEST_ID
) BB ( B )

Atualmente, estou usando o SQL Server 2008 e meu trabalho por enquanto é carregar minhas linhas com GUIDs em uma variável de tabela antes de terminar meu script de geração de dados aleatórios. Depois de tê-los como valores em uma tabela em vez de uma tabela virtual, o problema desaparece.

Eu tenho uma solução alternativa, mas estou procurando maneiras de contornar sem tabelas reais ou variáveis ​​de tabela.

Enquanto escrevia isso, tentei sem sucesso estas possibilidades: 1) colocar o newid () em uma tabela virtual aninhada:

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT TEST_ID
    FROM (
        SELECT NEWID() TEST_ID
    ) TT
) BB ( B )

2) envolvendo o newid() dentro de uma expressão de conversão, como:

SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID

3) invertendo a ordem de aparecimento das tabelas virtuais dentro da expressão join

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS JOIN (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

4) usando aplicação cruzada não correlacionada

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

Pouco antes de finalmente postar esta pergunta, agora tentei com sucesso, ao que parece, uma aplicação cruzada correlacionada:

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT A
    FROM (
        SELECT 1 UNION ALL
        SELECT 2
    ) TT ( A )
    WHERE BB.B IS NOT NULL
) AA ( A )

Alguém tem alguma outra solução alternativa mais elegante e simples? Eu realmente não quero usar aplicação cruzada ou correlação para uma multiplicação de linha simples, se não for necessário.

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Esse comportamento é intencional, conforme explicado em detalhes neste relatório de bug do Connect . A resposta mais pertinente da Microsoft é reproduzida abaixo por conveniência (e caso o link morra em algum momento):

    Postado pela Microsoft em 7/7/2008 às 9h27

    Fechando o ciclo. . . Eu discuti esta questão com a equipe de desenvolvimento. E eventualmente decidimos não mudar o comportamento atual, pelos seguintes motivos:

    1. O otimizador não garante tempo ou número de execuções de funções escalares. Este é um princípio estabelecido há muito tempo. É a 'liberdade' fundamental que permite ao otimizador liberdade suficiente para obter melhorias significativas na execução do plano de consulta.

    2. Esse "comportamento uma vez por linha" não é um problema novo, embora não seja amplamente discutido. Começamos a ajustar seu comportamento no lançamento do Yukon. Mas é muito difícil definir com precisão, em todos os casos, exatamente o que isso significa! Por exemplo, isso se aplica a linhas intermediárias calculadas 'no caminho' para o resultado final? - caso em que depende claramente do plano escolhido. Ou aplica-se apenas às linhas que eventualmente aparecerão no resultado concluído? - há uma recursão desagradável acontecendo aqui, como tenho certeza que você concorda!

    3. Como mencionei anteriormente, o padrão é "otimizar o desempenho" - o que é bom para 99% dos casos. O 1% dos casos em que pode alterar os resultados são bastante fáceis de detectar - 'funções' de efeito colateral, como NEWID - e fáceis de 'consertar' (perf de negociação, como consequência). Esse padrão para "otimizar o desempenho" novamente é estabelecido há muito tempo e aceito. (Sim, não é a postura escolhida pelos compiladores para linguagens de programação convencionais, mas que assim seja).

    Assim, nossas recomendações são:

    1. Evite depender de tempo não garantido e semântica de número de execuções.
    2. Evite usar NEWID() profundamente em expressões de tabela.
    3. Use OPTION para forçar um determinado comportamento (trading perf)

    Espero que esta explicação ajude a esclarecer nossos motivos para fechar este bug como "não vai consertar".

    As funções GETDATEe SYSDATETIMEsão de fato não determinísticas, mas são tratadas como constantes de tempo de execução para uma consulta específica. Em termos gerais, isso significa que o valor da função é armazenado em cache quando a execução da consulta é iniciada e o resultado é reutilizado para todas as referências na consulta.

    Nenhuma das 'soluções alternativas' na questão é segura; não há garantia de que o comportamento não mudará na próxima vez que o plano for compilado, na próxima vez que você aplicar um service pack ou atualização cumulativa... ou por outros motivos.

    A única solução segura é usar algum tipo de objeto temporário - uma variável, tabela ou função de várias instruções, por exemplo. Usar uma solução alternativa que parece funcionar hoje com base na observação é uma ótima maneira de experimentar comportamentos inesperados no futuro, geralmente na forma de um alerta de pager às 3h da manhã de domingo.

    • 21

relate perguntas