De: Daniel Farina | Actualizat: 2019-09-12 | Comentarii (2) | Conexe: Mai > T-SQL

Probleme

Ai auzit mereu că ar trebui să evite cursoare în cod T-SQL ca un Server SQL cele mai bune practici, becausecursors sunt în detrimentul performanceand provoca, uneori, probleme. Dar, uneori, este nevoie să faceți buclă prindate un rând la un moment dat, așa că în acest sfat ne vom uita la o comparație a modului de a faceo buclă fără a utiliza cursorul.,

soluție

știm cu toții că SQL Server, ca orice bază de date relațională permite utilizatorului săefectuați operații bazate pe seturi. De asemenea, așa cum fac mulți furnizori de baze de date, SQL Server includeo extensie procedurală care este limbajul T-SQL. Aceasta adaugă constructe găsite în limbaje procedurale care permit o codificare mai simplă pentru dezvoltatori. Aceste construcții au fost adăugate pentru un motiv și, uneori, aceasta este singura abordare a sarcinii la îndemână.,

Folosind o Buclă în Timp ce, în Loc de Cursoare în SQL Server

Dacă ați lucrat vreodată withcursors, s-ar putea găsi acest titlu un pic confusingbecause după toate, cursoare folosește în timp ce construiește pentru a itera între rânduri. Dar, pe lângă asta, vreau să vă arăt că, în anumite circumstanțe, atunci când folosim un cursor pentru a itera un set de rânduri, îl putem schimba într-o buclă while. În astfel de cazuri, singura provocareva fi alegerea unei condiții adecvate de ieșire.,

Pro și contra de a folosi cursoare pentru a itera prin rânduri de tabel în SQL Server

nu totul este în neregulă cu cursoare, ei au, de asemenea, unele avantajepeste alte tehnici de looping.

  • cursoarele sunt actualizabile: când creați un cursor, utilizați o interogare pentru a defineit folosind instrucțiunea DECLARE CURSOR. Utilizând opțiunea Actualizare din Instrucțiunea cursorcreation, puteți actualiza coloanele din cursor.,
  • puteți muta înainte și înapoi într-un cursor: Cu ajutorul SCROLL optionin DECLARĂ CURSORUL declarație puteți naviga de-a lungul cursorul înregistrări inboth direcții cu fetch opțiuni în PRIMUL rând, ULTIMUL, ANTERIOR, URMĂTOR, în RAPORT andABSOLUTE. Rețineți că opțiunea de derulare este incompatibilă cu opțiunile Forward_only și FAST_FORWARD.
  • Cursoare poate fi trecut la proceduri stocate: Dacă utilizați GLOBAL marede a crea un cursor, acesta poate fi utilizat în orice procedură stocată sau lot executedin aceeași conexiune. Acest lucru vă permite să utilizați cursoare pentru procedurile stocate imbricate.,
  • cursoarele au o mulțime de opțiuni diferite: cu cursoarele aveți șansapentru a utiliza diferite opțiuni care afectează modul în care se vor comporta în ceea ce privește blocarea.
  • cursoarele nu au nevoie de o condiție: prin utilizarea cursoarelor, manipulațiun set de rânduri ca înregistrare. Acest lucru vă permite să vă deplasați peste cursor fărănevoia de a avea o condiție booleană. De exemplu, puteți crea un cursorwith numele de baze de date care locuiesc pe o instanță de SQL Server fără theneed de un surogat cheie pentru a lucra ca o condiție de încercare ca pe o buclă în TIMP.,există, de asemenea, unele aspecte negative pe care ar trebui să le cunoașteți atunci când utilizați cursoareîn loc de alte opțiuni de buclă.

    • Dacă utilizați global cursoare în codul luați riscul de facingerrors din cauza unei cursorul fiind închis de către unele proceduri stocate imbricate în yourcode.
    • de obicei cursoarele au o performanță mai mică decât o buclă echivalentă folosind un WHILEloop sau CTE.

    Pro și contra de a folosi o buclă de timp pentru a itera prin rânduri de tabel în SQL Server

    există, de asemenea, beneficii pentru a utiliza o buclă de timp în comparație cu un cursor.,

    • în timp ce buclele sunt mai rapide decât cursoarele.
    • în timp ce buclele folosesc mai puține încuietori decât cursoarele.
    • mai puțină utilizare a Tempdb: în timp ce buclele nu creează o copie a datelor intempdb ca un cursor. Amintiți-vă că cursoarele, în funcție de opțiunile pe care le avețiutilizarea pentru a le crea poate determina crearea tabelelor temp.

    lista următoare detaliază aspectele negative ale buclelor WHILE.deplasarea înainte sau înapoi este complexă: pentru a vă deplasa înainte sau înapoi în aloop trebuie să modificați dinamic starea de iterație din interiorul buclei.,Acest lucru necesită îngrijire suplimentară; altfel puteți ajunge într-o buclă infinită.

  • riscul unei bucle infinite: în comparație cu un cursor, nu aveți un set fix de date de buclă (adică datele returnate de declarația SELECT în declarația cursorului), în schimb atunci când utilizați o buclă WHILE, trebuie să definiți o limită cu o expresie care este evaluată la true sau false.pentru a testa acest lucru, voi folosi un tabel cu o coloană de identitate (CursorTestID),o coloană varchar (Filler) și o coloană bigint (RunningTotal).,

    CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO

    ideea este de a bucla prin rânduri de masă comandat de către CursorTestID columnand actualiza RunningTotal coloana cu suma de CursorTestID coloana valoarea de valoarea de RunningTotal coloană a rândului precedent.

    dar înainte de a începe, mai întâi trebuie să generăm câteva rânduri de testare cu următorul script.,

    INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

    Pe script-ul de mai sus veți observa că am folosit doar un singur introduce statementand am profitat de lot separator (drum de 500000 de comanda), ca o scurtătură pt. a executa această instrucțiune insert 500000 de ori. Puteți citi mai multe despre această metodăpentru a repeta execuția lotului pe acest sfat:executarea unui lot T-SQL de mai multe ori folosind GO.

    exemplu de Cursor de bază pentru buclă prin rânduri de tabel în SQL Server

    să creăm un cursor pentru a umple coloana RunningTotal. Observați pe nextscript că am declarat cursorul cu opțiunea FAST_FORWARD., Acest lucru se face pentru a îmbunătăți performanța de cursorul pentru că, potrivit Microsoft theFAST_FORWARD argument „Specifică un FORWARD_ONLY, READ_ONLY cursorul cu performanceoptimizations activat”. Cu alte cuvinte, instruim SQL Server să foloseascăun cursor de citire numai care poate merge înainte și poate fi derulat de la primul la ultimul rând.

    DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

    imaginea următoare este o captură de ecran care arată executarea de script-ul de mai sus.După cum puteți vedea, a durat trei minute și cinci secunde pentru a actualiza 500.000 de discuri testul nostru de masă.,

    exemplu de buclă de bază în timp ce pentru a trece prin rânduri de tabel în SQL Server

    acum voi rescrie scriptul anterior evitând utilizarea unui cursor. Veți observa că conține o buclă în timp ce este aproape identică cu cea din scriptul cursorului. Aceasta este, așa cum am spus anterior, pentru că chiar și atunci când lucrați cu cursoaretrebuie să utilizați o structură de control iterativ.

    următoarea imagine este o captură de ecran a execuției scriptului de mai sus. A durat mai puțin timp pentru a rula bucla în timp ce decât cursorul.,

    un Alt SQL Server Cursorul Exemplu

    Să luăm de exemplu cursorul în tipStandardize SQL Server date cu text de căutare și înlocuiți funcție. Un cuvânt de sfaturi, pentru a rula acest cod, ar trebui să urmați pașii din sfat pentru a crea mediul de testare.

    și aici este codul cursorului:

    dacă disecăm acest cod, putem vedea că există un cursor care trece prinprodusele din tabel pe care le-am copiat mai jos.,

    DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products

    SQL Server Cursorul Exemplu Convertit la o Buclă în Timp ce

    În scopul de a înlocui acest cursor cu o BUCLĂ în TIMP ce, avem nevoie de a crea un temporarytable să pună în aplicare o evidență masă. Pentru toți cei care nu știu ce este un tabel de corespondență, îl putem defini ca un tabel care conține o pereche de coloane constând dintr-o cheie și valoarea acesteia. În cazul nostru particular, vom folosi o întreagă cheie secvențială începând de la 1, astfel încât să o putem folosi ca iterator. Această cheie va fi asociatăla un produs din tabelul de produse.,

    La început, deoarece Produsele de masă are ProductID cheie definit ca un identityyou putea fi tentați să treceți peste acest pas, dar trebuie să ia în considerare faptul că într-un real casea rând ar fi putut fi șterse, prin urmare, nu va fi capabil de a utiliza identitycolumn ca un iterator. În plus, un rând poate fi șters în timp ce rulăm ourcode și ar putea duce la erori de execuție. Pentru a evita acest lucru, vom adăuga un bloc de captură. Voi merge mai departe.înainte de a începe bucla WHILE, trebuie să setați starea de pornire și oprire., Forthis matter am adăugat două noi variabile întregi numite @ Iterator și @ MaxIterator.Variabila @MaxIterator este utilizată pentru a păstra numărul de articole din #TallyTabletable și i-am setat valoarea o singură dată înainte de a începe bucla. @Iterator variableis inițializat la 1, așa cum am definit-o ca numărul de pornire pe secvența andwe sunt de gând să incrementeze valoarea sa la fiecare iterație.

    pașii următori
    • sunteți nou la cursoare și aveți nevoie de practică? În nexttip veți găsi o explicație, un exemplu de cursor ușor de înțeles și mai multe citiri recomandate: SQL Server Cursor Example.,
    • Dacă doriți pentru a converti existente cursoare în cod pentru a seta bazate pe interogări,să ia o privire la acest chapterSQL Server Converti Cursorto Stabilit pe bază de theSQL Server de baze de Date de Proiectare cele mai Bune Practici Tutorial.
    • aveți nevoie de un alt exemplu cu privire la utilizarea unei bucle While? Aruncați o privire la acest sfatcare vă va arăta cum să împărțiți declarațiile DML în loturi: Optimizați SERVERINSERT SQL mare, actualizați și ștergeți procesele utilizând loturi.
    • în cazul în care nu știți cum să utilizați încercați…Prinde exceptionhandling, aruncați o privire la acest sfat: SQL Server încercați și prindeți manipularea excepțiilor.,
    • Stay tuned to theSQL Server T-SQL Tipscategory pentru a obține mai multe idei de codificare.

    Ultima Actualizare: 2019-09-12

    Despre autor
    Daniel Farina s-a născut în Buenos Aires, Argentina. Auto-educat, din copilărie a arătat o pasiune pentru învățare.
    Vezi toate sfaturile mele
    Resurse conexe

    • Mai multe sfaturi pentru dezvoltatori de baze de date…

Lasă un răspuns

Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate cu *