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.