SQL View


I have the following SQL View which is working but very slow - please could you advise how can receive the same output more quickly. The select statement needs to be re-designed.


       WITH TimesliceRecords AS
                           p.PatientIdentifier AS patid,
                           p.CPRDPracticeId AS pracid,
                           UsualGPStaffID AS usualgpstaffid,
                           GenderId AS gender,
                           YearOfBirth AS yob,
                           MonthOfBirth AS mob,                      

                     FROM [Committed].[Clinical_Patient] p WITH (NOLOCK)
                           LEFT OUTER JOIN [COMMITTED].[D_Patients_On_Date]('20171101') dp ON p.PatientIdentifier = dp.PatientIdentifier AND p.CPRDPracticeId = dp.CPRDPracticeId
                     WHERE p.RowStartDate <= '20171101' AND COALESCE(p.RowEndDate,'99991231') > '20171101'
                           AND dp.PatientIdentifier IS NULL
                           AND p.IsAcceptable = CONVERT(BIT,1)
              TimesliceRecords t
              t.PatientKey = (SELECT MAX(t1.PatientKey) FROM TimesliceRecords t1 WHERE t.patid=t1.patid)

Without the second SELECT statement to the query it runs instantly. Any advice will be appreciated.

Thank you

If you care about the validity of your results, you should consider loosing the "nolock" feature (see discussion in other threads in this and other forums).

I think this would help you:

select top(1) with ties
      ,p.PatientIdentifier as patid
      ,p.CPRDPracticeId as pracid
      ,UsualGPStaffID as usualgpstaffid
      ,GenderId as gender
      ,YearOfBirth as yob
      ,MonthOfBirth as mob
  from Committed.Clinical_Patient as p
       left outer join Committed.D_Patients_On_Date('20171101') as dp
                    on dp.PatientIdentifier=p.PatientIdentifier
                   and dp.CPRDPracticeId=p.CPRDPracticeId
 where p.RowStartDate<='20171101'
   and (p.RowEndDate>'20171101'
    or  p.RowEndDate is null
   and p.IsAcceptable=convert(bit,1)
   and dp.PatientIdentifier is null
 order by row_number() over(partition by p.PatientIdentifier
                                order by p.PatientKey desc

EDIT: woops, forgot the "with ties" part

What does the table-valued function 'Committed.D_Patients_On_Date' actually do? Why do you have the date parameter passed into that function hard-coded to a specific date?

It looks like you want the view to return one row only - returning the patient data for the patient with the latest patient key that does not exist in the function based on a fixed date. That really doesn't make sense...

Can you outline what you are trying to accomplish - and provider some sample data/tables?

I'd wager that this function gets called many, many times instead of just once. You could try to put it in a WITH construct to force it to only get called once.

It is already in a CTE - I'd wager this is a multi-statement table value function. Rewriting it as a inline-table valued function and changing to an outer apply would probably improve the performance dramatically. Then again, if there is a simpler way to check for existence then moving that check out of the function into an EXISTS might be much better than calling a function.