Acasă Adult69 DBIx :: MultiRow – Actualizarea rapida si usoara a mai multor randuri...

Adult69

DBIx :: MultiRow – Actualizarea rapida si usoara a mai multor randuri de baze de date

187

In multe sisteme apare o cerinta pentru actualizarea mai multor randuri de baze de date SQL. Pentru un numar mic de randuri care necesita actualizari, poate fi adecvat utilizarea unei instructiuni UPDATE pentru fiecare rand care necesita o actualizare. Dar daca exista un numar mare de randuri care necesita o actualizare, atunci cheltuielile generale cu emiterea unui numar mare de instructiuni UPDATE pot duce la operatiunea, in ansamblu, dureaza mult timp pana la finalizare.

Sfatul traditional pentru imbunatatirea performantei pentru mai multe declaratii UPDATE este sa „pregatiti” interogarea necesara o data, apoi sa „executati” interogarea pregatita o data pentru fiecare rand care necesita o actualizare. Dar, in multe cazuri, aceasta ofera doar o imbunatatire modesta, deoarece fiecare operatiune UPDATE necesita inca o comunicare dus-intors cu serverul bazei de date. In cazul in care serverul de aplicatii si serverul de baze de date sunt pe diferite gazde, calatoria dus-intors va implica si latenta retelei. Factorul dominant in timpul necesar pentru a finaliza operatiunea generala tinde sa fie lucrarea „admin” in transmiterea intentiei aplicatiei catre serverul de baze de date, mai degraba decat actualizarile efective ale bazei de date.

O solutie mai eficienta la aceasta problema este incercarea de a reduce numarul de declaratii UPDATE. Sa incepem cu un tabel simplu:

salariul personalului Bob 1100 Jane 1150 Frank 1050 Susan 1125 John 1100

Sa spunem ca vrem sa aplicam unele cresteri salariale intregului personal. Am putea actualiza fiecare rand folosind o declaratie UPDATE:

ACTUALIZARE SET salariu = 1200 WHERE nume = “Bob”; ACTUALIZARE personal SET salariu = 1200 WHERE nume = ‘Jane’; ACTUALIZARE personal SET salariu = 1200 WHERE nume = “Frank”; UPDATE SET salariu = 1200 WHERE nume = ‘Susan’; ACTUALIZARE personal SET salariu = 1200 WHERE nume = ‘John’;

Aceasta ar necesita cinci declaratii de actualizare. Putem face ceva mai bun? Da; in acest exemplu, intregul personal primeste acelasi salariu, astfel incat sa putem efectua acele modificari intr-o singura declaratie UPDATE:

ACTUALIZARE Personal SET salariu = 1200 WHERE nume IN (‘Bob’, ‘Jane’, ‘Frank’, ‘Susan’, ‘John’);

Aceasta reduce numarul de intrebari de la cinci la una. Dar aceasta abordare va functiona numai atunci cand toate randurile au aceeasi actualizare pentru aceleasi coloane. Daca nu toata lumea primea acelasi salariu?

ACTUALIZARE SET salariu = 1200 WHERE nume = “Bob”; ACTUALIZARE SET salariu = 1250 WHERE nume = ‘Jane’; ACTUALIZARE personal SET salariu = 1200 WHERE nume = “Frank”; ACTUALIZARE SET salariu = 1250 WHERE nume = ‘Susan’; ACTUALIZARE personal SET salariu = 1200 WHERE nume = ‘John’;

Nu mai setam toate campurile salariale la aceeasi valoare, deci nu le putem prabusi intr-o singura declaratie. Dar putem grupa actualizarile in functie de valoarea setata, apoi putem face o instructiune UPDATE pentru fiecare valoare distincta. In acest caz, exista doua valori distincte, deci o putem face in doua declaratii UPDATE:

ACTUALIZARE Personal SET salariu = 1200 WHERE nume IN (‘Bob’, ‘Frank’, ‘John’); UPDATE SET salariu = 1250 WHERE nume IN (“Jane”, “Susan”);

Deci putem reduce numarul de intrebari de la cinci la doua. Nu este la fel de bun, dar totusi o imbunatatire.

Dar masura in care acest lucru ne ajuta se reduce pe masura ce proportia valorilor SET distincte creste. In cele din urma fiecare valoare SET este diferita, de exemplu:

UPDATE SET salariu = 1125 WHERE nume = “Bob”; ACTUALIZARE personal SET salariu = 1200 WHERE nume = ‘Jane’; ACTUALIZARE personal SET salariu = 1100 WHERE nume = ‘Frank’; UPDATE SET salariu = 1175 WHERE nume = ‘Susan’; UPDATE SET salariu = 1150 WHERE nume = ‘John’;

Intrucat fiecare clauza SET este acum diferita, nu putem grupa interogari folosind abordarile mentionate anterior, asa ca mai avem o instructiune UPDATE pentru fiecare rand care necesita modificari. Putem face ceva?

Ce se intampla daca am avea un tabel de baze de date care continea o reprezentare a actualizarilor pe care am dorit sa le realizam, de ex

actualizeaza salariul nume Bob 1125 Jane 1200 Frank 1100 Susan 1175 John 1150

si am putea convinge serverul bazei de date sa aplice acele actualizari pe tabelul tinta? Acest lucru este de fapt posibil in multe sisteme de baze de date. In cazul PostgreSQL, acesta poate fi realizat folosind o clauza FROM cu UPDATE, astfel:

ACTUALIZARE personal SET salariu = actualizari.salary FROM actualizari WHERE Updates.name = staff.name;

Acest lucru face un INNER JOIN intre tabelele „personal” si „actualizari” unde coloana „nume” se potriveste. Clauza SET ia apoi campul „salariu” din tabelul „actualizari” si il foloseste pentru a actualiza campul „salariu” din tabelul „personal”.

Putem convinge cu usurinta existenta unui tabel „actualizari” prin crearea unui tabel temporar si popularea acestuia. Este relativ simplu sa populam o tabela cu mai multe randuri cu o singura interogare (sau cel putin, interogari mult mai putine decat numarul de randuri dorite).

Deci, avand in vedere o lista de actualizari care trebuie aplicate, le putem efectua folosind urmatorii pasi:

Utilizati CREATE TEMPORARY TABLE pentru a crea un tabel temporar pentru a retine actualizarile
Utilizati INSERT pentru a adauga tabelul temporar cu actualizarile
Utilizati UPDATE… FROM pentru a actualiza tabelul tinta folosind actualizari din tabelul temporar
Folositi DROP TABLE pentru a renunta la tabelul temporar

Deci in exemplul de mai sus putem reduce cinci afirmatii la patru. Aceasta nu este o imbunatatire semnificativa in acest caz. Insa acum numarul de declaratii nu mai depinde direct de numarul de randuri care necesita actualizari.

Chiar daca am dori sa actualizam o mie de randuri cu valori diferite, am putea totusi face acest lucru cu patru declaratii.

OK, este minunat, avem cateva abordari teoretice pentru reducerea numarului de intrebari, acum?

Codul necesar pentru a implementa logica de mai sus este suficient de fals incat probabil nu am dori sa o repetam. Asadar, am putea gandi in ceea ce priveste crearea unui modul reutilizabil care sa implementeze aceasta logica.

Aceasta este intentia DBIx :: MultiRow.

Introducerea functiei ar fi o lista de actualizari pe care apelantul doreste sa le faca. Fiecare actualizare trebuie sa contina doua aspecte:

O indicatie despre care rand trebuie actualizat
Valori noi pentru unul sau mai multe campuri

Revenind la primul nostru exemplu:

Pentru a face acest lucru folosind DBIx :: MultiRow, am scrie:

utilizati DBIx :: MultiRow ‘multi_update’; multi_update ($ dbh, table => \% staff_table_data, key_columns => [‘nume’], value_columns => [‘salariu’], Updates => [[[‘Bob’, 1200], [‘Jane’, 1200], [‘Frank’, 1200], [‘Susan’, 1200], [‘John’, 1200],]);

„Key_columns” specifica coloanele care vor fi utilizate pentru a identifica randurile care trebuie actualizate (utilizand WHERE).

„Value_columns” specifica coloanele care vor fi actualizate (utilizand SET).

„Actualizari” specifica actualizarile care trebuie facute. Fiecare element este un tablou cu doua elemente. Primul element furnizeaza valoarea coloanei (specificata de „key_columns”) pentru a identifica randul care urmeaza sa fie actualizat. Al doilea element furnizeaza valoarea care trebuie setata in coloana specificata de „value_columns”.

In acest caz, multi_update va constata ca valorile tinta sunt toate 1200 si vor efectua modificarile dorite folosind o singura instructiune UPDATE asa cum este descris mai sus. (Va folosi marcatoarele de plasare si legarea parametrilor daca considera ca este adecvat.)

Daca este dat al doilea exemplu cu doua valori distincte, multi_update va evidentia ca exista doua valori distincte, 1200 si 1250, si va efectua acest lucru cu doua instructiuni UPDATE asa cum este descris mai sus.

Optimizarea numarului de UPDATE prin gruparea valorilor SET distincte se poate face intr-un mod compatibil cu majoritatea bazelor de date SQL obisnuite. Dar abordarea temporara / UPDATE … FROM necesita cunoasterea bazei de date specifice SQL care este utilizata.

Deci DBIx :: MultiRow este structurat astfel incat abordarile generice pentru diferite baze de date SQL sa fie exprimate intr-o clasa de baza, iar abordarile care functioneaza numai pentru baze de date SQL specifice sunt exprimate intr-o subclasa. Un obiect al clasei relevante este instantiat la apelul, iar controlul este trecut apoi la implementarea relevanta a bazei de date in curs de utilizare.

Daca nu exista nici o subclasa specifica bazei de date pentru baza de date utilizata, DBIx :: MultiRow va folosi doar clasa de baza care implementeaza abordari care ar trebui sa functioneze pentru orice baza de date SQL.

In momentul scrierii, singura subclasa specifica bazei de date este pentru PostgreSQL. Asadar, daca apelantul are o baza de date PostgreSQL si apeleaza multi_update cu date pentru a reprezenta cel de-al treilea exemplu (unde valorile tinta sunt toate unice), atunci subclasa specifica PostgreSQL va efectua actualizarile cu ajutorul tabelului / UPDATE … FROM.

Mai sunt cateva detalii demne de mentionat. Sa extindem putin tabelul original:

personal companie nume_numele_nume bonus salariu last_updated WidgetCo Bob Jones 1100 100 2013-01-01 12: 30: 06.815182 + 01 WidgetCo Jane Smith 1150 105 2013-01-01 12:30: 06.815182 + 01 WidgetCo Jane Austin 1150 110 2013-01- 01 12: 30: 06.815182 + 01 WidgetCo Frank Johnson 1050 90 2013-01-01 12: 30: 06.815182 + 01 WidgetCo John Gray 1100 100 2013-01-01 12:30: 06.815182 + 01 Frobnitz Bob Jones 1100 110 2013-01 -01 12: 30: 06.815182 + 01 Frobnitz Susan Black 1125 115 2013-01-01 12: 30: 06.815182 + 01

„Nume” a fost impartit acum in „prenumele” si „prenumele”. Pentru a se potrivi pe nume, acum trebuie sa ne potrivim pe doua coloane. Deci, in timp ce anterior am specificat coloana de potrivire folosind un scalar, atunci cand exista mai mult de o coloana de potrivire, folosim un ArrayRef.

In mod similar, sa spunem ca vrem sa actualizam doua campuri, salariu si bonus. Daca dorim sa actualizam mai mult de o coloana, in timp ce anterior am specificat noua valoare folosind un scalar, cand exista mai mult de o coloana de actualizat, folosim un ArrayRef.

De asemenea, sa zicem ca vrem sa facem actualizari doar pentru persoanele din compania

„WidgetCo”. „Bob Jones” apare in ambele companii, deci trebuie sa fim

atenti sa identificam compania, dar nu vrem sa o specificam in

mod repetat pentru fiecare rand. Parametrul „unde” poate fi utilizat pentru a specifica

o conditie globala, de exemplu unde => {company => ‘WidgetCo’}.

Un alt lucru pe care am putea dori sa il facem este sa actualizam o coloana cu aceeasi

valoare pentru toate randurile actualizate. Asadar, aici am putea actualiza coloana „last_updated”. Mai degraba decat trebuie sa specificam

aceeasi valoare inca o data, putem folosi parametrul „set”.

Parametrul „set” are un HashRef ale carui taste sunt coloanele care trebuie

actualizate. Valorile specificate ca scalare sunt aplicate asa cum este, in timp ce valorile

specificate ca ScalarRef specifica SQL literal. In exemplul nostru, vom seta coloana „last_updated” la rezultatul apelarii functiei SQL NOW ().

Deci, pentru a efectua aceste modificari, am putea suna la mai multe date actualizate astfel:

multi_update ($ dbh, \% table_info, key_columns => [qw {first_name last_name}], value_columns => [qw {bonus salariu}], actualizari => [[[” Bob ‘,’ Jones ‘], [1125, 110 ]], [[‘Jane’, ‘Smith’], [1200, 130]], [[‘Jane’, ‘Austin’], [1200, 130]], [[‘Frank’, ‘Johnson’], [1125, 110]], [[‘John’, ‘Gray’], [1150, 110]],], unde => {company => ‘WidgetCo’}, set => {last_update => \ ‘ACUM ( ) ‘});

Pe langa faptul ca accepta conditii globale explicite (folosind parametrul „unde”), DBIx :: MultiRow va analiza actualizarile pentru a cauta eventualele cazuri in care valorile care urmeaza sa fie potrivite intr-o coloana sa fie toate aceleasi si le vor imparti pe acestea in conditii globale din clauza WHERE. Deci , daca se intampla sa fie orice conditii globale implicite, atunci acestea vor fi identificate si simplificate , oricum.

In mod similar, DBIx :: MultiRow va analiza actualizarile pentru a cauta eventualele cazuri in care valorile care trebuie sa fie SET pentru o coloana sunt toate aceleasi si le va descompune in sub-clauze simple din clauza SET.

Inca un lucru care merita o explicatie este parametrul prenume \% table_info. Pentru a putea crea in mod fiabil tabele temporare si a face interogari SQL unde nu pot fi deduse tipurile de parametri legati, DBIx :: MultiRow impune apelantului sa furnizeze informatii despre tabela tinta. Deci in cazul tabelului cu sase coloane de mai sus,% table_info ar putea fi:

(quoted_table_name => ‘staff’, column => {‘company’ => {dbi_type => SQL_VARCHAR, sql_type => ‘TEXT’}, ‘first_name’ => {dbi_type => SQL_VARCHAR, sql_type => ‘TEXT’}, ‘last_name’ => {dbi_type => SQL_VARCHAR, sql_type => ‘TEXT’}, ‘salariu’ => {dbi_type => SQL_NUMERIC, sql_type => ‘NUMERIC (9,2)’}, ‘bonus’ => {dbi_type => SQL_NUMERIC, sql_type => ‘NUMERIC (9,2)’}, ‘last_updated’ => {dbi_type => SQL_DATETIME, sql_type => ‘TIMESTAMP CU ZONA DE TIMP’}})

Un astfel de aspect de a face lucrurile in acest fel este faptul ca DBIx :: MultiRow nu garanteaza ordinea in care randurile vor fi actualizate, astfel incat, in cazul general, ordinea de blocare a actualizarii de randuri nu este cunoscuta. Daca evitarea blocajelor este o problema pentru apelant, atunci aceasta ar trebui sa fie tratata separat. De exemplu, sub PostgreSQL, acest lucru poate insemna mai intai efectuarea unei instructiuni SELECT … ORDIN CU … PENTRU ACTUALIZARE pentru a achizitiona mai intai blocarile de actualizare a randului intr-o ordine definita.

Dupa implementarea codului pentru a face UPDATE pe mai multe randuri, a fost doar un mic pas pentru a face un lucru similar pentru DELETE. Deci DBIx :: MultiRow include o alta functie multi_delete care poate fi folosita pentru a sterge mai multe randuri:

multi_delete ($ dbh, \% table_info, key_columns => [qw {prenumele prenumele}), sterge => [[‘Bob’, ‘Jones’], [‘Jane’, ‘Smith’], [‘Jane’, ” Austin ‘], [‘ Frank ‘,’ Johnson ‘],], unde => {company =>’ WidgetCo ‘},);

Daca doriti sa incercati DBIx :: MultiRow pentru dvs. insiva, l-am lansat ca parte a angajamentului NET-A-PORTER de a elibera un sursa deschisa valoroasa unde putem. Ar trebui sa-l poti gasi pe Github la https://github.com/hochgurgler/DBIx-MultiRow.

DBIx :: MultiRow – Actualizarea rapida si usoara a mai multor randuri de baze de date

Populare

Afacere proprie, angajat la stat sau angajat la privat? Pro &...

Cum alegi cel mai bun brad de Crăciun?

Afla cum iti poti lansa o mica afacere folosind un spatiu...

Top 10 cele mai supraevaluate filme din toate timpurile

10 regizori care au facut un film de groaza si au...

10 documentare care au schimbat perceptia publicului

Top 10 interviuri controversate cu celebritati din toate timpurile

Top 10 cele mai memorabile momente ale premiilor Oscar

Cum se schimba viata atunci cand intri in cercurile sociale ale...

10 povesti ciudate din lumea rap pe care sa le cunosti