Îmbinări SQL Vs SQL Subqueries (performanță)? (Programare, Sql, Performanță, Sql Server 2008, Subquery, A Se Alătura)

Vishal a intrebat.

Doresc să știu dacă am o join ceva de genul acesta –

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

și o subinterogare ceva de genul acesta –

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

Când iau în considerare performanța care dintre cele două interogări ar fi mai rapidă și de ce ?

De asemenea, există vreun moment în care ar trebui să prefer una în locul celeilalte?

Îmi cer scuze dacă acest lucru este prea banal și dacă a mai fost întrebat înainte, dar sunt confuz în această privință. De asemenea, ar fi grozav dacă mi-ați putea sugera instrumente pe care ar trebui să le folosesc pentru a măsura performanța a două interogări. Vă mulțumesc mult!

Comentarii

  • @Lucero, această întrebare este etichetată sql-server-2008, acolo unde postarea pe care o menționezi este etichetată MySql. Poți deduce că răspunsurile vor fi aceleași. Optimizarea performanțelor se face diferit pe cele două RDBMS-uri. –  > Por Francois Botha.
8 răspunsuri
JNK

M-aș AȘTEPTA ca prima interogare să fie mai rapidă, în principal pentru că aveți o echivalență și un JOIN explicit. Din experiența mea IN este un operator foarte lent, deoarece SQL îl evaluează în mod normal ca o serie de WHERE clauze separate prin „OR” (WHERE x=Y OR x=Z OR...).

Totuși, ca și în cazul TUTUROR lucrurilor SQL, kilometrajul dumneavoastră poate varia. Viteza va depinde foarte mult de indici (aveți indici pe ambele coloane ID? Asta vă va ajuta foarte mult…), printre altele.

Singura modalitate REALĂ de a spune cu 100% certitudine care este mai rapidă este să activați urmărirea performanței (IO Statistics este deosebit de utilă) și să le rulați pe ambele. Asigurați-vă că ștergeți memoria cache între două rulări!

Comentarii

    16

  • Am serioase îndoieli cu privire la acest răspuns, deoarece majoritatea SGBD-urilor, cu siguranță SQL Server 2008 și ulterior, traduc subinterogarea cu un singur ID (necorelată, ceea ce înseamnă: fără a face referire la mai multe coloane de interogare exterioară) într-o semi-joncțiune relativ rapidă. De asemenea, după cum s-a menționat anterior într-un alt răspuns, prima, adevărata îmbinare va returna un rând pentru fiecare apariție a ID-ului corespunzător în Dept – acest lucru nu face nicio diferență pentru un ID unic, dar vă va oferi tone de duplicate în alte părți. Sortarea acestora cu DISTINCT sau GROUP BY va fi o altă sarcină de performanță mare. Verificați planurile de execuție în SQL Server Management Studio! –  > Por Erik Hart.
  • Clauza IN ca echivalent al OR se aplică la listele de parametri/valori, dar nu și la subinterogări, care sunt tratate în mare parte ca niște îmbinări. –  > Por Erik Hart.
linuxatico

Ei bine, cred că este o întrebare „Veche, dar de aur”. Răspunsul este: „Depinde!”. Performanțele sunt un subiect atât de delicat încât ar fi prea prostesc să spunem: „Nu folosiți niciodată subqueries, întotdeauna alăturați-vă”. în următoarele link-uri, veți găsi câteva bune practici de bază pe care le-am găsit foarte utile:

Am un tabel cu 50000 de elemente, rezultatul pe care îl căutam era de 739 de elemente.

Interogarea mea la început a fost următoarea:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND p.anno = (
    SELECT MAX(p2.anno) 
    FROM prodotto p2 
    WHERE p2.fixedId = p.fixedId 
)

și a avut nevoie de 7,9 secunde pentru a fi executată.

Interogarea mea la final este următoarea:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND (p.fixedId, p.anno) IN
(
    SELECT p2.fixedId, MAX(p2.anno)
    FROM prodotto p2
    WHERE p.azienda_id = p2.azienda_id
    GROUP BY p2.fixedId
)

și a durat 0.0256s

Bun SQL, bun.

Comentarii

  • Interesant, puteți explica cum s-a rezolvat problema prin adăugarea GROUP BY? –  > Por cozos.
  • Tabelul temporar generat de subinterogare a fost mai mic. Prin urmare, execuția este mai rapidă, deoarece sunt mai puține date de verificat. –  > Por Sirmyself.
  • Cred că în prima interogare aveți o variabilă partajată între interogarea exterioară și subinterogarea, astfel încât pentru fiecare rând din interogarea principală, se execută subinterogarea, dar în a doua interogare subinterogarea se execută doar o singură dată și în acest fel performanța s-a îmbunătățit. –  > Por Ali Faradjpour.
  • Sql server și MySql și …Sql (cu excepția NoSql) sunt atât de asemănătoare în ceea ce privește infrastructura. Avem un fel de motor de optimizare a interogărilor dedesubt care convertește clauzele IN (…) în join (dacă a fost posibil). Dar atunci când aveți un Group by pe o coloană bine indexată (pe baza cardinalității sale), atunci va fi mult mai rapid. Așadar, totul depinde de situație. –  > Por Alix.
  • Sunteți sigur că bufferul era curat? este foarte logic că dacă ați rula ambele interogări una după alta, ar exista o diferență masivă de performanță – -.  > Por Yuval Perelman.
JP Emvia

Performanța se bazează pe cantitatea de date pe care se execută…

Dacă sunt mai puține date, în jur de 20k. JOIN funcționează mai bine.

Dacă datele sunt mai mult de 100k+, atunci IN funcționează mai bine.

Dacă nu aveți nevoie de datele din celălalt tabel, IN este bun, dar este întotdeauna mai bine să alegeți EXISTS.

Am testat toate aceste criterii, iar tabelele au indici corespunzători.

HLGEM

Începeți să vă uitați la planurile de execuție pentru a vedea diferențele în modul în care SQl Server le va interpreta. De asemenea, puteți utiliza Profiler pentru a rula de fapt interogările de mai multe ori și pentru a obține diferențele.

Nu m-aș aștepta ca acestea să fie atât de îngrozitor de diferite, unde puteți obține câștiguri reale și mari de performanță în utilizarea îmbinărilor în loc de subinterogări este atunci când utilizați subinterogări corelate.

EXISTS este adesea mai bun decât oricare dintre aceste două tipuri de interogări, iar atunci când este vorba de interogări la stânga, în care doriți ca toate înregistrările care nu se află în tabelul de interogare la stânga să fie incluse, atunci NOT EXISTS este adesea o alegere mult mai bună.

Lucero

Performanța ar trebui să fie aceeași; este mult mai important să aveți indici corecți și să aplicați clusterizarea corectă pe tabelele dumneavoastră (există câteva resurse bune pe această temă).

(Editat pentru a reflecta întrebarea actualizată)

onedaywhen

Este posibil ca cele două interogări să nu fie echivalente din punct de vedere semantic. Dacă un angajat lucrează pentru mai multe departamente (posibil în întreprinderea pentru care lucrez; recunosc, acest lucru ar implica faptul că tabelul dvs. nu este complet normalizat), atunci prima interogare ar returna rânduri duplicate, în timp ce a doua interogare nu ar face acest lucru. Pentru ca interogările să fie echivalente în acest caz, trebuie să se utilizeze DISTINCT ar trebui să fie adăugat cuvântul cheie SELECT care ar putea avea un impact asupra performanței.

Rețineți că există o regulă generală de proiectare care prevede că un tabel trebuie să modeleze o entitate/clasă sau o relație între entități/clase, dar nu ambele. Prin urmare, vă sugerez să creați un al treilea tabel, de exemplu OrgChart, , pentru a modela relația dintre angajați și departamente.

Arvin Amir

Știu că este o postare veche, dar cred că este un subiect foarte important, mai ales în zilele noastre, când avem peste 10M de înregistrări și vorbim de terabytes de date.

Voi pune și eu greutate cu următoarele observații. Am aproximativ 45M de înregistrări în tabelul meu ([data]) și aproximativ 300 de înregistrări în tabelul [cats]. Am o indexare extinsă pentru toate interogările despre care voi vorbi.

Luați în considerare exemplul 1:

UPDATE d set category = c.categoryname
FROM [data] d
JOIN [cats] c on c.id = d.catid

față de Exemplul 2:

UPDATE d set category = (SELECT TOP(1) c.categoryname FROM [cats] c where c.id = d.catid)
FROM [data] d

Exemplul 1 a durat aproximativ 23 de minute pentru a fi rulat. Exemplul 2 a durat aproximativ 5 minute.

Așadar, aș concluziona că, în acest caz, subinterogarea este mult mai rapidă. Bineînțeles, nu uitați că folosesc unități SSD M.2 capabile de i/o la 1 GB/sec (asta înseamnă bytes, nu biți), astfel încât și indexurile mele sunt foarte rapide. Așadar, acest lucru poate afecta și vitezele în cazul dvs.

Dacă este vorba de o curățare unică a datelor, probabil că cel mai bine este să o lăsați să ruleze și să se termine. Eu folosesc TOP(10000) și văd cât timp durează și înmulțesc cu numărul de înregistrări înainte de a mă confrunta cu interogarea mare.

Dacă optimizați bazele de date de producție, vă sugerez cu tărie să preprocesați datele, adică să folosiți declanșatori sau job-broker pentru a actualiza înregistrările asincrone, astfel încât accesul în timp real să recupereze date statice.

Snekse

Puteți utiliza un Explain Plan pentru a obține un răspuns obiectiv.

Pentru problema dvs, un filtru Exists ar avea probabil cea mai rapidă performanță.

Comentarii

  • „un filtru Exists ar avea probabil cea mai rapidă performanță” – probabil că nu, cred, deși un răspuns definitiv ar necesita testarea pe baza datelor reale. Filtrele Exists sunt probabil mai rapide în cazul în care există mai multe rânduri cu aceleași valori de căutare – astfel, un filtru exists ar putea funcționa mai repede dacă interogarea ar verifica dacă au fost înregistrați și alți angajați din același departament, dar probabil că nu și în cazul în care se face o căutare într-un tabel de departamente. – utilizator359040
  • Ar rula mai lent în acest ultim scenariu? –  > Por Snekse.
  • Depinde de optimizator – în anumite circumstanțe, ar putea, dar în mod normal m-aș aștepta la o performanță foarte asemănătoare. – user359040