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.
| 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. |
 |
|
|
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 ? |
|
|
|
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. |
|
|
|
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_IDFROM MyTable1 LEFT OUTER JOIN MyTable2 ON T2_ID = T1_IDWHERE 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. |
|
|
|
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_IDFROM MyTable1 LEFT OUTER JOIN MyTable2 ON T2_ID = T1_IDWHERE 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 tableMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
query optimization when join is used