Substituição do cursor para iniciantes

Depende™

A capacidade de contornar um ou vários cursores dependerá do que será executado dentro desse cursor. Sem saber o que está acontecendo nele, não há como dizer. Pode ser que não haja solução alternativa e você precise fazer o processamento de linha por linha.

Abaixo estão alguns exemplos.

Não trabalhando em conjuntos

Este exemplo é o mais básico e é simplesmente o fato de que você pode consultar todo o seu conjunto de dados ou partes de seu conjunto de dados de uma vez, mas o cursor foi criado e está consultando os dados linha por linha. Os mais comuns para substituir isso são JOIN's, CROSS APPLY/ OUTER APPLYe outros.

Considere o seguinte conjunto de dados:

CREATE TABLE dbo.Lotr(LotrId int, CharacterName varchar(255), Val varchar(255));
CREATE TABLE dbo.LotrAttributes(LotrATtributeId int, LotrId int, AttrVal varchar(255));

INSERT INTO dbo.Lotr(LotrId,CharacterName,Val)
VALUES(1,'Frodo','Ring')
,(2,'Gandalf','Staff');

INSERT INTO dbo.LotrAttributes(LotrId,LotrATtributeId,AttrVal)
VALUES(1,1,'RingAttribute1')
,(1,2,'RingAttribute2')
,(1,3,'RingAttribute3')
,(2,4,'StaffAttribute1')
,(2,5,'StaffAttribute2');

Pode-se tentar encontrar cada registro e ele corresponde separadamente, fazendo um loop sobre a LotrTabela.

Cursor:

DECLARE @LotrID int
DECLARE C CURSOR FOR SELECT LotrId from dbo.Lotr;
OPEN C
FETCH NEXT FROM C INTO @LotrID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT LotrATtributeId from dbo.LotrAttributes where LotrId = @LotrID;
FETCH NEXT FROM C INTO @LotrID;
END
CLOSE C
DEALLOCATE C

Resultando em dois conjuntos de resultados

LotrATtributeId
1
2
3
LotrATtributeId
4
5

Quando isso inner joiné usado, obtemos o mesmo resultado de um conjunto de resultados.

SELECT LotrATtributeId from dbo.Lotr L
INNER JOIN dbo.LotrAttributes LA 
ON L.LotrId = LA.LotrId;

LotrATtributeId
1
2
3
4
5

Manipulação de String

Um comum é usar FOR XML PATH('')para substituir manipulações de string dentro de cursores.

Conjunto de dados

CREATE TABLE dbo.Lotr(LotrId int, CharacterName varchar(255), Val varchar(255));
CREATE TABLE dbo.LotrAttributes(LotrATtributeId int, LotrId int, AttrVal varchar(255));

INSERT INTO dbo.Lotr(LotrId,CharacterName,Val)
VALUES(1,'Frodo','Ring');

INSERT INTO dbo.LotrAttributes(LotrId,LotrATtributeId,AttrVal)
VALUES(1,1,'RingAttribute1')
,(1,2,'RingAttribute2')
,(1,3,'RingAttribute3');

Cursor duplo com manipulação de string

DECLARE @LotrId int, @CharacterName varchar(255), @Val varchar(255)
DECLARE @LotrATtributeId int, @AttrVal varchar(255)
DECLARE C CURSOR FOR
SELECT LotrId,CharacterName, Val FROM dbo.Lotr
OPEN C
FETCH NEXT FROM C INTO @LotrId,@CharacterName,@Val
WHILE @@FETCH_STATUS = 0
BEGIN

        SET @CharacterName +='|'+ @Val

        DECLARE D CURSOR FOR
        SELECT LotrATtributeId, AttrVal FROM dbo.LotrAttributes where LotrId = @LotrId
        OPEN D
        FETCH NEXT FROM D INTO @LotrATtributeId,@AttrVal
        WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @CharacterName +='['+@AttrVal+ '],'

        FETCH NEXT FROM D INTO @LotrATtributeId,@AttrVal
        END
        CLOSE D 
        DEALLOCATE D

FETCH NEXT FROM C INTO @LotrId,@CharacterName,@Val
END
CLOSE C
DEALLOCATE C
SELECT LEFT(@CharacterName,len(@charactername)-1);

Resultado

(No column name)
Frodo|Ring[RingAttribute1],[RingAttribute2],[RingAttribute3],

Removendo os cursores com FOR XML PATH('')

SELECT L.Charactername +'|'+ L.Val + (SELECT stuff((SELECT ','+QUOTENAME(AttrVal) FROM dbo.LotrAttributes LA WHERE LA.LotrId = L.LotrId FOR XML PATH('')), 1, 1, ''))
FROM
dbo.Lotr L;

*

A solução real aqui seria descobrir por que os dados são apresentados dessa maneira e alterar o application/... para não precisar dele nesse formato, armazenando-o em algum lugar, ....

Se suas mãos estão atadas, esta seria a próxima melhor coisa.

Insira os 10 principais valores em uma tabela temporária com base nos IDs em outra tabela

Dados

CREATE TABLE dbo.sometable(InsertTableId int, val varchar(255)); CREATE TABLE dbo.Top10Table(Top10TableId int, InsertTableId int, val varchar(255));

INSERT INTO dbo.sometable(InsertTableId,val)
VALUES(1,'bla')
,(2,'blabla');
INSERT INTO dbo.Top10Table(Top10TableId,InsertTableId,Val)
VALUES(1,1,'WUW')
,(2,1,'WUW')
,(3,1,'WUW');

Cursor

CREATE TABLE #Top10Values(Top10TableId int, InsertTableId int, val varchar(255))

    DECLARE @InsertTableId int;
    DECLARE C CURSOR FOR select InsertTableId from dbo.sometable;
    OPEN C
    FETCH NEXT FROM C INTO @InsertTableId;
    WHILE @@FETCH_STATUS =0
    BEGIN
    INSERT INTO #Top10Values(Top10TableId,InsertTableId,val)
    SELECT top(10) Top10TableId,InsertTableId,Val FROM dbo.Top10Table 
    where InsertTableId = @InsertTableId
    ORDER BY Top10TableId 

    FETCH NEXT FROM C INTO @InsertTableId;
    END
    CLOSE C
    DEALLOCATE C

    SELECT * FROM  #Top10Values;
    DROP TABLE #Top10Values;

Resultado

Top10TableId    InsertTableId   val
1   1   WUW
2   1   WUW
3   1   WUW

Substituindo o cursor por CROSS APPLYe umCTE

CREATE TABLE #Top10Values(Top10TableId int, InsertTableId int, val varchar(255));
;WITH CTE 
AS
(
select InsertTableId  from dbo.sometable
)

INSERT INTO #Top10Values(Top10TableId,InsertTableId,val)
SELECT  T1T.Top10TableId,T1T.InsertTableId,T1T.Val 
FROM 
CTE
CROSS APPLY (SELECT TOP (10) Top10TableId,InsertTableId,Val from dbo.Top10Table T1T
WHERE T1T.InsertTableId = CTE.InsertTableId
) T1T ;

SELECT * FROM  #Top10Values;
DROP TABLE #Top10Values;

Outros exemplos

• Um exemplo de substituição de um cursor para selecionar um conjunto dinâmico de itens por Fornecedor usando CROSS APPLY aqui .
• Um exemplo sobre o uso de funções de janelas para substituir um cursor aqui .

Às vezes não há outra escolha

Se você não puder trabalhar em conjuntos e tiver que fazer o processamento linha por linha, ainda poderá otimizar o cursor.

Uma das maiores mudanças na aceleração do cursor é adicionar LOCAL FAST_FORWARDa ele.

DECLARE C CURSOR LOCAL FAST_FORWARD FOR SELECT LotrId from dbo.Lotr

Dê uma olhada neste post de @AaronBertrand onde ele explica as possíveis diferenças de desempenho ao usar ou não usar configurações de cursor como LOCAL& FAST_FORWARD.

Não há "substituição geral" - você escondeu todo o "trabalho sujo" aqui, então é difícil dizer se há uma substituição específica neste caso. Certamente existem alguns casos específicos em que você está processando um conjunto de linhas uma linha de cada vez, seja usando um cursor, loop while ou qualquer outro processo iterativo, em que a conversão para um processo baseado em conjunto que processa todas as linhas em uma vez é muito melhor. Mas há outras coisas que só precisam ser feitas uma linha de cada vez, como executar um procedimento armazenado ou algum SQL dinâmico por linha, a mesma consulta em vários bancos de dados etc.

Cursor ou não, os problemas aos quais você está aludindo e vinculando são os mesmos se você usa declare cursor ou alguma outra estrutura de loop (veja este post ), e são irrelevantes quando o que você precisa fazer tem que ser feito uma linha por vez Tempo de qualquer maneira. Portanto, se você fornecer alguns detalhes específicos sobre o que esse cursor está fazendo, poderá obter alguns conselhos sobre como remover o cursor (ou que não pode), mas sua busca por uma abordagem mágica de eliminar todos os cursores que você pode aplicar para todos os cenários vai ser muito frustrante para você.

O conselho geral para novas pessoas que estão entrando no idioma, IMHO, deve ser sempre pensar sobre o que você precisa fazer em um conjunto de linhas , em oposição ao que você precisa fazer em cada linha em um conjunto . A diferença na linguagem é sutil, mas crucial. Se as pessoas pensarem no problema como um conjunto de dados em vez de um monte de linhas individuais, é menos provável que usem um cursor por padrão. Mas se eles vêm de diferentes tipos de programação - onde iterativo é a melhor / única maneira - além de simplesmente ensiná-los que o SQL Server não é otimizado para funcionar dessa maneira, não sei se há alguma maneira de tornar isso óbvio ou automático.

Sua pergunta ainda pede uma substituição geral, e ainda acredito que isso não existe.

Doug Lane fez uma série de vídeos chamados "T-SQL Level Up" que estão no YouTube. Parte da série explora uma abordagem geral para remover cursores mais ou menos assim:

• Remova todo o idioma do cursor (declare cursor, open, fetch, while, close, desalocate, etc) e outras declarações de variáveis
• Identifique locais onde as operações baseadas em conjunto podem ser combinadas (variáveis ​​preenchidas por um SELECTque são usadas posteriormente em um INSERTpodem ser substituídas por uma INSERT INTO...SELECTinstrução, por exemplo)
• Mova a lógica condicional ( IF...ELSE) para WHEREcláusulas, CASEinstruções, subconsultas, etc.

Como as outras ótimas respostas aqui apontaram, não há bala de prata para isso. Mas esses vídeos são, na minha opinião, uma abordagem realmente intuitiva para resolver o problema.

Doug passa por três substituições de cursor de complexidade crescente em cada parte, eu recomendo assistir (já que todo o negócio fica melhor no vídeo):

• T-SQL Level Up Capítulo 6 Substituindo Cursores Parte 1
• T-SQL Level Up Capítulo 6 Substituindo Cursores Parte 2
• T-SQL Level Up Capítulo 6 Substituindo Cursores Parte 3