Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Select - the inner execution sequence
Message
 
 
General information
Forum:
Microsoft SQL Server
Category:
Other
Miscellaneous
Thread ID:
00825026
Message ID:
00825854
Views:
22
Kamil,

There're three separate issues here:
#1 How to build a query that produces desired result
#2 What execution plan query optimizer would use to process the query
#3 How optimize the query

#1. It doesn't matter if JOIN or WHERE will execute first, the result will be the same. Here're two queries that, I belive, are equivalent of your query
SELECT Pc1.*, Place1.User_Na, Place1.Dep_Pk,
		Dep.Na AS Dep_Na
	FROM Pc1
	JOIN Place1 ON Pc1.Place1_Pk=Place1.pk
	JOIN Dep ON Dep.pk=Place1.Dep_Pk
	WHERE Place1.pk IN (SELECT Place1_Pk FROM Place1_Po1))
	ORDER BY User_Na

SELECT Pc1.*, Place1.User_Na, Place1.Dep_Pk,
		Dep.Na AS Dep_Na
	FROM Pc1
	JOIN Place1 ON Pc1.Place1_Pk=Place1.pk
	JOIN Dep ON Dep.pk=Place1.Dep_Pk
	JOIN Place1_Po1 ON Place1_Po1.Place1_Pk = Place1.pk
	ORDER BY User_Na
#2. I covered in my previous message. Most likely the two queries from #1 will produce the same excution plan in SQL Server. Anyway, the execution plan selected doesn't affect the query result only performance.

#3. SQL Server query optimizer does pretty good job in designing most effective execution plan in most cases. It's not recomended to specify optimization hints w/o good reason because most likely the execution plan will be less effective.

>Sergey,
>>Each database engine has it's own query optimizer which >desides how to execute particular query by building an >execution plan.
>But i have to be certain that one and the same SELECT produces the same result through changing server versions.
>In this particular case i have to be sure that Dep is joined to what is left of Place1 after it was joined to Pc1 (see item 3) below). So i guess there should be some basic rules for building result of a SELECT. In other words the sceleton is there but meat is the buisness of the query optimizer.
>
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform