Cursors kunnen eruit zien als snelkoppelingen naar een ontwikkelaar. Wanneer u een complexe taak moet uitvoeren en u de rijen in een tabel moet manipuleren, lijkt de snelste manier om één voor één door de rijen te itereren met behulp van een Transact-SQL-cursor. Immers, omdat je moet itereren door middel van datastructuren in uw eigen code op de client kant, kunt u in de verleiding om hetzelfde te doen als je te maken hebt met SQL Server data., Maar itereren door data met behulp van Transact – SQL cursors schaalt vaak niet goed, en ik hoop u te overtuigen dat het ook geen goede ontwerp-of architectuurpraktijk is.

een Cursor-ervaring

Ik breng dit naar voren omdat ik een paar maanden geleden te maken had met het Transact-SQL-script van een leverancier dat hun databasecomponent upgrade naar een nieuwe versie van de toepassing van de leverancier. Ze ontwierpen het script om een zeer grote tabel te draaien en de relevante gegevens in nieuwe tabel horizontaal op te slaan, als aaneengeschakelde strings., De leverancier wilde de prestaties verbeteren door de tabel kleiner te maken, dus besloten ze om de detailgegevens horizontaal op te slaan, als komma-gescheiden strings voor elke ouder-id. De client applicatie kan de resulterende komma-gescheiden strings sneller opvragen dan het krijgen van elk van hen als individuele rijen, en in de context, de verandering zinvol en deed het verbeteren van de prestaties van de toepassing.

Het Transact-SQL-script van de leverancier om de gegevens tijdens de upgrade te draaien duurde echter 16 uur om op een testmachine te draaien, en de klant kon zich niet meer dan een paar uur downtime voor de upgrade veroorloven., Toen we het script van de leverancier onderzochten, zagen we dat de ontwikkelaar het draaiproces in twee stappen had gecodeerd: een cursor om door alle hoofdtabel-ID ‘ s te itereren om een lege vooraf geformatteerde tabel te bouwen, en vervolgens een ander script om de strings samen te voegen, opnieuw met behulp van een cursor.

door een set-based aanpak te gebruiken, konden we de verwerkingstijd van meer dan 16 uur terugbrengen naar minder dan vijf minuten. We volgden de oorspronkelijke strategie van de ontwikkelaar, bouwden de lege tabel met behulp van SELECT statements, en we verminderden de tijd voor die stap tot minder dan twee minuten., We concateneerden vervolgens de strings met behulp van een UPDATE statement, uitgevoerd per parent id. Onze iteratie door de ouder ID ‘ s gebruikt een WHILE loop, en eindigde in minder dan drie minuten.

de onvermijdelijkheid van iteratie

veel toegangen tot databasegegevens moeten iteratief zijn om de gegevens voor te bereiden op verdere manipulatie. Zelfs de SQL Server engine itereert door gegevens wanneer het scant of voegt gegevens met behulp van de verschillende soorten joins beschikbaar voor het. Dit kunt u zien wanneer u het queryplan van SQL Server onderzoekt voor een query die veel rijen uit een grote gegevensset retourneert., Voor een join zie je meestal een geneste lus, maar soms ook een merge of hash join. Voor eenvoudiger query ‘ s kunt u een geclusterde of niet-geclusterde indexscan zien. Alleen in de gevallen waarin SQL Server een enkele rij of een kleine reeks rijen kan retourneren en de tabel een passende index heeft, zie je een zoekopdracht met behulp van een index.

denk er eens over na: Microsoft heeft de SQL Server-engine jarenlang geoptimaliseerd en afgestemd om de beschikbare gegevens zo efficiënt mogelijk te gebruiken., Stel je voor, als je de tijd had en bereid was om de energie te besteden, zou je waarschijnlijk lage toegangen kunnen schrijven naar databasebestanden die vrij efficiënt zouden zijn. Echter, het zou alleen efficiënt zijn voor de individuele taak voor je, en je zou moeten debuggen en zou kunnen hebben om het volledig te herschrijven als de omvang van uw toegang tot gegevens zou veranderen. Het zou je waarschijnlijk jaren duren om echt de code volledig geoptimaliseerd en gegeneraliseerd, en zelfs dan zou je niet dicht bij de efficiëntie van de code in de SQL Server storage engine.,

dus waar is de winst in het opnieuw uitvinden van het wiel? Het is gewoon omdat de SQL Server engine is zo goed geoptimaliseerd en debugged, dat het beter is om het te laten doen de iterating voor u en profiteren van de uitgebreide ontwikkeling en testen die al is ingebed in de database.

Als u uw gegevensverwerkingstaken nader bekijkt, denk ik dat u zult zien dat er echt heel weinig gelegenheden zijn waar cursors nodig zijn. Allereerst, vaak kunt u uw doel te bereiken door te vertrouwen op de set-based SQL commando ‘ s in Transact-SQL, en het negeren van de volgorde van de rijen van een tabel., Ten tweede zijn Transact-SQL cursors slechts een manier om rij voor rij door een tabel te herhalen. Als u elke rij van een tabel die u moet herhalen uniek kunt identificeren, kunt u een WHILE-lus gebruiken in plaats van een cursor, en mogelijk betere prestaties krijgen. Laat me je een voorbeeld geven om je te laten zien waarom.

bij het vergelijken van Iteratiestrategieën

wordt ervan uitgegaan dat u elke rij van een tabel uniek kunt identificeren omdat de tabel een unieke sleutel of een unieke groep kolommen heeft., In een WHILE-lus hoeft u alleen maar de laagste waarde van de unieke voorwaarde te vinden en vervolgens elke keer dat u itereert de volgende hoogste waarde te vinden. Hier is een voorbeeld van de SQL Server 2005 AdventureWorks sample databases productie.TransactionHistory table. Het heeft een geclusterde index op de primaire sleutel, en de WHILE lus kan elke keer in de rij zoeken.,

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

Hier is dezelfde lus met behulp van een snel vooruit cursor, wat het meest efficiënte type Transact-SQL cursor is voor het lezen van gegevens:

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

Op mijn laptop, nadat ik het een paar keer heb uitgevoerd om er zeker van te zijn dat de gegevens allemaal in de cache zitten, duurt de while lus negen seconden en de cursor 17 seconden. Uw eigen duur kan variëren. Merk op dat hoewel het voorbeeld echt niets doet met de gegevens, de WHILE loop sneller is. De cursor voegt blijkbaar meer overhead toe.,

de cursor heeft ook extra commando ‘ s nodig, waardoor de code er rommelig uitziet. Zonder in de details van hoe cursors werken, die Microsoft volledig uitlegt in Microsoft SQL Server 2005 Books Online, merk op dat wanneer u een WHILE loop te gebruiken, er geen vereiste is om iets te declareren, openen, sluiten en deallocate. De logica is eenvoudiger, en u kunt zelfs rijen vrij te werken langs de weg. Om de rijen met de cursor bij te werken, moet u het cursortype wijzigen.

zelfs een WHILE-lus voegt de overhead van iteratie toe., U kunt in staat zijn om het te vervangen door een set-based SELECT commando, of eventuele updates die u wilt doen in uw loop te vervangen door de set-based UPDATE commando, en laat de iteratie aan de SQL Server engine. Een eenvoudig SELECT statement om dezelfde gegevens te krijgen als onze cursor en WHILE lus hierboven duurt minder dan 3 seconden, en het Retourneert de rijen naar de client, wat meer werk is dan de twee vorige lussen doen.

SELECT *FROM Production.TransactionHistory

deze selectie is gebaseerd op SQL Server om door de gegevens te itereren, en is veruit de snelste van de drie methoden van toegang tot gegevens die we hebben bekeken.,

van zakken naar Sets

soms lijken cursors noodzakelijk. Wanneer u gewoon door databasegegevens moet itereren, rij voor rij, in hun fysieke volgorde, werkt soms alleen een cursor. Dit gebeurt meestal wanneer u dubbele rijen hebt en er geen manier is om een bepaalde rij in de tabel uniek te identificeren. Deze tabellen zijn zakken, geen sets, van gegevens, zoals een ‘ zak ‘ geen dubbele waarden elimineert, zoals een set doet.

dergelijke zakken met gegevens treden meestal op wanneer u gegevens importeert uit een externe bron en u kunt de gegevens niet volledig vertrouwen., Bijvoorbeeld, als onze AdventureWorks transaction history tabel geen groep kolommen had die u uniek kon noemen, en / of dubbele rijen had, zou u kunnen denken dat u een cursor moet gebruiken.

U kunt echter altijd een zak met rijen omzetten in een genormaliseerde tabel. Zelfs als u dubbele rijen in een tabel hebt, of geen reeks kolommen waarop u kunt vertrouwen voor uniciteit, kunt u een identiteitskolom aan de tabel toevoegen en de identiteit seed om te beginnen met nummeren met 1. Dit voegt een unieke sleutel toe aan de tabel, zodat u een WHILE lus kunt gebruiken in plaats van een cursor., Zodra u een unieke sleutel hebt, kunt u duplicaten verwijderen met behulp van de opdracht Transact-SQL set-based UPDATE.

de logische API naar databasegegevens

met behulp van set-base operaties is beter dan de gegevens zelf itereren op ten minste twee manieren.

ten eerste, set-based SQL commando ’s zijn efficiënter omdat u SQL Server’ s sterk geoptimaliseerde engine gebruikt om uw iteratie uit te voeren. Als u zelf door gegevens heen itereert, gebruikt u de SQL Server storage engine niet optimaal. In plaats daarvan, je peppering het met commando ‘ s om slechts een enkele rij op te halen op een moment., Elke keer dat u een enkele rij aanvraagt, moet uw opdracht door de SQL Server optimizer gaan voordat deze bij de storage engine kan komen, en u gebruikt uiteindelijk de geoptimaliseerde code van de SQL Server storage engine niet. Als je jezelf itereerde, vertrouw je ook op externe fysieke informatie over de tabel, namelijk de volgorde van de rijen, bij het verwerken van de gegevens. De set-base Transact – SQL SELECT -, UPDATE-en DELETE-opdrachten geven u een manier om de volgorde van de rijen te negeren en ze gewoon te beïnvloeden op basis van de kenmerken van de gegevens-en ze zijn sneller.,

ten tweede, set-based commando ‘ s zijn logischer omdat het denken over data in sets abstraheert je weg van vreemde details die meer bezig zijn met hoe de data daadwerkelijk wordt geordend. In feite, set-based commando ‘ s zoals SELECT, UPDATE, en DELETE, wanneer toegepast op tabellen direct en niet in een cursor of WHILE lus, brengen u dichter logisch bij uw gegevens, juist omdat u de volgorde van de gegevens kunt negeren.,

Hier is een andere manier om na te denken over dit tweede punt-net zoals opgeslagen procedures de meest natuurlijke API zijn voor applicaties om programmatisch met SQL Server te communiceren, zo zijn set-based SQL commando ‘ s de juiste API voor toegang tot relationele data. Opgeslagen procedures ontkoppelen uw toepassing van de database internals, en ze zijn efficiënter dan ad hoc queries. Ook de set-base SQL commando ‘ s in Transact-SQL geven u een logische interface naar uw relationele gegevens, en ze zijn efficiënter omdat u vertrouwt op de SQL Server storage engine voor het itereren door gegevens.,

De bottom line is niet dat itereren door data slecht is. Eigenlijk is het vaak onvermijdelijk. In plaats daarvan, het punt is, laat de storage engine doen voor u en vertrouwen in plaats daarvan op de logische interface van de set-gebaseerde Transact-SQL commando ‘ s. Ik denk dat je weinig of geen situaties zult vinden waar je eigenlijk een Transact-SQL cursor moet gebruiken.