Este tabele temporare necesare, clubul pentru programatori

Cea mai mare parte a literaturii care se recomandă să se abțină de la utilizarea de tabele temporare, pe bună dreptate, susțin că acest lucru poate provoca probleme de performanță din cauza blocajului tempdb la crearea unui tabel temporar pentru a crește numărul de operații I / O, folosind un tabel temporar, precum și potențialul de tempdb de blocare, în cazul în care crearea și operațiunile ulterioare au loc cu un tabel temporar într-o singură tranzacție. Și asta să nu mai vorbim de numeroasele probleme pe care SQL Server se confruntă atunci când se ocupă cu tabele temporare - vezi lista de mai jos articolul din Baza de cunoștințe .. Fără a supune îndoielile de mai sus, am de gând să dau câteva argumente în favoarea tabelelor temporare. Eu recunosc că nu folosesc sau să găsească motive pentru a utiliza tabele temporare la nivel mondial, astfel încât nu veți găsi aici o discuție a acestor tabele.

Ceea ce am putea folosi un tabel temp?

Iată câteva motive pentru care le folosesc tabele temporare în munca lor: conservarea rezultatelor cauzate de procedura stocată; reducerea numărului de linii utilizate în compușii; combina date din diferite surse; și înlocuirea cursorului.

Ca cererea dvs. a devenit mai complexe, veți descoperi blocuri repetitive de cod într-o singură cerere sau mai multe cereri. O astfel de reutilizare stimulează crearea unei proceduri stocate care conține cod duplicat, cu apelul la locurile corespunzătoare. Acest lucru poate duce la apariția unui număr mare de proceduri stocate în baza de date, dar reduce în mare măsură de întreținere, deoarece atunci când schimbă funcționalitatea trebuie să modificați codul de doar o interogare, mai degrabă decât multe cereri care nu au fost încă găsite. Eu folosesc aceasta tehnica destul de des, iar acest lucru de multe ori mă obligă să utilizeze un tabel temporar pentru a stoca rezultatele procedurii stocate, deoarece Transact-SQL nu permite utilizarea rezultatelor procedurilor memorate ca un tabel. Acesta este, probabil, principalul motiv pentru utilizarea tabelelor temporare în codul meu.

Am foarte multe ori să combine tabelele de interogare care conțin aproximativ 10, 100 și 20 de milioane de linii, urmată de sortare pentru afișare modificări recente în primul rând al rezultatului. Chiar și cu indici proprii și folosind clauza WHERE pentru a filtra și forțat să utilizeze interogarea indicele de performanță este inacceptabilă (deoarece aplicația cu care lucrez, este folosit în așa fel încât un timp de răspuns acceptabil la o solicitare în secunde), și de multe ori sortarea cauzelor mare pierdere de performanță, precum și un tempdb imens de încărcare. Destul de des, sa dovedit că utilizarea de tabele temporare corespunzătoare pentru fiecare dintre tabelele permanente îmbutelierii filtrate folosind oferă date în cazul în care, înainte de a face conexiunea și sortarea, astfel încât a crescut performanța de interogare, pot să-l pună în aplicare, de fapt, fără a avea grija de performanță sau impactul pe baza de date tempdb. Mai jos este o cerere foarte simplu, a spus arată.

SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
DE LA Table1 dbo.table1
INTERIOARĂ TE dbo.table2 Table2
ON table1.numBillID = table2.numBillID
INTERIOARĂ TE dbo.table3 Tabelul 3
ON table2.numBillDtlID = table3.numBillDtlID
UNDE table1.numCustID = '5555'
ȘI table2.strPhoneNumber = '5555555555'
ȘI table3.strPhoneNumberCalled = '1234561234'
COMANDA PRIN table3.dtmCalled DESC

(Această interogare nu se potrivește cu orice sistem, orice interogare existentă în compania Verizon. A fost creat pentru a prezenta o anumită problemă pe bază ipotetică a datelor referitoare la telecomunicații.)

Noua interogare:

(Îmi place să numesc un tabel temporar numit procedură stocată care creează, așa că am putea sorta rapid orice probleme în tempdb, asociată cu utilizarea de tabele temporare.)

CREATE TABLE # tquery2a
(Multiplecolumns tipuri de date)

CREATE TABLE # tquery2b
(Mulitplecolumns tipuri de date)

INSERT INTO # tquery2a
coloane SELECT DIN dbo.table2 UNDE table2.strPhoneNumber = '5555555555'

INSERT INTO # tquery2b
coloane SELECT DIN dbo.table3 UNDE table3.strPhoneNumberCalled = '1234561234'

SELECT table1.numCustID, # tquery2a.strPhoneNumber, # tquery2b.strPhoneNumberCalled
DE LA Table1 dbo.table1
INTERIOARĂ join # tquery2a # tquery2a
ON table1.numBillID = # tquery2a.numBillID
INTERIOARĂ join # tquery2b # tquery2b
ON # tquery2a.numBillDtlID = # tquery2b.numBillDtlID
UNDE table1.numCustID = '5555'
ORDER BY # tquery2b.dtmCalled DESC

Vrei sa - crezi sau - nu, dar această metodă este mult mai rapid decât cererea inițială, mai ales atunci când există o propunere ORDER BY.

Ultimul argument în favoarea unui tabel temporar - un cursor de înlocuire. Nu-mi place cursoare și să îndemne să facă tot posibilul pentru a înlocui mouse-ul (deși doriți să evaluați performanța deciziilor dumneavoastră în ceea ce privește performanța cursorului). Un truc Eu folosesc este de a imita principalul motiv pentru care, de obicei, se bazează peste - itera progresiva prin setul de rezultate și de a efectua o acțiune pe baza datelor din acel rând. Mai jos - interogare scurt care demonstrează această logică prin obținerea numele tuturor tabelelor de utilizator și executa sp_spaceused pe fiecare masă.

SET NOCOUNT ON
DECLAR @lngTabCount INTEGER
DECLAR @lngLoopCount INTEGER
DECLAR @strTabName SYSNAME

CREATE TABLE #tTables
(
numID INTEGER IDENTITATE (1,1)
,strTableName SYSNAME
)

INSERT INTO #tTables (strTableName)
Numele SELECT FROM WHERE dbo.sysobjects xtype = 'U'

SET @lngTabCount = @@ ROWCOUNT
SET @lngLoopCount = @lngTabCount

ÎN TIMP CE @lngLoopCount <> 0
BEGIN
SET @strTabName = (SELECT strTableName FROM WHERE #tTables numID = @lngLoopCount)
EXEC sp_spaceused @strTabName
SET @lngLoopCount = @lngLoopCount - 1
END

#tTables DROP TABLE
GO

O astfel de acțiune fără a cursorului săgeată și probleme de performanță aferente.

Cum puteți lucra fără utilizarea de tabel temporar?

Acum, că v-am arătat câteva situații în care este justificată utilizarea de tabele temporare, hai să vorbim despre ceea ce se poate face pentru a evita, eventual, utilizarea tabelei temporare.

Fiecare dintre aceste metode a fost discutată ca o posibilă soluție alternativă la utilizarea unui tabel temporar. Principalul lucru pe aici - pentru a evalua metode alternative pentru a determina dacă se poate înlocui în mod rezonabil utilizarea de tabele temporare, care sunt, de obicei create prin forța de obicei. Pe măsură ce creați tehnici sau trucuri diferite pe care le utilizați tabele temporare mai puțin și mai puțin, și chiar vor experimenta disconfort atunci când se utilizează un tabel temporar, considerând că cu siguranță există o cale de a face fără ea.

Dacă utilizați tabele temporare, pentru a optimiza utilizarea acestora.

În cazul în care situația impune utilizarea unui tabel temporar, atunci ar trebui să faceți câteva lucruri pentru a îmbunătăți performanța. În primul rând, tocmai pentru că este un tabel temporar, nu încercați să puneți toate coloanele și toate rândurile din tabelul permanent, dacă nu aveți nevoie de ele. Se filtrează datele care vin în tabelul dvs. temporar pentru a limita numărul minim de coloane și rânduri necesare. În al doilea rând, nu utilizați SELECT INTO pentru a crea tabele temporare. SELECT INTO în cod trebuie evitată cu orice preț, din cauza blocaj, el impune pe obiecte de sistem pentru o lungă perioadă de timp pentru a decide cum să construiască un tabel. Fa-ti timp pentru a scrie un script pentru a crea un tabel temporar și INSERT individuale în situațiile la finalizarea acestuia. Eu cred că puteți utiliza SELECT INTO, în cazul în care acesta include o unde 1 = 0, pentru a crea un tabel în cel mai rapid mod, dar nu o fac doar pentru a salva câteva apăsări de taste. În al treilea rând, uita-te la modul în care utilizați tabele temporare pentru a evita recompilarea o procedură stocată. Am explica acest lucru în detaliu în optimizarea articol procedură stocată recompiles. disponibil pe site-ul meu. În al patrulea rând, verificați necesitatea unui indice de cluster pe masă temporară. Dacă setul de date este mare, indicele grupat va accelera funcționarea timp masa de prelevare a probelor, dar trebuie să cântărească costul de performanță pentru a crea indexul și introduceți date într-un tabel cu un indice de cluster. Aceasta este una dintre acele metode care trebuie să fie verificate înainte de decizia privind alegerea indicelui în ambele versiuni cu cel mai mare set de date posibile, care, în opinia dumneavoastră, va fi plasat într-un tabel temporar. În cele din urmă, știm că atunci când se termină executarea procedurii stocate și completează conexiunea, tabelul temporar este scăzut, dar de ce-l păstrați, în cazul în care nu mai este necesară. În cazul în care codul dvs. creează și utilizează un tabel temporar, iar apoi trece la alte lucruri care nu utilizează acest tabel, scoateți-l în codul. Aceasta eliberează resurse pentru alte obiecte tempdb. Am șterge tabelul de la finalul procedurii memorate chiar și atunci când se termină conexiunea, dar pentru a evita orice probleme care pot apărea din cauza unor erori necunoscute.

Deși tabelele temporare (în opinia mea) mult mai bine decât cursoare, utilizarea lor nu produce degradarea performanței. Acest articol prezintă pe scurt o serie de motive pentru a utiliza tabele temporare, și mai multe metode alternative. Ar trebui luată o decizie concretă în funcție de situație. Verificați solicitarea utilizând abordări alternative înainte de a crea un tabel temporar, și să evalueze performanța de tabele temporare; atunci puteți face o alegere informată. Sunt ferm din punctul de vedere, pe baza lecțiilor învățate (și acest lucru, chiar dacă am scrie acest articol), care înainte de a aplica orice citit într-o carte sau pe un site, trebuie să-l verificați în mai multe moduri diferite. Fa acest lucru și abilitățile dumneavoastră de a utiliza Transact-SQL va continua să crească până la un nivel la care veți avea întotdeauna mai multe moduri diferite de a crea o interogare.