Would a SELECT of the original tables using just the required columns help? or it wouldn't make any significant improvement?
Or maybe a view limiting the rows I need before I use the query on my SP?
SELECT * FROM(
SELECT
c.bilamtBILAMT_CT, --
c.cusnoCUSTNUM_CT, --
c.insnoINSNUN_CT, --
CONVERT(int, c.invno)INVNUM_CT, --
c.lnenoLNNUM_CT, --
c.procdePROCDE_CT, --
CASE WHEN ISDATE(c.stdos) = 1
THEN CONVERT(datetime,cast(c.stdos as char(8)),112)
ELSE '1/1/1800' ENDSTDOS_CT --
FROM AR_LOAD_DATA.dbo.clmtrk c WITH(NOLOCK)
UNION ALL
SELECT
h.bilamtBILAMT_CT,
h.cusnoCUSTNUM_CT,
h.insnoINSNUN_CT,
CONVERT(int, h.invno)INVNUM_CT,
h.lnenoLNNUM_CT,
h.procdePROCDE_CT,
ISNULL( CONVERT(datetime,cast(NULLIF(h.stdos, 0) as char(8)),112), '18000101')
STDOS_CT
FROM AR_LOAD_DATA.dbo.cltrkH h WITH(NOLOCK)) Claims
JOIN (SELECT 'A4310', 90,0 UNION ALL
SELECT 'A4311', 90,0 UNION ALL
SELECT 'J3490', 0,5 UNION ALL
SELECT 'L0631', 0,5 UNION ALL
SELECT 'L7900', 0,5)