O SQL Server Loop através de Linhas da Tabela, sem Cursor

Por: Daniel Farina | Atualizado em: 2019-09-12 | Comentários (2) | Relacionados: Mais > T-SQL

Problema

Youhave sempre ouvi dizer que você deve evitar cursores inyour código T-SQL como um SQL Server melhores práticas, becausecursors são prejudiciais para performanceand, por vezes, causa problemas. Mas, às vezes, há uma necessidade de percorrer a data uma linha de cada vez, então Nesta Dica vamos olhar para uma comparação de como fazer um laço sem usar cursor.,

solução

todos nós sabemos que o servidor SQL, como qualquer banco de dados relacional permite ao usuário operações baseadas em conjuntos de toperform. Além disso, como muitos vendedores de banco de dados fazem, SQL Server inclui uma extensão processual que é a linguagem T-SQL. Ele adiciona construções encontradas em linguagens processurais permitindo um código mais simples para desenvolvedores. Estas construções foram acrescentadas por uma razão e, por vezes, esta é a única abordagem à tarefa em questão.,

usando um ciclo While em vez de cursores no servidor SQL

Se alguma vez trabalhou com cursores, poderá achar este título um pouco confuso, porque afinal de contas, Cursores usa enquanto constrói para iterar entre linhas. Mas quero mostrar-lhe que em algumas circunstâncias, quando usamos um cursor para passar um conjunto de linhas, podemos mudá-lo para um loop while. Em tais casos, o único desafio será escolher uma condição de saída adequada.,

Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server

Not everything is wrong with cursors, they also have some advantagesover other looping techniques.

• cursores são atualizáveis: quando você cria um cursor, você usa uma consulta para defini-lo usando a instrução de declaração de CURSOR. Se usar a opção Actualizar na instrução de cursorcreation, poderá actualizar as colunas dentro do cursor.,
• poderá mover – se para a frente e para trás num cursor: se usar a opção de posicionamento na declaração de CURSOR, poderá navegar através dos registos de cursor em ambas as direcções com as opções de obtenção Em Primeiro, Último, anterior, seguinte, relativo e auto. Tenha em mente que a opção SCROLL é incompatível com as opções Forward_ Only e FAST_FORWARD.
• cursores podem ser passados para procedimentos armazenados: Se você usar a opção GLOBAL para criar um cursor, ele pode ser usado em qualquer procedimento armazenado ou em lote executado na mesma conexão. Isto permite-lhe usar cursores em procedimentos armazenados aninhados.,
• Cursores têm muitas opções diferentes: com cursores você tem a oportunidade de usar diferentes opções que afetam como eles se comportam em relação ao bloqueio.
• Cursores não precisam de uma condição: ao usar cursores, você está handlinga conjunto de linhas como um registro. Isto permite-lhe passar através do cursor sem a necessidade de ter uma condição booleana. Por exemplo, você pode criar um cursor com o nome das bases de dados que residem em uma instância de servidor SQL sem a necessidade de uma chave substituta para funcionar como uma condição de teste como em um laço WHILE.,

Existem também alguns aspectos negativos que deve estar ciente ao usar cursores em vez de outras opções de looping.

• Se usar cursores globais no seu código, está a correr o risco de erros Faciais devido ao facto de um cursor estar a ser fechado por algum procedimento armazenado, aninhado no seu código.
• normalmente os cursores têm menos desempenho do que um laço equivalente usando um WHILEloop ou CTE.

prós e contras de usar um laço While para iterar através de linhas de tabela no servidor SQL

também há benefícios de usar um laço WHILE em comparação com um cursor.,

• Enquanto os laços são mais rápidos que os cursores.enquanto os laços usam menos fechaduras que os cursores.
• menor utilização do Tempdb: enquanto os loops não criam uma cópia dos dados intempdb como um cursor. Lembre-se que os cursores, dependendo das opções que você usa para criá-los, podem fazer com que as tabelas de temperatura sejam criadas.

a próxima lista detalha os aspectos negativos dos ciclos de WHILE.

• mover para a frente ou para trás é complexo: para mover para a frente ou para trás em aloop você precisa mudar dinamicamente a condição de iteração dentro do loop.,Isso requer cuidado extra; caso contrário, você pode acabar em um loop infinito.
• O risco de um loop infinito: em Comparação com um cursor, você não dominam conjunto fixo de dados para loop (i.e. os dados retornados pela ESCOLHA statementin a declaração de cursor), em vez disso, quando utilizar um loop WHILE, você tem de definir uma boundarywith uma expressão que é avaliada como true ou false.

criação do Ambiente de Teste para Cursores e Loops

Para testar isso, vou usar uma tabela com uma coluna de identidade (CursorTestID),uma coluna varchar (Filler) e uma coluna bigint (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO 

a ideia é percorrer as linhas da tabela ordenadas pela coluna CursorTestID e actualizar a coluna RunningTotal com a soma da coluna CursorTestID e o valor da coluna RunningTotal da linha anterior.

mas antes de iniciar, primeiro precisamos gerar algumas linhas de teste com o próximo script.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

no programa acima, irá reparar que só usei uma única declaração de inserção e aproveitei o separador do lote (o comando GO 500000) como um atalho para executar esta declaração de inserção 500000 vezes. Você pode ler mais sobre este método para repetir a execução em lote Nesta Dica: executando um lote T-SQL várias vezes usando GO.

exemplo de um Cursor básico para percorrer as linhas da tabela no servidor SQL

vamos criar um cursor para preencher a coluna RunningTotal. Repare no nextscript que eu declarei o cursor com a opção FAST_FORWARD., Isto é feito para melhorar o desempenho do cursor porque de acordo com o argumento Microsoft theFAST_FORWARD “especifica um FORWARD_ONLY, read_only cursor com performanceoptimizações ativadas”. Em outras palavras, estamos instruindo o servidor SQL para usea ler apenas o cursor que só pode avançar e ser desenrolado da primeira para a última linha.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

A próxima imagem é uma captura de tela mostrando a execução do script acima.Como você pode ver, ele demorou três minutos e cinco segundos para atualizar as 500.000 rowsof nossa tabela de teste.,

exemplo de um ciclo de While básico para circular através de linhas de tabela no servidor SQL

Agora vou reescrever o programa anterior evitando o uso de um cursor. Você vai notar que ele contém um laço While que é quase idêntico ao que está no script do processador. Isto é, como eu disse anteriormente, porque mesmo quando se trabalha com cursorsyou precisa usar uma estrutura de controle iterativo.

a próxima imagem é uma captura de tela da execução do script acima. É tempo de correr o ciclo while do que o cursor.,

outro exemplo do Cursor do servidor SQL

tomemos por exemplo o cursor no tipStandardize SQL Server data com a função de pesquisa e substituição de texto. Uma palavra de aconselhamento, a fim de executar este código, você deve seguir os passos na ponta para criar o ambiente de teste.

E aqui está o código do cursor:

Se dissecarmos este código, podemos ver que existe um cursor que passa pelos produtos da tabela que eu copiei abaixo.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products 

SQL Server Cursor exemplo convertido para um ciclo While

a fim de substituir este cursor por um ciclo WHILE, precisamos criar uma temporarytable para implementar uma tabela de Registo. Para todos vocês que não sabem o que é um tallytable, podemos defini-lo como uma tabela que contém um par de colunas consistindo de uma chave e seu valor. Em nosso caso particular, usaremos um integerkey sequencial a partir de 1, para que possamos usá-lo como iterador. Esta chave será associada a um produto da tabela de produtos.,

no início, uma vez que a tabela de produtos tem a chave do produto definida como uma identityyyyyyyou pode ser tentado a contornar esta etapa, mas você tem que considerar que em uma linha casea real poderia ter sido excluído, portanto você não será capaz de usar a identitycolumn como um iterador. Além disso, uma linha pode ser apagada enquanto estamos executando o nosso código e pode levar a erros de execução. Para evitar isso, vamos adicionar um bloqueio aTRY-CATCHblock. Vou aprofundar este assunto.

Antes de iniciar o ciclo WHILE, precisamos definir a sua condição de início e paragem., Para esta matéria eu adicionei duas novas variáveis inteiras chamadas @Iterator e @MaxIterator.A variável @MaxIterator é usada para manter o número de itens no #Tallytabable e nós definimos seu valor apenas uma vez antes de iniciar o loop. O @ Iterator variable é inicializado para 1, como o definimos como o número inicial na sequência e vamos aumentar o seu valor em cada iteração.

próximas etapas

• Você é novo em Cursores e precisa de alguma prática? No nextttttip você encontrará uma explicação, um exemplo de cursor fácil de entender e leituras morerecomendadas:exemplo de Cursor do servidor SQL.,
• Se quiser converter os cursores existentes no seu código para definir consultas baseadas, dê uma olhada neste servidor chapterSQL Convert Cursor Set baseado no Tutorial de Design de Melhores Práticas do servidor de theSQL.
• você precisa de outro exemplo sobre o uso de um laço While? Dê uma olhada nesta dica que lhe mostrará como dividir declarações DML em lotes:otimize grandes servidores SQL, atualize e exclua processos usando lotes.
• No caso de não saber usar o TRY…CATCH exceptionhandling, take a look at this tip: SQL Server Try and Catch Exception Handling.,
• Mantenha-se sintonizado com o servidor de theSQL T-SQL Tipscategory para obter mais ideias de codificação.

Última actualização: 2019-09-12

Sobre o autor

Daniel Farina nasceu em Buenos Aires, Argentina. Auto-educado, desde a infância ele mostrou uma paixão pela aprendizagem.
View all my tips

Related Resources

• More Database Developer Tips…