cursoarele pot arata ca comenzi rapide pentru un dezvoltator. Când aveți un loc de muncă complex pentru a efectua și aveți nevoie pentru a manipula rândurile într-un tabel, cel mai rapid mod poate părea să itera prin rândurile unul câte unul folosind un cursor Transact-SQL. La urma urmei, din moment ce trebuie să iterați prin structurile de date din propriul cod din partea clientului, este posibil să fiți tentat să faceți același lucru atunci când aveți de-a face cu datele SQL Server., Dar iterarea prin date folosind cursoare Transact-SQL de multe ori nu se scalează bine și sper să vă conving că nu este, de asemenea, un design bun sau o practică arhitecturală.

o experiență Cursor

am adus asta pentru că acum câteva luni, am avut de a face cu script-ul Transact-SQL un furnizor care a actualizat componenta lor de baze de date la o nouă versiune a cererii furnizorului. Ei au proiectat script-ul pentru a pivota un tabel foarte mare și stoca datele relevante în tabel nou orizontal, ca șiruri concatenate., Vânzătorul a dorit să îmbunătățească performanța făcând Tabelul mai mic, așa că au decis să stocheze datele detaliate orizontal, ca șiruri delimitate prin virgulă pentru fiecare id părinte. Aplicația client ar putea interoga șirurile delimitate prin virgulă rezultate mai repede decât obținerea fiecăruia ca rânduri individuale, iar în context, schimbarea a avut sens și a îmbunătățit performanța aplicației.

cu toate acestea, scriptul Transact-SQL al furnizorului pentru a pivota datele în timpul actualizării a durat 16 ore pentru a rula pe o mașină de testare, iar clientul nu și-a putut permite mai mult de câteva ore de nefuncționare pentru actualizare., Când am examinat scriptul vânzătorului, am văzut că dezvoltatorul a codat procesul de pivotare în doi pași: un cursor pentru a itera prin toate ID-urile tabelului părinte pentru a construi un tabel pre-formatat gol și apoi un alt script pentru a concatena șirurile, folosind din nou un cursor. folosind o abordare bazată pe set, am reușit să reducem timpul de procesare de la 16 ore plus la mai puțin de cinci minute. Am urmat strategia inițială a dezvoltatorului, construind tabelul gol folosind instrucțiuni selectate și am redus timpul pentru acel pas la mai puțin de două minute., Apoi am concatenat șirurile folosind o declarație de actualizare, executată pe ID-ul părinte. Iterația noastră prin ID-urile părinte a folosit o buclă WHILE și a terminat în mai puțin de trei minute.

inevitabilitatea iterației

multe accesări la datele bazei de date trebuie să fie iterative într-o anumită manieră pentru a pregăti datele pentru manipulare ulterioară. Chiar și motorul SQL Server iterează prin date atunci când scanează sau se alătură Datelor folosind diferitele tipuri de Join-uri disponibile. Puteți vedea acest lucru atunci când examinați planul de interogare SQL Server pentru o interogare care returnează mai multe rânduri dintr-un set mare de date., Pentru un join, veți vedea cel mai frecvent o buclă imbricate, dar, uneori, de asemenea, o îmbinare sau hash se alăture. Pentru interogări mai simple, este posibil să vedeți o Scanare index cluster sau non-cluster. Este numai în cazurile în care SQL Server poate returna un singur rând sau un set mic de rânduri, iar tabelul are un index adecvat, că veți vedea o caută folosind un index.gândiți-vă: Microsoft a optimizat și reglat motorul SQL Server de ani de zile pentru a itera prin datele disponibile cât mai eficient posibil., Imaginați-vă, dacă ați avea timp și ați fi dispuși să cheltuiți energia, probabil ați putea scrie accesări de nivel scăzut la fișierele de date ale bazei de date care ar fi destul de eficiente. Cu toate acestea, ar fi eficient numai pentru sarcina individuală din fața dvs. și ar trebui să o depanați și ar putea fi necesar să o rescrieți complet dacă domeniul de acces al datelor dvs. s-ar schimba. Probabil că v-ar lua ani de zile pentru a obține într-adevăr codul complet optimizat și generalizat și chiar și atunci nu ați fi aproape de eficiența codului din interiorul motorului de stocare SQL Server.,deci, unde este câștigul în reinventarea roții? Doar pentru că motorul SQL Server este atât de bine optimizat și depanat, încât este mai bine să îl lăsați să facă iterarea pentru dvs. și să profite de dezvoltarea și testarea extinsă care este deja încorporată în baza de date. dacă vă uitați mai atent la activitățile dvs. de prelucrare a datelor, cred că veți constata că există într-adevăr foarte puține ocazii în care sunt necesare cursoare. În primul rând, de multe ori vă puteți atinge obiectivul bazându-vă pe comenzile SQL bazate pe set în Transact-SQL și ignorând ordinea rândurilor unui tabel., În al doilea rând, cursoarele Transact-SQL sunt doar o modalitate de a itera printr-un rând de tabel pe rând. Dacă puteți identifica în mod unic fiecare rând dintr-un tabel pe care trebuie să-l repetați, puteți utiliza o buclă WHILE mai degrabă decât un cursor și puteți obține performanțe mai bune. Permiteți-mi să vă prezint un exemplu pentru a vă arăta de ce.

Compararea strategiilor de iterație

presupuneți că puteți identifica în mod unic fiecare rând al unui tabel, deoarece tabelul are o cheie unică sau un grup unic de coloane., Într-o buclă de timp, tot ce trebuie să faceți este să găsiți cea mai mică valoare a condiției unice și apoi să găsiți următoarea valoare cea mai mare de fiecare dată când iterați. Iată un exemplu din SQL Server 2005 AdventureWorks eșantion baze de date de producție.Tranzacțieistorie de masă. Ea are un indice grupate pe cheia primară, și în timp ce bucla poate căuta în rândul de fiecare dată.,

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

Aici e aceeași buclă folosind un FAST FORWARD cursorul, care este cel mai eficient tip de Transact-SQL cursorul pentru citirea de date:

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

Pe laptop-ul meu, dupa ce am alergat-o de câteva ori să asigurați-vă că toate datele sunt în cache, în TIMP ce buclă nevoie de nouă secunde și cursorul nevoie de 17 secunde. Duratele proprii pot varia. Rețineți că, chiar dacă exemplul nu face nimic cu datele, bucla WHILE este mai rapidă. Cursorul adaugă în mod evident mai mult deasupra capului.,

cursorul necesită, de asemenea, comenzi suplimentare, care fac ca Codul să pară aglomerat. Fără a intra în detalii despre modul în care funcționează cursoarele, pe care Microsoft le explică pe deplin în Microsoft SQL Server 2005 Books Online, observați că atunci când utilizați o buclă WHILE, nu există nicio cerință de a declara, deschide, închide și dealoca nimic. Logica este mai simplă și puteți chiar să actualizați rândurile în mod liber pe parcurs. Pentru a actualiza rândurile folosind cursorul, va trebui să schimbați tipul cursorului.chiar și o buclă în timp ce adaugă deasupra capului de iterație., Este posibil să îl puteți înlocui cu o comandă de selectare bazată pe set sau să înlocuiți orice actualizări pe care doriți să le faceți în bucla dvs. cu comanda de actualizare bazată pe set și să lăsați iterarea la motorul SQL Server. O simplă instrucțiune SELECT pentru a obține aceleași date ca și cursorul nostru și în timp ce bucla de mai sus durează mai puțin de 3 secunde și returnează rândurile clientului, ceea ce este mai mult de lucru decât cele două bucle anterioare.

SELECT *FROM Production.TransactionHistory

această selectare se bazează pe SQL Server pentru a itera datele și este de departe cea mai rapidă dintre cele trei metode de acces la date pe care le-am analizat.,

de la pungi la seturi

uneori cursoarele ar putea părea necesare. Când pur și simplu trebuie să itera prin datele bazei de date, rând cu rând, în ordinea lor fizică, uneori, doar un cursor va funcționa. Acest lucru se întâmplă cel mai frecvent atunci când aveți rânduri duplicate și nu există nicio modalitate de a identifica în mod unic un rând dat în tabel. Aceste tabele sunt saci, nu Seturi, de date, ca un ” sac ” nu elimină valorile duplicat, ca un set nu.astfel de pungi de date apar de obicei atunci când importați date dintr-o sursă externă și nu puteți avea încredere completă în date., De exemplu, dacă tabelul nostru de istoric al tranzacțiilor AdventureWorks nu avea un grup de coloane pe care să le poți apela unic și/sau să ai rânduri duplicate, s-ar putea să crezi că trebuie să folosești un cursor. cu toate acestea, puteți transforma întotdeauna o pungă de rânduri într-un tabel normalizat. Chiar dacă aveți rânduri duplicate într-un tabel sau niciun set de coloane pe care vă puteți baza pentru unicitate, puteți adăuga o coloană de identitate în tabel și puteți semăna identitatea pentru a începe numerotarea cu 1. Aceasta adaugă o cheie unică la tabel, permițându-vă să utilizați o buclă în timp ce în loc de un cursor., Odată ce aveți o cheie unică, puteți elimina duplicatele folosind comanda de actualizare bazată pe Set Transact-SQL.

API-ul logic al datelor bazei de date

folosind operațiunile set-base este mai bună decât iterarea datelor în cel puțin două moduri.

în primul rând, comenzile SQL bazate pe set sunt mai eficiente, deoarece utilizați motorul extrem de optimizat al SQL Server pentru a face iterația. Dacă iterați singur datele, nu utilizați în mod optim motorul de stocare SQL Server. În schimb, îl ardeți cu comenzi pentru a prelua doar un singur rând la un moment dat., De fiecare dată când solicitați un singur rând, comanda dvs. trebuie să treacă prin SQL Server optimizer înainte de a putea ajunge la motorul de stocare și veți ajunge să nu utilizați codul optimizat al motorului de stocare SQL Server. Dacă te-ai iterat, te bazezi și pe informații fizice străine despre tabel, și anume ordinea rândurilor, atunci când procesezi datele. Comenzile set-base Transact-SQL SELECT, UPDATE și DELETE vă oferă o modalitate de a ignora ordinea rândurilor și de a le afecta doar pe baza caracteristicilor datelor-și sunt mai rapide.,în al doilea rând, comenzile bazate pe seturi sunt mai logice, deoarece gândirea datelor din seturi vă îndepărtează de detaliile străine care sunt mai preocupate de modul în care datele sunt de fapt ordonate. De fapt, comenzile bazate pe set, cum ar fi selectarea, actualizarea și ștergerea, atunci când sunt aplicate direct pe tabele și nu într-un cursor sau în buclă, vă aduc mai aproape logic de datele dvs., tocmai pentru că puteți ignora ordinea datelor.,

Iată un alt mod de a gândi la acest al doilea punct-la fel cum procedurile stocate sunt API-ul cel mai natural pentru aplicațiile de interfață cu SQL Server programatic, astfel încât comenzile SQL bazate pe set sunt API-ul adecvat pentru accesarea datelor relaționale. Procedurile stocate decuplează aplicația dvs. de bazele de date interne și sunt mai eficiente decât interogările ad-hoc. În mod similar, comenzile SQL set-base din Transact-SQL vă oferă o interfață logică pentru datele dvs. relaționale și sunt mai eficiente deoarece vă bazați pe motorul de stocare SQL Server pentru iterarea datelor.,

linia de jos nu este că iterarea prin date este rău. De fapt, de multe ori este inevitabil. Mai degrabă, ideea este să lăsați motorul de stocare să o facă pentru dvs. și să se bazeze în schimb pe interfața logică a comenzilor Transact-SQL bazate pe set. Cred că veți găsi puține în cazul în care orice situații în care trebuie să utilizați de fapt, un cursor Transact-SQL.