Using a subselect as a derived column is performance time bomb. You have at least a couple of those in there already. You also have a split function in there which raises at least a yellow flag. Does that split function use xml or looping?

Not quite sure why you have the second subselect in there. The one selecting from BLT_BILLM with the huge sum. You already have that table in your query.

See if something like this gets you closer.

SELECT p.period,

m.clnt_matt_code AS [Client Code],

m.matter_name,

o.offc_desc,

'Insolvency' AS matter_code,

Isnull(l.Merged_Source_of_Work_Lender_Desc, k.name) AS Bank,

CASE

WHEN m.matter_code NOT IN ( 'IBR', 'ISR', 'BNK', 'ADV' ) THEN Isnull(mins.original_estimated_fee, 0)

+ Isnull(mins.original_contingent_fee, 0)

ELSE 0

END AS [Original Fee],

Isnull(ii.WalkIn, 'N') AS [Walk In],

em.employee_name,

Isnull(Sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT), 0) AS billings_to_date

FROM bo_live5.dbo.hba_source_bus AS i

INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno

INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno

LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged

INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code

INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno

INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno

INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno

INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc

INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code

AND LEFT(c.prof, 2) IN (SELECT FieldName

FROM dbo._000007_splitlist(@Profit_Center, ','))

INNER JOIN CTE_Mins AS mins

ON mins.matter_uno = m.matter_uno

LEFT JOIN bo_live5.dbo.glm_period_end AS p

ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'

left join (SELECT TOP 1 'Y' as WalkIn FROM bo_live5.dbo.hba_source_bus) ii on ii.matter_uno = i.matter_uno AND ii.comments LIKE '%walk-in%'

GROUP BY p.period

,m.clnt_matt_code

,m.matter_name

,o.offc_desc

,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)

,m.matter_code

,mins.original_estimated_fee

,mins.original_contingent_fee, i.matter_uno ,em.employee_name

, Isnull(ii.WalkIn, 'N')

, Isnull(Sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT), 0)