0

My query is related to JOIN logic where multiple tables or other database objects are involved.

Query 1: If there is piece of code containing join logic which involves multiple tables, which columns should we compare first in ON clause? Like, should we consider primary key columns, then foreign key columns and then other required columns Ex.:

c.dept_ID = d.dept_ID
AND d.product_ID = e.product_ID
AND ----
----

Does sequence of these comparisons really matter in case of query performance?

Query 2: Suppose Table alias c has 100 departments or Table alias d has 10 departments, will comparison sequence matters here? Like,

c.dept_ID = d.dept_ID or
d.dept_ID = c.dept_ID

Query 3: If we included view also along with multiple tables, should view related columns compared at last or first?

Saeid Babaei
  • 481
  • 2
  • 16
Anuj Thite
  • 65
  • 7
  • Having indexes or referential links between the relevant fields will have a far greater impact on performance. If you do have performance issues, then use slow request analysis to see which queries are your problem. – Dragonthoughts Dec 17 '19 at 08:17
  • Can you elaborate on "Slow Request Analysis" or provide any weblinks or sample scenarios which will help me in this. Actually, I am in learning phase and have many queries related to this in my mind. – Anuj Thite Dec 17 '19 at 08:21
  • This answer is old, but the basics still remain correct. Please put in some effort to research the problem yourself. https://stackoverflow.com/questions/257906/how-can-i-log-and-find-the-most-expensive-queries – Dragonthoughts Dec 17 '19 at 08:24
  • Thanks Dragonthoughts. Will go through the link which you have shared. – Anuj Thite Dec 17 '19 at 08:32
  • @Dragonthoughts or others : Will you be able to help me on 2nd and 3rd Query ? – Anuj Thite Dec 17 '19 at 08:35
  • There are too many unknowns in your question as stated. For an authoritative answer, the SQL Server version, and the hardware specifications would be extremely significant, however when you are dealing with tiny amounts of data (anything less than 10,000 rows can be regarded as tiny) it is likely that you would not be able to measure the difference, if basic indexes and relational constraints are in place. Badgering for answers, when you don't provide the critical data is also not appropriate. – Dragonthoughts Dec 17 '19 at 16:15

2 Answers2

0

It probably depends on a lot of things that are unknown from this description. Like indexes/statistics, table design, data etc. Write the conditions in the different ways and compare the query pan that the query optimizer produces, to find out

Cedersved
  • 1,015
  • 1
  • 7
  • 21
  • Thanks for the reply Cedersved. Yes. I know there are lot of things we need to considered while applying conditions in JOIN logic. But, Is it good to follow rule like primary key columns, columns on which indexes applied should considered first. – Anuj Thite Dec 17 '19 at 08:12
  • Anybody, who will provide me help me on my Query 2 – Anuj Thite Dec 17 '19 at 08:16
  • Anybody, who will provide me help me on my Query 3 – Anuj Thite Dec 17 '19 at 08:16
  • Also the version of SQL-Server makes a big difference, as the enterprise editions have a lot more reporting and general optimisations, including larger caches. – Dragonthoughts Dec 17 '19 at 16:12
0

In most of the circumstances Order of Join condition do not matter.

What matter is which join will filter how much data ? Whether the join is filtering only require data or more than that.

Query 2 : It do not matter.

Query 3 : Question is not valid.It depend upon example to example.

KumarHarsh
  • 5,046
  • 1
  • 18
  • 22