How to convert a Cross Apply to a Pivot for Performance reasons

67 Views Asked by At

I have a query as shown below. It runs great for the first hundred or so rows but once I start to scale it, it falls flat on its face. After checking the Query Plan, It is what I expected, the cross apply on a TVF is the root cause. The TFV is a very in depth function but on its own, it runs at 0ms. But once I throw it into a Cross Apply, I am getting 18 seconds for 170 records but the strange thing is with 280 records, I still get 18 seconds. I was reading that I could convert the Cross Apply to a Pivot and was wondering how I would do that. Here is the code.

SELECT 
    P.prno_id
    ,C.client_id as [ClientID]
    ,E.enco_id as [Encounter]
    ,C.fname + ' ' + C.lname as [Patient]
    ,PL.prle_listname as [Program]
    ,t.PreDate as [RXDate]
    ,t.RxNumber as [RxNumber] 
    ,CASE WHEN CHARINDEX(t.RxPrice, '$') = 0 THEN '$' ELSE '' END + t.RxPrice as [Price]
    ,t.RXNotes as [Notes]
    ,t.RxStatus as [Status]
FROM Progress_Note P
JOIN Encounter E 
    on E.enco_id = P.enco_id
JOIN client_program CP 
    on CP.clpr_id = dbo.Sundown_func_Get_Admission_Program_By_Encounter(E.enco_id)
JOIN PROGRAM_LEVEL PL 
    on PL.prle_id = CP.prle_id
JOIN Client 
    C on C.client_id = P.client_id
Cross Apply dbo.Sundown_func_Get_HTML_Table_Results_test(P.prno_id)  as t

I have tried looking at indexing but the TVF does not use anything that would need to be indexed, its purely calculations of a Table and coverts it. I really just want to try our PIVOT as a replacement for the Cross Apply.

0

There are 0 best solutions below