CODEWITHSUNDEEP
databaset-sqloptimization
Shawn
Shawn

Reputation: 19803

Joining on varchar(50) foreign key slows query

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. >.<

Upvotes: 1

Views: 1269

Answers (4)

n8wrl
n8wrl

Reputation: 19765

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

Upvotes: 2

erikkallen
erikkallen

Reputation: 34401

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.

Upvotes: 2

Shawn
Shawn

Reputation: 19803

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

Upvotes: 2

CodeMonkey1313
CodeMonkey1313

Reputation: 16011

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

Upvotes: 0

Related Questions