Intro

Em bancos de dados relacionais, as operações são feitas em um conjunto de linhas. Por exemplo, uma instrução SELECT retorna um conjunto de linhas que é chamado de conjunto de resultados. Às vezes, a lógica do aplicativo precisa trabalhar com uma linha por vez, em vez de todo o conjunto de resultados de uma vez. No T-SQL, uma maneira de fazer isso é usando um CURSOR.

Se você possui habilidades de programação, provavelmente usaria um loop como FOR ou WHILE para iterar um item por vez, fazer algo com os dados e o trabalho estão feitos. No T-SQL, um CURSOR é uma abordagem semelhante e pode ser preferido porque segue a mesma lógica. Mas fique atento, siga este caminho e problemas podem surgir.

Há alguns casos, quando usar o CURSOR não causa tanta confusão, mas geralmente eles devem ser evitados. Abaixo, mostraremos alguns exemplos em que o uso de um CURSOR cria problemas de desempenho e veremos que o mesmo trabalho pode ser feito de muitas outras maneiras.

Para o propósito desta demonstração, usaremos o banco de dados AdventureWorks2012, e digamos que queremos obter alguns dados de. tabela, para cada produto que requer menos de um dia para ser fabricado, ou seja, da tabela.

Um exemplo de cursor

Vamos começar usando um CURSOR e escrever a seguinte sintaxe:

Após uma breve pausa para o café , a consulta terminou de ser executada, retornando 833 linhas no tempo mostrado abaixo. É importante mencionar que as sintaxes escolhidas acima são apenas para fins de demonstração e não fiz nenhum ajuste de índice para acelerar as coisas.

Na execução do cursor, temos duas etapas. Etapa um, o posicionamento, quando o cursor define sua posição para uma linha do conjunto de resultados. Etapa dois, a recuperação, quando obtém os dados daquela linha específica em uma operação chamada FETCH.

Em nosso exemplo, o cursor define sua posição para a primeira linha retornada pelo primeiro SELECT e busca o valor ProductID que corresponde à condição WHERE na variável @id. Em seguida, o segundo SELECT usa o valor da variável para obter os dados. e a próxima linha é buscada. Essas operações são repetidas até que não haja mais linhas para trabalhar.

Finalmente, a sintaxe CLOSE libera o conjunto de resultados atual e remove os bloqueios das linhas usadas pelo cursor, e DEALLOCATE remove a referência do cursor.

Nossas tabelas de demonstração são relativamente pequenas contendo aproximadamente 1.000 e 500 linhas. Se tivéssemos que percorrer tabelas com milhões de linhas, isso duraria uma quantidade considerável de tempo e os resultados não nos agradariam.

Vamos dar ao nosso cursor outra chance e remover o comentário da linha –LOCAL STATIC. Há muitos argumentos que podemos usar em uma definição de cursor, mais sobre isso neste link Argumentos CURSOR, mas por agora vamos nos concentrar no que essas duas palavras significam.

Quando especificamos a palavra-chave LOCAL, o escopo do cursor é local para o lote no qual foi criado e é válido apenas neste escopo. Depois que o lote termina de ser executado, o cursor é desalocado automaticamente. Além disso, o cursor pode ser referenciado por um procedimento armazenado, gatilho ou por uma variável de cursor local em um lote.

A palavra-chave STATIC faz uma cópia temporária dos dados usados pelo cursor em tempdb em uma tabela temporária . Aqui temos algumas pegadinhas. Um cursor ESTÁTICO é somente leitura e também é conhecido como cursor de instantâneo porque só funciona com os dados a partir do momento em que foi aberto, o que significa que não exibirá nenhuma alteração feita no banco de dados no conjunto de dados usado pelo cursor.Basicamente, nenhuma atualização, exclusão ou inserção feita depois que o cursor foi aberto será visível no conjunto de resultados do cursor, a menos que fechemos e reabramos o cursor.

Esteja ciente disso antes de usar esses argumentos e verifique se eles correspondem às suas necessidades. Mas vamos ver se nosso cursor é mais rápido. Abaixo temos os resultados:

12 segundos são muito melhores do que 4 minutos e 47 segundos, mas tenha em mente que restrições explicadas acima.

Se executarmos a sintaxe usando este argumento LOCAL READ_ONLY FORWARD_ONLY, obteremos os mesmos resultados. O cursor READ_ONLY FORWARD_ONLY pode ser rolado apenas da primeira linha para a última. Se a palavra-chave STATIC estiver faltando, o cursor é dinâmico e usa um plano dinâmico, se disponível. Mas há casos em que um plano dinâmico é pior do que um estático.

O que acontece quando removemos o comentário –LOCAL FAST_FORWARD?

FAST_FORWARD é equivalente ao cursor READ_ONLY e FORWARD_ONLY, mas tem a capacidade de escolher o melhor plano de um estático ou dinâmico.

LOCAL FAST_FORWARD parece ser a melhor escolha devido à sua flexibilidade para escolha entre um plano estático ou dinâmico, mas FORWARD_ONLY também faz o trabalho muito bem. Embora tenhamos obtido o resultado em uma média de 12 segundos usando ambos os métodos, esses argumentos devem ser devidamente testados antes de escolher um deles.

Existem muitas maneiras de obter o mesmo resultado, muito mais rápido e com menos impacto no desempenho.

Alternativa do cursor

Um método é usar JOIN e, como podemos ver a seguir, os resultados são consideravelmente melhores.

Primeiro, temos que habilitar o tempo de estatísticas para medir o tempo de execução do SQL Server e fazer um INNER JOIN entre duas tabelas,. e . na coluna ProductID. Depois de clicar no botão EXECUTAR, produzimos os mesmos resultados em 330 ms, em comparação com 04:47 tempo do método do cursor, e temos um sorriso em nosso rosto.

Não precisamos iterar em cada linha para obter o que precisamos, não temos mais loops, nem cláusula while, nem iter em vez disso, estamos trabalhando com conjuntos de dados, obtendo o que queremos mais rápido e escrevendo menos código.

Um uso apropriado do cursor

Agora que vimos quanto dano um cursor pode fazer, vamos ver um exemplo onde podemos fazer uso dele.

Vamos supor que queremos selecionar o tamanho e o número de linhas para apenas algumas tabelas de um banco de dados. Para conseguir isso, obteremos todos os nomes de tabela com base nos critérios de information_schema.tables e, usando um CURSOR, faremos um loop por cada nome de tabela e executaremos o procedimento armazenado sp_spaceused passando um nome de tabela por vez para obter as informações de que precisamos .

Usaremos o mesmo banco de dados AdventureWorks2012 e obteremos todas as tabelas do esquema de vendas que contém o nome ‘Vendas’. Para cada nome de tabela retornado, queremos ver todas as informações de information_schema.tables.

Abaixo, temos a sintaxe T-SQL e os resultados obtidos:

Este é um método em que o CURSOR é útil ao iterar alguns dados, uma linha por vez, e obter o resultado necessário. Nesse caso específico, o cursor executa o trabalho sem ter implicações no desempenho e é fácil de usar.

Conclusões

Aí está. Mostramos alguns exemplos com o bom, o ruim e o feio ao usar cursores. Na maioria dos casos, podemos usar JOINS, mesmo cláusulas WHILE, SSIS pacotes ou outros métodos alternativos para obter o mesmo resultado mais rápido, com menos impacto na saída de desempenho e ainda escrevendo menos linhas de sintaxe.

Quando nós estão lidando com ambiente OLTP e grandes conjuntos de dados para processar, a palavra ‘CURSOR’ não deve ser falada.

Em SQL, é uma boa prática pensar em fazer operações em conjuntos de dados, em vez de pensar de forma programática, usando iterações ou loops, pois esse tipo de abordagem não é recomendada nem pretendida para esse uso. Tentar usar loops como FOR ou FOREACH de linguagens de programação e associar essa lógica a operações SQL é um obstáculo para obter a solução certa para nossas necessidades. Temos que pensar em operações baseadas em conjunto, em vez de uma linha de cada vez para obter os dados de que precisamos.

Os cursores podem ser usados em alguns aplicativos para operações serializadas como mostrado no exemplo acima, mas geralmente eles devem ser evitados porque trazem um impacto negativo no desempenho, especialmente ao operar em grandes conjuntos de dados.

• Autor
• Postagens recentes

Sou um DBA da Yazaki Component Technology com 3 anos de experiência em SQL Server.
Sou formado em Engenharia da Computação e também possuo habilidades em .NET, C # e Windows Server. Desenvolvi experiência em ajuste e monitoramento de desempenho, desenvolvimento de T-SQL, estratégia de backup, administração e configuração de bancos de dados.
Sou apaixonado por coisas de TI, videogames, bons filmes e música electro-house me ajuda a trabalhar quando o escritório está barulhento.
Ver todas as postagens de Sergiu Onet

Últimos posts de Sergiu Onet (ver todos)

• Usando cursores do SQL Server – Vantagens e desvantagens – 23 de março de 2016