0

I understand that using multiple column for joining will increase data accuracy , but how about performance ?

For example my query below

SELECT tableOne.field FROM tableOne TO WITH(NOLOCK)
INNER JOIN tableTwo TW WITH(NOLOCK) on TW.fieldPK = TO.fieldPK
LEFT JOIN tableThree TT WITH(NOLOCK) TT.fieldFK = TO.fieldFK
   AND TT.fieldTwo = TW.SOMEFIELD

From the query above , by adding AND TT.fieldTwo = TW.SOMEFIELD it can improve the accuracy , but even without it, I still can get my data due to join with primary key, so may I know what is the pros and cons of adding addtional field during JOIN

abc cba
  • 2,563
  • 11
  • 29
  • 50
  • This depends on your indexes. You should compare 2 execution plans to see if there is a difference in your case or not – sepupic Dec 20 '18 at 08:27
  • Same result from both queries? Compare the execution plans. – jarlh Dec 20 '18 at 08:27
  • There is way too little information to answer this. Depending on what data you want, either you **need** the additional condition (to filter out unwanted items), or else you should not add it. Furthermore, what indexes you have on all of the JOIN columns will have big impact on performance. And why are you using `WITH(NOLOCK)` which comes with its own set of potential gotchas? It's definitely not a speed up modifier. – Peter B Dec 20 '18 at 08:39
  • @PeterB Hi, Peter why should not add WITH(NOLOCK) ? and which WITH(NOLOCK) you are saying ? – abc cba Jan 02 '19 at 07:25
  • Easy to find **why you should avoid it UNLESS you *understand* it + you are sure that you *need* it**, e.g. https://blog.entelect.co.za/view/7529/the-effects-of-the-nolock-hint / https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/ / https://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice – Peter B Jan 02 '19 at 09:20

1 Answers1

1

If you run your queries with execution plan (ctrl+m in management studio) you will see the specifics of the different joins, this is a great tool to quickly finding out where your queries hurt the most.

In your case i suspect the cost of index scans will differ. Adding additional fields may change the execution plan and make your queries more efficient, but also it may make it worse as in the screenshot below. Top sample uses PK matching, second uses PK + field.

Indexing

WernerW
  • 792
  • 11
  • 27