title: Joining on varchar(50) foreign key slows query
body: <p>I have this query which is pretty long, but adding a where clause to it, or joining on a string makes it take an extra 2 seconds to run. I can't figure out why.</p>
  
  <p>Here's the query in full:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[RespondersByPracticeID]
      @practiceID int = null,
      @activeOnly bit = 1
  AS
  BEGIN
      SET NOCOUNT ON;
      select 
          isnull(sum(isResponder),0) as [Responders]
          ,isnull(count(*) - sum(isResponder),0) as [NonResponders]
          ,isnull((select 
                  count(p.patientID)
              from patient p 
                  inner join practice on practice.practiceid = p.practiceid
                              inner join [lookup] l on p.dosing = l.lookupid and l.lookupid = 'da_ncd'
                  where
                  p.practiceID = isnull(@practiceID, p.practiceID)
                  and p.active = case @activeOnly when 1 then 1 else p.active end
              ) - (isnull(sum(isResponder),0) + isnull(count(*) - sum(isResponder),0)),0)
           as [Undetermined]
      from (  
          select 
              v.patientID
              ,firstVisit.hbLevel as startHb
              ,maxHbVisit.hblevel as maxHb
              , case when (maxHbVisit.hblevel - firstVisit.hbLevel &gt;= 1) then 1 else 0 end as isResponder
              ,count(v.patientID) as patientCount
          from patient p 
              inner join visit v on v.patientid = v.patientid 
              inner join practice on practice.practiceid = p.practiceid
              inner join [lookup] l on p.dosing = l.lookupid and l.lookupid = 'da_ncd'
              inner join (
                  SELECT
                    p.PatientID
                    ,v.VisitID
                    ,v.hblevel 
                    ,v.VisitDate 
                  FROM Patient p
                    INNER JOIN Visit v ON p.PatientID = v.PatientID
                  WHERE
                      v.VisitDate = (
                          SELECT MIN(VisitDate) 
                          FROM Visit 
                          WHERE PatientId = p.PatientId
                       )
              ) firstVisit on firstVisit.patientID = v.patientID
              inner join (
                  select 
                      p.patientID
                      ,max(v.hbLevel) as hblevel
                  from Patient p
                       INNER JOIN Visit v ON p.PatientID = v.PatientID
                  group by
                      p.patientID
              ) MaxHbVisit on maxHbVisit.patientid = v.patientId
          where
              p.practiceID = isnull(@practiceID, p.practiceID)
              and p.active = case @activeOnly when 1 then 1 else p.active end
  
          group by
              v.patientID
              ,firstVisit.hbLevel
              ,maxHbVisit.hblevel
          having
              datediff(
                  d,
                  dateadd(
                      day
                      ,-DatePart(
                          dw
                          ,min(v.visitDate)
                      ) + 1
                      ,min(v.visitDate)
                  )
                  , max(v.visitDate)
              ) &gt;= (7 * 8) -- Eight weeks.
      ) responders
  END
  </code></pre>
  
  <p>The line that slows it down is:</p>
  
  <pre><code>inner join [lookup] l on p.dosing = l.lookupid and l.lookupid = 'da_ncd'
  </code></pre>
  
  <p>Also, moving it to the <code>where</code> clause has the same effect:</p>
  
  <pre><code>where p.dosing = 'da_ncd'
  </code></pre>
  
  <p>Otherwise, the query runs almost instantly. <code>&gt;.&lt;</code></p>