Introducere

În bazele de date relaționale, operațiile se fac pe un set de rânduri. De exemplu, o instrucțiune SELECT returnează un set de rânduri care se numește set de rezultate. Uneori, logica aplicației trebuie să funcționeze cu un rând la rând, mai degrabă decât cu întregul set de rezultate simultan. În T-SQL, o modalitate de a face acest lucru este utilizarea unui CURSOR.

Dacă dețineți abilități de programare, probabil că veți folosi o buclă ca FOR sau WHILE pentru a itera printr-un articol la un moment dat, faceți ceva cu datele și treaba sunt gata. În T-SQL, un CURSOR este o abordare similară și ar putea fi preferat deoarece urmează aceeași logică. Dar fiți sfătuiți, luați această cale și pot să apară probleme.

Există unele cazuri când utilizarea CURSOR nu face atât de mare mizerie, dar în general acestea ar trebui evitate. Mai jos, vom arăta câteva exemple în care utilizarea unui CURSOR creează probleme de performanță și vom vedea că aceeași treabă poate fi realizată în multe alte moduri.

În scopul acestei demonstrații vom folosi baza de date AdventureWorks2012 și să presupunem că vrem să obținem niște date de la. tabel, pentru fiecare produs care necesită mai puțin de o zi pentru a fi fabricat, adică din tabel ..

Un exemplu de cursor

Să începem folosind un CURSOR și să scriem următoarea sintaxă:

După o scurtă pauză de cafea , interogarea s-a terminat de executat, returnând 833 de rânduri în timpul prezentat mai jos. Este important să menționăm că sintaxele alese mai sus sunt doar în scopuri demonstrative și nu am făcut nicio reglare a indexului pentru a accelera lucrurile.

În executarea cursorului, avem doi pași. Pasul unu, poziționarea, când cursorul își stabilește poziția pe un rând din setul de rezultate. Pasul doi, regăsirea, când obține datele din acel rând specific într-o operație numită FETCH.

În exemplul nostru, cursorul își setează poziția pe primul rând returnat de primul SELECT și preia valoarea ProductID care se potrivește condiției WHERE în variabila @id. Apoi, al doilea SELECT folosește valoarea variabilă pentru a obține date de la. iar următorul rând este preluat. Aceste operații se repetă până când nu mai există rânduri cu care să lucrați.

În cele din urmă, sintaxa CLOSE eliberează setul de rezultate curent și elimină blocările din rândurile utilizate de cursor, iar DEALLOCATE elimină referința cursorului.

Tabelele noastre demo sunt relativ mici, conținând aproximativ 1.000 și 500 de rânduri. Dacă ar trebui să parcurgem tabele cu milioane de rânduri, ar dura o perioadă considerabilă de timp, iar rezultatele nu ne vor face plăcere.

Să oferim cursorului o altă șansă și să comentăm linia – STATIC LOCAL. Există multe argumente pe care le putem folosi într-o definiție a cursorului, mai multe despre asta pe acest link Argumente CURSOR, dar deocamdată să ne concentrăm asupra a ceea ce înseamnă aceste două cuvinte.

Când specificăm cuvântul cheie LOCAL, scopul cursorului este local pentru lotul în care a fost creat și este valabil numai în acest domeniu. După finalizarea executării lotului, cursorul este repartizat automat. De asemenea, cursorul poate fi menționat printr-o procedură stocată, un declanșator sau printr-o variabilă locală de cursor într-un lot.

Cuvântul cheie STATIC face o copie temporară a datelor utilizate de cursor în tempdb într-un tabel temporar . Aici avem niște gotchas. Un cursor STATIC este doar în citire și este denumit și un cursor instantaneu, deoarece funcționează numai cu datele din momentul în care a fost deschis, ceea ce înseamnă că nu va afișa nicio modificare făcută în baza de date pe setul de date utilizate de cursor.Practic, nicio actualizare, ștergere sau inserare făcută după deschiderea cursorului nu vor fi vizibile în setul de rezultate ale cursorilor decât dacă închidem și redeschidem cursorul.

Rețineți acest lucru înainte de a utiliza aceste argumente și verificați dacă se potrivește nevoilor dvs. Dar să vedem dacă cursorul nostru este mai rapid. Mai jos avem rezultatele:

12 secunde sunt mult mai bune decât 4 minute și 47 secunde, dar rețineți că restricțiile explicate mai sus.

Dacă executăm sintaxa folosind acest argument LOCAL READ_ONLY FORWARD_ONLY vom obține aceleași rezultate. READ_ONLY FORWARD_ONLY cursorul poate fi derulat doar de la primul rând la ultimul. Dacă cuvântul cheie STATIC lipsește, cursorul este dinamic și folosește un plan dinamic, dacă este disponibil. Dar există cazuri când un plan dinamic este mai rău decât unul static.

Ce se întâmplă atunci când decomentăm –LOCAL FAST_FORWARD?

FAST_FORWARD este echivalent cu cursorul READ_ONLY și FORWARD_ONLY, dar are capacitatea de a alege planul mai bun, fie unul static, fie unul dinamic.

LOCAL FAST_FORWARD pare a fi cea mai bună alegere datorită flexibilității sale alegeți între un plan static sau dinamic, dar FORWARD_ONLY face și treaba foarte bine. Deși am obținut rezultatul în medie de 12 secunde folosind ambele metode, aceste argumente ar trebui testate corespunzător înainte de a alege una dintre ele.

Există multe modalități de a obține același rezultat, mult mai rapid și cu impact mai mic asupra performanței.

Alternativă la cursor

O metodă este utilizarea unui JOIN și, după cum putem vedea în continuare, rezultatele sunt considerabil mai bune.

În primul rând, trebuie să activăm timpul statisticilor pentru a măsura timpul de execuție SQL Server și să facem un INNER JOIN între două tabele,. și . pe coloana ProductID. După apăsarea butonului EXECUTE, am produs aceleași rezultate în 330 ms, comparativ cu ora 04:47 din metoda cursorului și avem un zâmbet pe față.

Nu este nevoie să parcurgem fiecare rând pentru a obține ceea ce avem nevoie, nu mai avem bucle, nici clauză while, nici iter acțiuni, în schimb lucrăm cu seturi de date, obținând ceea ce dorim mai repede și scriem mai puțin cod.

O utilizare adecvată a cursorului

Acum că am văzut cât de multe daune cursorul poate face, să vedem un exemplu în care îl putem folosi.

Să presupunem că dorim să selectăm dimensiunea și numărul de rânduri doar pentru anumite tabele dintr-o bază de date. Pentru a realiza acest lucru, vom obține toate numele tabelelor pe baza criteriilor din information_schema.tables și folosind un CURSOR vom parcurge fiecare dintre numele tabelului respectiv și vom executa procedura stocată sp_spaceused prin trecerea unui nume de tabel la un moment dat pentru a obține informațiile de care avem nevoie .

Vom folosi aceeași bază de date AdventureWorks2012 și vom obține toate tabelele din schema de vânzări care conține numele „Vânzări”. Pentru fiecare nume de tabel returnat, dorim să vedem toate informațiile din information_schema.tables.

Mai jos avem sintaxa T-SQL și rezultatele obținute:

Aceasta este o metodă în care CURSOR este util prin iterarea unor date câte un rând la rând și obține rezultatul necesar. În acest caz particular, cursorul face treaba fără a avea implicații asupra performanței și este ușor de utilizat.

Concluzii

Acolo îl avem. Am arătat câteva exemple cu bune, rele și urâte atunci când folosim cursori. În majoritatea cazurilor, putem folosi JOINS, chiar și clauze WHILE, SSIS pachete sau alte metode alternative pentru a obține același rezultat mai rapid, cu un impact mai mic asupra rezultatelor de performanță și chiar scrierea mai puține linii de sintaxă.

Când au de a face cu mediul OLTP și cu seturi mari de date de procesat, cuvântul „CURSOR” nu ar trebui rostit.

În SQL, este o bună practică să te gândești la efectuarea operațiunilor pe seturi de date, mai degrabă decât să te gândești într-un mod programatic, folosind iterații sau bucle, deoarece acest tip de abordare nu este recomandat și nu este destinat acestei utilizări. Încercarea de a folosi bucle precum FOR sau FOREACH din limbaje de programare și de a asocia această logică cu operațiile SQL, este un obstacol pentru obținerea soluției corecte la nevoile noastre. Trebuie să ne gândim la operațiuni bazate pe seturi, mai degrabă decât la un rând la rând, pentru a obține datele de care avem nevoie.

Cursorii ar putea fi folosiți în unele aplicații pentru operațiuni serializate, așa cum se arată în exemplul de mai sus, dar în general ar trebui să fie evitate deoarece au un impact negativ asupra performanței, mai ales atunci când operează pe un set mare de date.

• Autor
• Postări recente

Sunt DBA la Yazaki Component Technology cu 3 ani de experiență în SQL Server.
Am o diplomă de licență în inginerie informatică și am, de asemenea, competențe în .NET, C # și Windows Server. Am dezvoltat experiență în reglarea și monitorizarea performanțelor, dezvoltarea T-SQL, strategia de backup, administrarea și configurarea bazelor de date.
Sunt pasionat de lucruri IT, jocuri video, filme bune și muzică electro-house mă ajută să lucrez când biroul este noisy.
Vezi toate mesajele lui Sergiu Onet

Ultimele postări ale lui Sergiu Onet (vezi toate)

• Utilizarea cursoarelor SQL Server – Avantaje și dezavantaje – 23 martie 2016