title: Reduce Hash Match Cost in sql code
body: <p>I have two tables and I am finding the missing values that exist in the second table but not in first table.</p>
  
  <p>Below is my sql code.</p>
  
  <pre><code>SELECT DISTINCT A.Patient
     ,B.Visit
  FROM ( SELECT Patient
             ,Visit
             ,VisitName
          FROM dbo.PatientVisit
       ) A
     ,( SELECT Visit
             ,VisitName
          FROM dbo.Visitlookup v1
      ) B
  WHERE B.Visit NOT IN ( SELECT Visit
                          FROM dbo.PatientVisit p1
                          WHERE A.Patient = p1.Patient )
      AND B.Visit &lt;&gt; A.Visit
  GROUP BY A.Patient
     ,B.Visit
  HAVING B.Visit &lt;&gt; MAX(A.Visit);
  </code></pre>
  
  <p>Explaining my code... </p>
  
  <p>I have two tables one for patient visit and another the visit lookup. I have patient visit from 10 to 70 in the look up table.</p>
  
  <p>Some patients have missed visits in there regular schedule. Say a patient has come for visits 10, 20 , 30, 60 and 70. So 40 and 50 will be there missed visits.</p>
  
  <p>I want to return patients who has missed visits. So I had the patient data having Patient and Visit as Query A and Data from lookup table as Query B. Then i'm taking visits from lookup table that does not exist in Query A. This will give me the missed visits. </p>
  
  <p>The code is working fine and i want to optimize the code. When I checked the execution plan, I found that Hash Match is taking up 46% cost of overall execution.</p>
  
  <p>Below is my execution plan screenshot.</p>
  
  <p><a href="https://i.stack.imgur.com/kIAGp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kIAGp.png" alt="Execution Plan"></a></p>
  
  <p>Is there a way to optimize this code so that the performance can be better?</p>
  
  <p>I have the schema and data in this <a href="http://sqlfiddle.com/#!6/b5824" rel="nofollow noreferrer">sql fiddle</a>.</p>
  
  <p><strong>Updated</strong></p>
  
  <p>I found the reason why hash match takes more cost. Its due to</p>
  
  <p>A Hash Match is likely to pop-up in the following scenarios:</p>
  
  <ul>
  <li>No covering index is available on the tables involved in join, union
  or aggregation operations. </li>
  <li>One large table is being joined against a    much small table, Hash
  Match sometimes proves to be very efficient in    these cases.</li>
  </ul>
  
  <p>As users suggested I created constraints and modified the code. </p>
  
  <p>I created covering index on the patient visit table for visit as included columns and got some changes on the cost. The Hash Match reduced to 20.5% from 46% however the sort operation got a little bit cost from 26% to 37%. but the overall cost came down to 41%</p>
  
  <p><a href="https://i.stack.imgur.com/hHaf8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hHaf8.png" alt="enter image description here"></a></p>