INNER JOIN and WHERE: what is order execute?

Question

INNER JOIN and WHERE: what is order execute?

nguyennd

SSCommitted

Points: 1881
More actions
May 20, 2010 at 10:54 pm

#224811

Hi all
I have a SQL and I have a qusetion 😀
SELECT
*
FROM
TABLEA
INNER JOIN TABLEB ON ...(ALL join key are index)
WHERE
....
What is order execute?
1. Step1: TABLEA JOIN TABLEB
2. Step2: After JOIN, SQL execute WHERE
OR
1. Step1: SQL execute WHERETABLEA JOIN TABLEB
2. Step2: After WHERE, SQL execute TABLEA JOIN TABLEB
Please help me
http://unitech.vn
FROM
Cartesian Product
ON filter
OUTER
WHERE
GROUP BY
HAVING
SELECT
Expression Evaluation
DISTINCT
TOP
ORDER BY

...is just that: logical. The physical implementation depends on choice made by the optimiser, query processor, and storage engine. You can get reasonably close to the physical order of operations by inspecting the execution plan from a real execution, as Atif mentioned.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

You can see it in the Execution Plan.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

nguyennd (5/20/2010)
What is order execute?
1. Step1: TABLEA JOIN TABLEB
2. Step2: After JOIN, SQL execute WHERE
OR
1. Step1: SQL execute WHERETABLEA JOIN TABLEB
2. Step2: After WHERE, SQL execute TABLEA JOIN TABLEB

Depends on the indexes, the data volume and the decisions the optimiser makes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nguyennd SSCommitted Points: 1881 More actions · Answer 3

if the data volume on 2 table are > 20.000.000 record and index seek for all join and where, what is the way SQL choose?

http://unitech.vn

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 4

i guess the order of processing the query will be in

1. FROM

2. ON

3. OUTER

4. WHERE

5. GROUP BY

6. CUBE | ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10 ORDER BY

11. TOP

is it depends up on the volume and the index structure?

Paul White SSC Guru Points: 150467 More actions · Answer 5

nguyennd (5/21/2010)
if the data volume on 2 table are > 20.000.000 record and index seek for all join and where, what is the way SQL choose?

Generally, the optimiser will try to push suitable predicates before the join. The idea is that joining is an expensive operation, so if the rows can be filtered first, the join will be cheaper.

In practice, the optimiser considers very many different physical implementations based on the logic in your query, statistical information, and the various data access paths (e.g. indexes).

The plan that is chosen is the one the optimiser considers cheapest - but bear in mind that the optimiser does not try to consider all possible combinations, and may transform your written query to an equivalent (but more efficient) construction, either in whole or in part.

In reference to sharath.chalamgari's post: the logical order of processing (thank you Itzik Ben-Gan):

nguyennd SSCommitted Points: 1881 More actions · Answer 6

Thank all for all support:-D

I'm understand:-P

http://unitech.vn