T-SQL

Re: mutare conditii de where in join; performant sau nu ?

B_gd_n[ ]Sahlean — Mon, 10 Oct 2011 12:54:18 GMT

Pentru joncțiuni INNER, un răspuns este oferit de Books Online:
"There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join. ". Este posibil, totuși, ca BOL/MSDN să facă referire la rezultatele joncțiunii și nu la performanță.

Pentru jocțiunile INNER se pot face următoarele teste care demonstrează că (în principiu) nu există nici un câștig/o penalizare (cel puțin în SQL Server 2008) dacă scriu condiția în WHERE sau în ON.

Versiune SQL Server: 2008
Bază de date: AdventureWorks2008
Interogări utilizate:
-- Test 1
PRINT  '***** Q1.1 *****'
SELECT  COUNT(h.AccountNumber)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE  d.SpecialOfferID = 2;

PRINT  '***** Q1.2 *****'
SELECT  COUNT(h.AccountNumber)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND    d.SpecialOfferID = 2;

-- Test 2
PRINT  '***** Q2.1 *****'
SELECT  COUNT(*)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE  h.SalesPersonID = 277;

PRINT  '***** Q2.2 *****'
SELECT  COUNT(*)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND    h.SalesPersonID = 277;

-- Test 3
PRINT  '***** Q3.1 *****'
SELECT  COUNT(*)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE  h.SalesPersonID = 277
AND    YEAR(h.OrderDate) = 2001;

PRINT  '***** Q3.2 *****'
SELECT  COUNT(*)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND    h.SalesPersonID = 277
AND    YEAR(h.OrderDate) = 2001;

PRINT  '***** Q3.3 *****'
SELECT  COUNT(*)
FROM  Sales.SalesOrderHeader h
INNER JOIN  Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND    h.SalesPersonID = 277
WHERE  YEAR(h.OrderDate) = 2001;

Rezultate SET STATISTICS IO (panoul "Messages"): identice
Planurile de execuţie: identice

Re: mutare conditii de where in join; performant sau nu ?

tebbaerty — Thu, 29 Sep 2011 11:11:48 GMT

Cred ca m-am lamurit.

Acum ca am citit si http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/ am inteles de fapt ca la executie se muta cum este mai convenabil filtrarea.

Re: mutare conditii de where in join; performant sau nu ?

tebbaerty — Thu, 29 Sep 2011 10:07:19 GMT

Diana:
Foloseste profiler-ul si vezi de exemplu numarul de "reads" in cele doua cazuri.
Alte "good practices", de data asta pentru acuratetea rezultatelor, gasesti la nr. 6 din
http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

am folosit set statistics io on iar rezultatele au fost aceleasi in ambele cazuri descrise de mine anterior :(.

Re: mutare conditii de where in join; performant sau nu ?

tebbaerty — Thu, 29 Sep 2011 10:02:07 GMT

Salut,

Mersi pentru recomandare si explicarea ordinii.

Nelamurirea mea era legata de plasarea conditiilor.
Voi cauta in carte mai multe detalii dar ca si exermplu pragmatic ar fi vorba de :

select
*
from Document
inner join DocumentDetail on Document.DocumentId = DocumentDetail.DocumentId
inner join InvoiceDetail on DocumentDetail.DocumentDetailId = InvoiceDetail.InvoiceDetailId
where
Document.DocumentId = 1
option(force order) -- pentru a nu fi reordonat querry-ul

Aici s-ar face joinul dintre toate DocumentDetail cu InvoiceDetail ( pe conditia de on) si daca ar fi 100.000 atunci ar fi match-uite toate, dupa care se filtreaza cu conditia de where si din cele 100.000 se ia doar inregistrarea relevanta.

select
*
from Document
inner join DocumentDetail on Document.DocumentId = DocumentDetail.DocumentId and Document.DocumentId = 1
inner join InvoiceDetail on DocumentDetail.DocumentDetailId = InvoiceDetail.InvoiceDetailId
option(force order) -- pentru a nu fi reordonat querry-ul

Teoretic acum ar trebui sa se faca joinul dintre DocumentDetail si InvoiceDetail doar cu un match pe inregistrarea relevanta (dupa ce se aplica conditia de on dintre Document si DocumentDetail se foloseste inputul si se merge mai departe doar cu 1 inregistrare in loc de 100.000 cum ar fi fost in cazul anterior).

Intrebarea este daca al 2-lea caz, intradevar va citi din InvoiceDetail doar 1 inregistrare in loc de 100.000

Mersi,

Re: mutare conditii de where in join; performant sau nu ?

Diana — Thu, 29 Sep 2011 09:47:25 GMT

Foloseste profiler-ul si vezi de exemplu numarul de "reads" in cele doua cazuri.
Alte "good practices", de data asta pentru acuratetea rezultatelor, gasesti la nr. 6 din
http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Re: mutare conditii de where in join; performant sau nu ?

katalyn — Thu, 29 Sep 2011 09:28:55 GMT

Iti recomand cartea "Inside Microsoft® SQL Server® 2008: T-SQL Querying" de Itzik Ben-Gan unde ordinea de executare a interogarilor este explicata detaliat.

Ca idee ordinea este:
FROM, JOIN, APPLY si ON : JOIN-urile sunt executate si aplicate primele filtre (ON)
WHERE: Este aplicat al 2-lea set de filtre
GROUP BY si functiile de agregare: Se realizeaza gruparile si se calculeaza agregarile
HAVING: Al 3-lea set de filtre este aplica (se aplica filtre si peste agregari)
TOP: Ultima filtrare este aplicata. Se returneaza doar X randuri (inregistrari). TOP este executat inainte de ORDER BY
UNION si SELECT: Rezultatele interogarilor sunt concatenate si sunt selectate coloanele returnate de interogarea finala
ORDER BY: Se ordoneaza intregul set de date
FOR XML: Rezultatul este convertit la formatul XML.

http://www.amazon.com/Inside-Microsoft%C2%AE-Server%C2%AE-2008-Pro-Developer/dp/0735626022

mutare conditii de where in join; performant sau nu ?

tebbaerty — Thu, 29 Sep 2011 08:29:34 GMT

Salut,

Corectati-ma daca gresesc dar ordinea de executie a componentelor unui statement este:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
DISTINCT
TOP

In cele ce urmeaza ne intereseaza primele 2.

Am auzit la multe persoane ca un best practice este sa muti din conditiile de where in conditiile de join (frecvent la cele cu inner join, la cele cu left join trebuie sa fii mai atent deoarece poti obtine un efect nedorit ) pentru ca mai intai se executa FROM si cu cat cu cat micsorezi mai mult datele cu atat mai putine readuri din page file-uri se fac.

Cum as putea verifica, in mod pragmatic, ca acest lucru este adevarat ?
Eu am impresia ca, teoretic conteaza si face sens, dar practic nu prea conteaza.

Daca aveti vreun blog, vreun link, orice informatie este binevenita.

Mersi,