T-sql, calcularea totalurilor de funcționare

Acesta este un alt sarcini comune. Principiul de bază constă în acumularea valorilor atributului (elementul aggregatable) pe baza ordonării alt atribut sau atribute (element de comanda), opțional în prezența unor rânduri de secțiuni definite pe baza unui alt atribut sau atribute (membru partiție). În viață, există multe exemple de funcționare, totaluri calcula cum ar fi calculul soldurilor bancare, monitorizarea disponibilității produselor în stoc și cifrele de vânzări curente, etc.

Pentru a demonstra diferitele soluții pe care le folosesc soldurile conturilor. Iată codul care creează și umple masa Tranzacții o cantitate mică de date de test:

Fiecare rând reprezintă o tranzacție bancară pe cont. tranzacție Depozitele marcate ca o valoare pozitivă în coloana val și retrageri - ca o tranzacție valoare negativă. Sarcina noastră - pentru a calcula soldul contului în orice moment, prin acumularea sumelor din tranzacțiile efectuate în linia val atunci când comanda de tranid coloana, iar acest lucru trebuie să fie făcut pentru fiecare cont separat. Rezultatul dorit ar trebui să arate astfel:

Pentru a testa ambele soluții au nevoie de mai multe date. Acest lucru se poate face cu această interogare:

Puteți seta datele introduse pentru a modifica numărul de secțiuni (conturi) și rânduri (tranzacții) în secțiunea.

Bazat pe un set de soluții cu utilizarea funcțiilor de ferestre

Voi începe povestea cu soluții bazate pe seturi, care utilizează o agregare funcție fereastră SUM. fereastră Determinarea aici destul de clar: trebuie să partiție fereastră de actid, comanda tranid și un filtru pentru a selecta linia în cadru în partea de jos (PRECED nemarginit) la curent. Iată ancheta relevantă:

Acest cod nu este numai simplă și directă - și este rapid. Planul acestei interogări este prezentat mai jos:

O abordare similară poate fi implementată folosind compușii. Se folosește același predicat, ca și în cazul în care în clauza interogarea secundară conexiunile clauza ON. În acest caz, tranzacția N-lea de aceeași în instanță a contului A, desemnat ca T1, veți găsi corespondențele în N T2 exemplu, numerele de tranzacție se execută de la 1 la N. Ca urmare, liniile T1, în comparație repetate, prin urmare, este necesar rânduri de grup de pe toate elementele unui T1, pentru a obține informații despre tranzacție curentă și aplică agregarea atributului Val T2 pentru a calcula suma cumulativă. Cererea Ready arata ca acest lucru:

În planurile Figura ambele soluții sunt enumerate mai jos:

În fiecare secțiune a planului include citirea 1 + 2 +. + Rânduri R, pentru un total de (r + r * 2) / 2. Numărul total de rânduri procesate în plan este p * r + p * (r + r2) / 2. Aceasta înseamnă că numărul de operațiuni în ceea ce privește creșterile pătrat secțiune cu o dimensiune în creștere, adică în cazul în care creșterea dimensiunii secțiunii f ori, creșterea volumului de lucru f aproximativ 2 ori. Acest lucru este rău. De exemplu, 100 de rânduri corespunde cu 10 mii Strings. Și o mie de rânduri corespunde milioane etc. Pur și simplu pune, acest lucru duce la o scădere semnificativă a inflației în interogarea la o rată de nu atât de puțin secțiune, deoarece funcția pătratică este în creștere foarte rapid. Aceste soluții funcționează în mod satisfăcător în zeci de rânduri pe secțiune, dar nu mai mult.

Soluții cu ajutorul cursorului

soluții bazate pe cursor sunt puse în aplicare „pe frunte.“ Declară un cursor pe un actid de date de secvențiere bazate pe interogare și tranid. După aceea, o iterativ trece înregistrările cursorului. La detectarea unui cont nou este variabilă de resetare care conține unitatea. La fiecare iterație se adaugă variabila la valoarea unei noi tranzacții, atunci șirul este stocată în tabela variabilă, cu informații despre tranzacție curentă, plus valoarea curentă a total progresive. După trecerea iterativ returnează rezultatul variabilei tabel. Aici este un cod complet soluție:

Planul de interogare cu ajutorul cursorului este prezentat mai jos:

Planul este scalat liniar, astfel încât datele din index sunt căutate doar o singură dată într-o anumită ordine. De asemenea, fiecare operațiune de citire un rând al cursorului aproximativ aceeași valoare pe fiecare linie. Dacă luăm sarcina prelucrării unui șir de cursor egal cu g, costul acestei decizii poate fi estimată ca p * r + p * r * g (după cum vă amintiți, p - numărul de secțiuni, și r - numărul de rânduri din secțiunea). Deci, dacă vom crește numărul de rânduri pe secțiuni uneori f, sarcina pe sistemul va fi p * r * f + p * r * f * g, adică va crește liniar. Costul de prelucrare pe linie este mare, dar din cauza naturii liniare de scalare, cu o anumită secțiune Mărimea acestei decizii va fi de a demonstra o mai bună scalabilitate decât soluțiile bazate pe interogări imbricate și conexiuni datorită scalarea pătratic acestor soluții. Uzat mi-a arătat că măsurarea performanței numărul, atunci când soluția la cursorul mai rapid egală cu câteva sute de rânduri pe secțiuni.

În ciuda câștigurilor de performanță prevăzute de deciziile pe baza cursorului, în general, acestea ar trebui să fie evitate, deoarece acestea nu sunt relaționale.

Soluții bazate pe CLR

O posibilă soluție bazată pe CLR (Common Language Runtime) este, în esență, o formă de soluții cu ajutorul cursorului. Diferența este că, în loc de a folosi cursorul T-SQL, care își petrece o mulțime de resurse pentru a primi rândul următor și ITERATE itera SqlDataReader .NET și .NET, care sunt mult mai rapid. Una dintre caracteristicile CLR care face ca această opțiune mai rapid este faptul că nu este nevoie de șirul rezultat într-un tabel temporar - rezultatele sunt trimise direct la procesul de asteptare. Soluții logice, bazate pe CLR este similară cu logica deciziei cu ajutorul cursorului și T-SQL. Aici este codul C # care identifică o procedură stocată pentru decizie:

Dacă apelați AccountBalances de asamblare, precum și calea către fișierul de asamblare - „C: \ Proiecte \ AccountBalances \ bin \ Debug \ AccountBalances.dll“, pentru a încărca ansamblul în baza de date și să înregistreze procedura stocată poate fi următorul cod:

După implementare, următorul cod poate efectua procedurile de asamblare și de înregistrare:

Așa cum am spus, SqlDataReader este doar o altă formă a cursorului, dar în această versiune costul de citire linii este semnificativ mai mică decât cu cursorul tradițional în T-SQL. De asemenea, în iterație NET este mult mai rapid decât T-SQL. Astfel, soluțiile bazate pe CLR, de asemenea, scalate liniar. Testarea a arătat că performanța de luare a deciziilor devine o productivitate mai mare și cu utilizarea compușilor subinterogari în cazul în care numărul de rânduri din secțiunea 15 trece prin.

La finalizarea este necesar să se execute codul următor Cleanup:

iterație imbricat

Până în acest punct am arătat soluții iterative și soluții bazate pe seturi. Decizia următoare se bazează pe o iterații imbricate, care sunt abordări iterative și hibride bazate pe seturi. Ideea este de a copia mai întâi rândurile din tabelul sursă (în acest caz, conturi bancare) într-un tabel temporar cu un nou atribut numit ROWNUM, care se calculează cu ajutorul funcției ROW_NUMBER. Numerele de linie sunt partiționate de actid și ordonate după tranid, numărul, prin urmare, prima tranzacție pentru fiecare cont bancar atribuit 1, a doua tranzacție - 2, etc. Apoi, tabelul temporar este creat cu o listă de taste grupate index (ROWNUM, actid). Se utilizează apoi o expresie recursive sau ciclu special CTE creat pentru a procesa o linie pentru fiecare iterație în toate conturile. Apoi totalul cumulat este calculat prin adăugarea unei valori corespunzătoare liniei curente, cu valoarea asociată cu linia anterioară. Aici este punerea în aplicare a acestei logici, folosind un recursiv CTE:

Această punere în aplicare folosind o buclă explicită:

Această soluție oferă o performanță bună atunci când există un număr mare de secțiuni cu un număr mic de rânduri în secțiunile. Apoi, numărul de iterații este mic, dar activitatea principală se face pe baza unui set de o parte a soluției, care leagă linia asociată cu un număr de linie, cu rânduri asociate cu numărul liniei anterioare.

variabilă actualizare Multiline

Arătat până la acest punct tehnici calcula totaluri de funcționare sunt garantate pentru a da rezultatul corect. Această secțiune descrie procedura este ambiguă, deoarece se bazează pe comportamentul observat și nu documentată a sistemului, în plus, este contrară principiilor relativității. Ridicat atractivitatea datorită vitezei mari.

Această metodă folosește o variabilă UPDATE. UPDATE pentru a atribui o expresie variabilă bazată pe valoarea coloanei și atribuie valori în coloanele cu o expresie variabilă. Soluția începe cu crearea unei temporare Tranzacții de masă denumite cu atributele actid, tranid, Val și echilibru și o listă de chei index (actid în cluster, tranid). Apoi, masa temporară este umplut cu toate liniile din Transactions originale DB, în care o coloană de echilibru în toate rândurile valorizează 0.00 este introdus. instrucțiune UPDATE este apoi apelat cu variabilele asociate cu tabelul de timp pentru a calcula totalurile de funcționare și a introduce valoarea calculată în echilibru coloana.

Variabilele folosite si @prevbalance @prevaccount, iar valoarea din coloana balanța se calculează folosind următoarea expresie:

După exprimarea soluție UPDATE este o linie din tabelul temporar și elimină ultimul. Aici este un cod complet soluție:

Planul acestei soluții este prezentată în figura de mai jos. Prima parte este instrucțiunea INSERT, al doilea - UPDATE, iar al treilea - SELECT:

Măsurarea performanțelor

Am petrecut măsurarea și compararea performanțelor diferitelor metode. Rezultatele sunt prezentate mai jos:

Am rupt două din program datorită faptului că metoda de utilizare a unei interogări imbricată sau mai lent decât ceilalți compuși, astfel încât a trebuit să folosesc o scară diferită pentru ea. În orice caz, rețineți că majoritatea soluțiilor prezintă o dependență liniară a volumului lucrărilor la mărimea secțiunii, și singura soluție pe baza unei interogări imbricate sau compuși prezintă o dependență pătratică. De asemenea, arată în mod clar cât de mult mai eficient o nouă decizie pe baza unei funcții fereastră agregate. UPDATE bazat pe o variabilă de decizie este, de asemenea, foarte rapid, dar din motivele deja descrise, eu nu recomand utilizarea acestuia. Soluția folosind CLR este, de asemenea, destul de rapid, dar este necesar să se scrie acel cod și să implementați asamblare .NET în baza de date. După cum se arată, și seturi de soluții pe bază folosind unități de fereastră este cea mai preferată.