December 14, 2017 at 4:08 am
Hi All,
Trying to understand how does below sql stmt gets executed. The reason why I am asking is , I can do inline queries if filters conditions are executed first.
Here in the below sample sql stmt and trying to understand how does the execution, happen? below is my undertanding. Correct me, if I am wrong.
select
t1.c1,
t2.c2,
t3.c4
from t1 inner join t2 on t1.c1 = t2.c2
inner join t3 on t1.c3 = t3.c3 and t3.c4 = t2.c4 --join condition
where t1.c5 in (10,20,30) --filter condition
and t2.c4 in (Select cval from #tmp);
order by t2.c2
- table t1 gets loaded into memory
- table t2 gets loaded into memory
- table t3 gets loaded into memory
- join happens in between t1 and t2
- join happens between ( t1+t2 result) & t3
- next filtering happens i,e t1.c5 and t2.c4 predicates gets executed
- Finally, sorting i,e, order by gets executed and the end result is sent back to client app.
or else, filters happens first before join operation???? please confirm,
other thing is that, Does it make a difference if I keep the table which is returning/filtering rows and then doing a join to other joining tables?
Thanks,
Sam
December 14, 2017 at 4:14 am
If you mean, what order is the Statement processed in, have a look at SELECT (Transact-SQL); specifically the section on Logical Processing Order.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 14, 2017 at 4:19 am
Indeed, and regarding tables getting loaded into memory, that doesn't necessarily happen at all. If the query optimizer finds a suitable index, that index will be used and the table won't necessarily be loaded into memory. Even the whole index will not necessarily be used if a seek operation is possible.
John
December 14, 2017 at 4:30 am
Yes, there's a lot of variables to consider. Although the Logical Processing page gives how queries work for the most part, it's not necessarily always how it works (and the page tells you that). Another consider, such as if the query is also referencing a linked server, which can introduce it's own "fun and game". 😉
Every query will run different, as they are all different. (unsurprisingly). This is why so much time is taken to not only making sure a query's result is correct, but efficient. This is why getting the indexes right is so important. As John mentioned, a seek operation makes things a lot easier for the Data Engine to do the work, rather than having to do an Index Scan, or Key Lookup.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 14, 2017 at 5:02 am
I think what the OP is asking is what is the precedence for the various clauses in the SQL statement and it is a valid question.
The example given will be the same in every version of SQL:
tables will be joined based on JOIN criteria THEN filters will be applied from the WHERE criteria THEN the results will be sorted from the ORDER BY criteria.
If you included a SELECT TOP N then this is where you could get differences.
Microsoft will ORDER BY THEN TOP N
Oracle will TOP N THEN ORDER BY
This means that in Oracle world you need to ORDER BY in a sub-query and then TOP N in the outer query which never seemed to make much sense to me - but then I don't own a $4m racing Catamaran so what do I know 🙂
Oracle also doesn't have a BIT or BOOLEAN you have to use TINYINT with a contraint of 0 or 1 - go figure
December 14, 2017 at 8:24 am
vsamantha35 - Thursday, December 14, 2017 4:08 AMHi All,Trying to understand how does below sql stmt gets executed. The reason why I am asking is , I can do inline queries if filters conditions are executed first.
Here in the below sample sql stmt and trying to understand how does the execution, happen? below is my undertanding. Correct me, if I am wrong.select
t1.c1,
t2.c2,
t3.c4
from t1 inner join t2 on t1.c1 = t2.c2
inner join t3 on t1.c3 = t3.c3 and t3.c4 = t2.c4 --join condition
where t1.c5 in (10,20,30) --filter condition
and t2.c4 in (Select cval from #tmp);
order by t2.c2- table t1 gets loaded into memory
- table t2 gets loaded into memory
- table t3 gets loaded into memory
- join happens in between t1 and t2
- join happens between ( t1+t2 result) & t3
- next filtering happens i,e t1.c5 and t2.c4 predicates gets executed
- Finally, sorting i,e, order by gets executed and the end result is sent back to client app.or else, filters happens first before join operation???? please confirm,
other thing is that, Does it make a difference if I keep the table which is returning/filtering rows and then doing a join to other joining tables?
Thanks,
Sam
If it is the logical processing order you are looking for, Microsoft documents it as:
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
There can be some queries that stray from that general order but that wouldn't be too common. You can find Microsoft's documentation on this in the following:
SELECT (Transact-SQL)
Sue
December 14, 2017 at 9:08 am
Thanks aaron.I want to understand the basics of how execution happens w.r.t to the query I shared.
However, got to know other things. Thank you all.
December 14, 2017 at 9:25 am
You should also know that you can use CTEs to partially control the order of processing. The entire CTE has to be evaluated before it can be used later in the query.
Drew
NB: These statements only apply to the logical processing order.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 14, 2017 at 10:36 am
I suggest that you read this article from Itzik Ben-Gan.
http://www.itprotoday.com/microsoft-sql-server/logical-query-processing-what-it-and-what-it-means-you
You also need to learn about execution plans and the different operators. You'll understand that tables are not fully loaded into memory if there's no need to do it. Indexes, statistics, resources, and complexity can change the way a query executes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply