Joining on varchar(50) foreign key slows query

Question

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.

Here's the query in full:

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 >= 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)
      ) >= (7 * 8) -- Eight weeks.
    ) responders
END

The line that slows it down is:

inner join [lookup] l on p.dosing = l.lookupid and l.lookupid = 'da_ncd'

Also, moving it to the where clause has the same effect:

where p.dosing = 'da_ncd'

Otherwise, the query runs almost instantly. >.<

Answer 1

Try creating an index on that table, being sure to properly include that VARCHAR field in the list of fields.

Answer 2

Ah, sorry I figured it out. Patient.Dosing was set as allow nulls. I guess that made it a different sort of index.

Answer 3

For the record, even though the question is answered. Usually things like this happen because the execution plan is changed. Compare the plans in query analyzer.

Answer 4

Another gotcha is data types - if p.dosing and l.lookupid differ - nvarchar vs. varchar, for example, can have a huge impact.