Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query optimization when join is used

Author  Topic 

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2015-03-28 : 09:54:40
hi team,

during some sql server developer interview i got a question where there is two table having millions of records and there is join applied on primary key columns of bothe the table.

The interviewer asked me how to optimize this query what should be your approach to tune this query.

in the reply i told that when there is join condition based on primary key columns then the clustered index seek should be there and this is best possible thing we can have in execution plan and along with that there is merge join should be there and there is no such scope of improve the performance.


Please let me know as a interviewer what you are expecting from a candidate or what you answer as a candidate.








prithvi nath pandey

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-28 : 15:53:14
For the most part you are right. Primary key does not necessarily have to be the clustering key although that is the default. If the join is not an inner join, one or both tables will need to be scanned. Other things to do are to make sure the indexes are maintainedand that that you are selecting only the required rows and columns.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-29 : 10:54:05
quote:
Originally posted by James K

If the join is not an inner join, one or both tables will need to be scanned.



That doesn't feel right ... What if there is a perfectly valid Foreign Key between two tables [and PKey / suitable Indexes) but i'm too lazy to check (or worried some twit might drop it in future!) so I code it as a LEFT OUTER JOIN ?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-30 : 08:39:34
Kristen, I was referring to the generic case with no where clause, in which case, all the rows in the preserved tables (LEFT table in the case of a LEFT join) will need to be returned.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 04:14:39
So this will scan (because it is OUTER JOIN), even if index available on both tables and the columns are all declared as NOT NULL?

SELECT T1_ID, T2_ID
FROM MyTable1
LEFT OUTER JOIN MyTable2
ON T2_ID = T1_ID
WHERE T1_ID = 1234

Sorry if I'm missing the point! but we use LEFT OUTER JOIN a lot just in case a foreign key has been dropped by accident and records would otherwise be missing from the query.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-01 : 08:14:34
quote:
Originally posted by Kristen

So this will scan (because it is OUTER JOIN), even if index available on both tables and the columns are all declared as NOT NULL?

SELECT T1_ID, T2_ID
FROM MyTable1
LEFT OUTER JOIN MyTable2
ON T2_ID = T1_ID
WHERE T1_ID = 1234

Sorry if I'm missing the point! but we use LEFT OUTER JOIN a lot just in case a foreign key has been dropped by accident and records would otherwise be missing from the query.


I do not think so. Table scan may happen only when you do not have index on right table and do not filter any records using indexed column of left table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -