Início / dba / Perguntas / 10263
Accepted
olle
Asked: 2012-01-07 07:04:35 +0800 CST

Coluna de atualização de um grande conjunto de registros com um valor calculado

  • 772

Estou tentando descobrir qual seria uma boa maneira de escrever a pseudoconsulta abaixo no TSQL, levando em consideração que haverá cerca de 50 registros de actualResult e 100.000 registros de previsão por resultado real envolvido.

update prediction
set points = (
    @points = 0
    if(prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB)
        @points = @points + 3
    if (prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB)
        @points = @points + 1
    select @points
)   
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

o que eu estou pensando é:

  • Existe uma maneira de fazer o cálculo que na realidade é mais complexo em linha? No momento, só posso fazê-lo funcionar se colocá-lo em uma função, mas pelo que entendi, isso significa que ele é constantemente recalculado, mesmo que a entrada seja a mesma (o que eu provavelmente atribuo às previsões)
  • Dessa forma bloquearia toda a gama de registros? E se assim for, há uma maneira de evitar isso? Por exemplo, copiando tudo para uma variável de tabela e, em seguida, fazendo o cálculo sobre ela e atualizando os registros assim que todos os cálculos forem feitos?

estou no caminho certo aqui ou você escreveria de uma maneira diferente?

sql-server sql-server-2008

4 respostas

  1. Tente isto:

    update prediction
set points = 
    (
        0 
        + 
        CASE WHEN prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB
        THEN 3 ELSE 0 END
        +
        CASE WHEN prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB
        THEN 1 ELSE 0 END
    )   
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

    AFAIK - se a função não depende dos dados na consulta, ela chama uma vez por consulta,

    E

    se a função for determinística e vinculada ao esquema - ela não chamará várias vezes para os mesmos dados, MAS deve haver um índice nos dados do argumento E o otimizador deve querer usar esse índice

    Se você deseja bloqueio por linha, use a dica ROWLOCK

    • 1
  2. Best Answer

    Existe uma maneira de fazer o cálculo que na realidade é mais complexo em linha?

    Se você puder dobrar a lógica em uma instrução inline, talvez usando CASE como mostrado abaixo, ela normalmente superará uma UDF escalar (função definida pelo usuário) por ordens de magnitude. O argumento para uma UDF geralmente é o encapsulamento de um cálculo complexo ou comum, mas se for necessário apenas nesta consulta, é melhor inline.

    UPDATE
    p
SET
    p.Points = p.Points +
            CASE 
                WHEN p.valueA = a.valueA AND p.valueB = a.valueB THEN 3
                WHEN p.valueA = a.valueA OR p.valueB = a.valueB THEN 1
                ELSE 0
            END
FROM
    dbo.Prediction p
INNER JOIN
    dbo.ActualResult a
ON  a.Id = p.ActualId
WHERE
    a.Id IN (subquery)

    Dessa forma bloquearia toda a gama de registros?

    Você precisa entender o conceito de níveis de isolamento para determinar a) o que será bloqueado e talvez mais importante b) o que deve ser bloqueado. Resumindo, no nível de isolamento READ COMMITTED padrão, um bloqueio compartilhado será obtido e imediatamente liberado em cada linha de cada tabela conforme ela é lida para realizar a junção. As linhas na previsão que serão atualizadas serão protegidas com atualização e, em seguida, bloqueios exclusivos nas linhas que serão mantidas até que a instrução (ou transação pai) seja confirmada.

    Dependendo da distribuição e volume de atualizações, você pode encontrar linhas acima substituídas por páginas ou tabelas de pior caso.

    E se assim for, há uma maneira de evitar isso? Por exemplo, copiando tudo para uma variável de tabela e, em seguida, fazendo o cálculo sobre ela e atualizando os registros assim que todos os cálculos forem feitos?

    Você não pode impedir o bloqueio de linhas que estão sendo atualizadas ou inseridas, caso contrário, a conformidade com o ACID seria comprometida. Você pode reduzir o bloqueio nas linhas que estão sendo lidas, mas pode ser necessário considerar o contrário e aumentar o nível de isolamento. Se outros processos inserirem ou atualizarem registros enquanto essa atualização ocorrer, você pode acabar com um erro de cálculo.

    De acordo com os níveis de isolamento no mecanismo de banco de dados ...

    As transações especificam um nível de isolamento que define o grau em que uma transação deve ser isolada de recursos ou modificações de dados feitas por outras transações. Os níveis de isolamento são descritos em termos dos quais efeitos colaterais de simultaneidade, como leituras sujas ou leituras fantasmas, são permitidos.

    Um rastreamento do Profiler em eventos Lock:Acquired/Released e/ou uso do sinalizador de rastreamento 1200 em uma máquina de teste isolada pode ser muito útil para entender a sequência de bloqueios aplicados e liberados em diferentes níveis de isolamento.

    • 1

  3. Outra coisa que você poderia fazer se estiver preocupado em bloquear grandes porções de uma tabela ou envolver um gatilho subjacente em milhares de chamadas ao mesmo tempo é examinar mais uma abordagem de loop/chunk. Dado que você tem ~ 50 registros ActualResult, você pode percorrer esses 1-5 de cada vez e provavelmente obter um desempenho decente. Isso também pode evitar que seus bloqueios de linha se transformem em bloqueios de tabela.

    • 1

  4. Se o objetivo é manter prediction-se atualizado de acordo com os dados da actualResulttabela, você pode tentar usar uma abordagem diferente. Escreva AFTER INSERT,UPDATE,DELETEo gatilho queUPDATE prediction set valueA = valueA +3(or -,depends on dml) WHERE ....

    • 0

relate perguntas