cursores podem parecer atalhos para um desenvolvedor. Quando você tem uma tarefa complexa para executar e você precisa manipular as linhas em uma tabela, a maneira mais rápida pode parecer iterar através das linhas uma a uma usando um cursor Transact-SQL. Afinal, uma vez que você tem que iterar através de estruturas de dados em seu próprio código no lado cliente, você pode ser tentado a fazer o mesmo quando você está lidando com dados do servidor SQL., Mas a iteração através de dados usando Cursores Transact-SQL muitas vezes não tem uma boa escala, e espero convencê-lo de que também não é um bom projeto ou prática arquitetônica.

a Cursor Experience

i bring this up because a few months ago, I had to deal with a vendor’s Transact-SQL script that upgraded their database component to a new version of the vendor’s application. Eles projetaram o script para rodar uma tabela muito grande e armazenar os dados relevantes em nova tabela horizontalmente, como cadeias concatenadas., O fornecedor queria melhorar o desempenho, tornando a tabela menor, então eles decidiram armazenar os dados de detalhes horizontalmente, como strings delimitados por vírgulas para cada ID Pai. A aplicação cliente poderia consultar as cadeias delimitadas por vírgulas resultantes mais rapidamente do que obter cada uma delas como linhas individuais, e no contexto, a mudança fez sentido e melhorou o desempenho da aplicação.

no entanto, o script Transact-SQL do fornecedor para rodar os dados durante a atualização levou 16 horas para executar em uma máquina de teste, e o cliente não poderia ter mais do que algumas horas de parada para a atualização., Quando examinamos o script do Fornecedor, vimos que o desenvolvedor codificou o processo de pivô em dois passos: um cursor para iterar através de todos os ids da tabela pai para construir uma tabela pré-formatada em branco, e então outro script para concatenar as strings, novamente usando um cursor. usando uma abordagem baseada em set, conseguimos reduzir o tempo de processamento de 16 mais horas para menos de cinco minutos. Seguimos a estratégia original do desenvolvedor, construindo a tabela em branco usando declarações selecionadas, e reduzimos o tempo para esse passo para menos de dois minutos., Em seguida, concatenamos as strings usando uma declaração de atualização, executado por ID Pai. A nossa iteração através das identificações dos pais usou um laço WHILE, e terminou em menos de três minutos.

a inevitabilidade da iteração

muitos acessos aos dados da base de dados devem ser iterativos de alguma forma, a fim de preparar os dados para posterior manipulação. Mesmo o motor do servidor SQL itera através de dados quando ele digitaliza ou junta dados usando os vários tipos de junções disponíveis para ele. Você pode ver isso quando você examinar o plano de pesquisa do servidor SQL para uma consulta que retorna muitas linhas de um conjunto de dados grande., Para uma junção, você verá mais comumente um loop aninhado, mas às vezes também uma junção merge ou hash. Para consultas mais simples, você pode ver uma varredura de índice agrupada ou não agrupada. É apenas nos casos em que o servidor SQL pode retornar uma única linha ou pequeno conjunto de linhas, e a tabela tem um índice apropriado, que você verá uma busca usando um índice.

pense nisso: a Microsoft tem otimizado e sintonizado o motor SQL Server por anos para iterar através de seus dados disponíveis o mais eficientemente possível., Imagine, se você tivesse tempo e estivesse disposto a gastar a energia, você provavelmente poderia escrever acessos de baixo nível para arquivos de dados de banco de dados que seriam muito eficientes. No entanto, seria eficiente apenas para a tarefa individual à sua frente, e você teria que depurar e poderia ter que reescrevê-la completamente se o escopo de seu acesso de dados fosse mudar. Provavelmente levaria anos para realmente obter o código totalmente otimizado e generalizado, e mesmo assim você não estaria perto da eficiência do código dentro do motor de armazenamento do servidor SQL.,então, onde está o ganho em reinventar a roda? É só porque o motor do servidor SQL é tão bem otimizado e depurado, que é melhor deixá-lo fazer a iteração para você e tirar proveito do desenvolvimento extensivo e testes que já está incorporado no banco de dados.

Se você olhar para as suas tarefas de processamento de dados mais de perto, eu acho que você vai descobrir que existem realmente muito poucas ocasiões em que cursores são necessários. Em primeiro lugar, muitas vezes você pode realizar o seu objetivo, contando com os comandos SQL baseados em set no Transact-SQL, e ignorando a ordem das linhas de uma tabela., Segundo, Cursores Transact-SQL são apenas uma maneira de iterar através de uma tabela linha por linha. Se você pode identificar exclusivamente cada linha de uma tabela que você deve iterar, você pode usar um laço WHILE em vez de um cursor, e potencialmente ganhar melhor desempenho. Deixa-me mostrar-te um exemplo para te mostrar porquê.

comparando estratégias de iteração

Assume que pode identificar de forma única cada linha de uma tabela porque a tabela tem uma chave única ou um grupo único de colunas., Em um loop WHILE, tudo que você precisa fazer é encontrar o menor valor da condição única, e então encontrar o próximo valor mais alto cada vez que você iterar. Aqui está um exemplo do SQL Server 2005 AdventureWorks sample databases Production.TransactionHistory table. Ele tem um índice agrupado na chave primária, e o laço WHILE pode procurar na linha de cada vez.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Aqui o mesmo loop usando um cursor para a FRENTE RÁPIDO, que é o tipo mais eficiente de Transact-SQL cursor para só de leitura de dados:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

No meu laptop, depois eu corri algumas vezes para se certificar de que os dados em cache, o loop WHILE, leva nove segundos, e o cursor toma a 17 segundos. A sua duração pode variar. Note que mesmo que o exemplo realmente não faz nada com os dados, o laço WHILE é mais rápido. O cursor evidentemente adiciona mais acima.,

o cursor também necessita de comandos adicionais, que fazem o código parecer confuso. Sem entrar nos detalhes de como o cursors funciona, o que a Microsoft explica totalmente no Microsoft SQL Server 2005 Livros Online, observe que quando você usa um laço WHILE, não há nenhum requisito para declarar, abrir, fechar e desallocate qualquer coisa. A lógica é mais simples, e você pode até atualizar as linhas livremente ao longo do caminho. Para actualizar as linhas com o cursor, terá de alterar o tipo de cursor.

mesmo um laço WHILE adiciona a sobrecarga da iteração., Você pode ser capaz de substituí-lo por um comando set-based SELECT, ou substituir quaisquer atualizações que você queria fazer em seu loop com o comando set-based UPDATE, e deixar a iteração para o motor de servidor SQL. Uma simples instrução selecione para obter os mesmos dados que o nosso cursor e enquanto o loop acima leva menos de 3 segundos, e retorna as linhas para o cliente, que é mais trabalho do que os dois loops anteriores fazem.

SELECT *FROM Production.TransactionHistory

Esta opção depende do servidor SQL para iterar através dos dados, e é de longe o mais rápido dos três métodos de acesso de dados que vimos.,

de sacos a Conjuntos

por vezes, os cursores podem parecer necessários. Quando você simplesmente deve iterar através de dados de banco de dados, linha por linha, em sua ordem física, às vezes apenas um cursor irá funcionar. Isso mais comumente acontece quando você tem linhas duplicadas e não há maneira de identificar uma dada linha na tabela. Estas tabelas são sacos, não conjuntos, de dados, como um ‘saco’ não elimina valores duplicados, como um conjunto faz.esses sacos de dados geralmente ocorrem quando você importa dados de uma fonte externa e você não pode confiar completamente nos dados., Por exemplo, se a nossa tabela de histórico de transacções do AdventureWorks não tivesse um grupo de colunas a que pudesse chamar unique e/ou tivesse linhas duplicadas, poderá pensar que terá de usar um cursor.

no entanto, você pode sempre transformar um saco de linhas em uma tabela normalizada. Mesmo que você tenha linhas duplicadas em uma tabela, ou nenhum conjunto de colunas que você pode confiar para a unicidade, você pode adicionar uma coluna de identidade para a tabela e semear a identidade para começar a numeração com 1. Isto adiciona uma chave única à tabela, permitindo-lhe usar um ciclo WHILE em vez de um cursor., Logo que tenha uma chave única, poderá remover duplicados usando o comando de actualização baseado no conjunto Transact-SQL.

a API lógica aos dados da Base de dados

Usando operações de base de Conjuntos é melhor do que iterar os próprios dados de pelo menos duas maneiras.

Em Primeiro Lugar, os comandos SQL baseados em set são mais eficientes porque você está usando o motor altamente otimizado do servidor SQL para fazer a sua iteração. Se você mesmo iterar através de dados, você não está usando o motor de armazenamento do servidor SQL de forma otimizada. Em vez disso, você está peppering-lo com comandos para recuperar apenas uma única linha de cada vez., Cada vez que você pede uma única linha, seu comando deve passar pelo otimizador do servidor SQL antes que ele possa chegar ao motor de armazenamento, e você acaba não usando o código otimizado do motor de armazenamento do servidor SQL. Se você se iterou, você também está confiando em informações físicas externas sobre a tabela, nomeadamente a ordem das linhas, ao processar os dados. Os comandos set-base Transact-SQL SELECT, UPDATE e DELETE lhe dão uma maneira de ignorar a ordem das linhas e apenas afetá-las com base nas características dos dados-e eles são mais rápidos.,

Em segundo lugar, os comandos baseados em conjuntos são mais lógicos porque pensar em dados em conjuntos abstrai-o de detalhes estranhos que estão mais preocupados com a forma como os dados são realmente ordenados. Na verdade, comandos baseados em set, como selecionar, Atualizar e excluir, quando aplicados em tabelas diretamente e não em um cursor ou ciclo, trazê-lo mais perto logicamente de seus dados, precisamente porque você pode ignorar a ordem dos dados.,

Aqui está outra maneira de pensar sobre este segundo ponto-assim como os procedimentos armazenados são a API mais natural para aplicações para interface com o servidor SQL programaticamente, então os comandos SQL baseados em conjunto são a API apropriada para acessar dados relacionais. Os procedimentos armazenados dissociam a sua aplicação dos internos da base de dados, e são mais eficientes do que as consultas ad hoc. Da mesma forma, os comandos SQL de base de conjunto dentro do Transact-SQL dão-lhe uma interface lógica para os seus dados relacionais, e eles são mais eficientes porque você confia no motor de armazenamento do servidor SQL para iterar através de dados.,

a linha de fundo não é que iterar através de dados é ruim. Na verdade, muitas vezes é inevitável. Rather, the point is, let the storage engine do it for you and rely instead on the logical interface of the set-based Transact-SQL commands. Eu acho que você vai encontrar algumas situações em que você realmente deve usar um cursor Transact-SQL.