SQL View

Transact-SQL
1

Hi,

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.

CREATE VIEW [COM].[OSR_TEMPLATE_Pat_201711]
AS

       WITH TimesliceRecords AS
       (
              SELECT  
                           p.PatientKey,
                           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)
       )
       SELECT
              t.patid,
              t.pracid,
              t.usualgpstaffid,
              t.gender,
              t.yob,
              t.mob,
              
       FROM
              TimesliceRecords t
       WHERE
              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

2

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:

Query 
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

3

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?

4

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.

5

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.