Here is the entire beast. This is more than just these two with a UNION ALL. That query is wrapped in another query. Your comments about the origin of this query seems a little odd. One time you said it was a third party app and can't be changed but then you said it is from entity frameworks. Not sure what the reality is but it doesn't really matter.

Yes the code is in dire need of a complete overhaul but apparently that isn't possible. You need to look at the indexes on these tables. There are 4 missing indexes listed in the original execution plan you posted. Now I would never suggest you blindly implement those suggestions but it does demonstrate there are some index issues going on here. I am not going to speculate on why this query hangs as you stated without at least seeing the table structures and index definitions. Anything anyone could suggest without these details is purely a best guess.

For anybody interested here is the entire query reassembled from various posts and formatted for legibility.

SELECT *

FROM (

--this part is for the monthly balances

SELECT lb.loanid

,lb.clientid

,lb.beginning_loan_balance

,lb.sales

,lb.ar_turn

,lb.loan_turn

,lb.minimum_interest

,lb.collections

,lb.beginning_collateral_balance

,lb.float_days

,lb.yield_rate

,lb.minimum_interest_applied

,l.loan_name

,b.customer_description

,lb.total_income

,lb.ar_balance

,lb.total_other_fees

FROM ef_monthly_loan_balances lb

,ef_loan_master l

,ef_borrowers b

WHERE b.customer_id = l.client_id

AND lb.loanid = l.loan_id

AND lb.created = '2014-07-31'

AND isnull(NULL, lb.clientid) = lb.clientid

AND b.customer_id IN (

'CLIENT_20070910162652'

,'CLIENT_20140807133527'

,'CLIENT_ADVA'

,'CLIENT_AIRT'

,'CLIENT_AISS'

,'CLIENT_ALEX'

,'CLIENT_ALIA'

,'CLIENT_ALUR'

,'CLIENT_ARIK'

,'CLIENT_20140314130938'

,'CLIENT_AVIX'

,'CLIENT_AXIS'

,'CLIENT_ATEC'

,'CLIENT_BARC'

,'CLIENT_BOLI'

,'CLIENT_20140207141829'

,'CLIENT_CAND'

,'CLIENT_CARD'

,'CLIENT_CARG'

,'CLIENT_CENT'

,'CLIENT_CLEA'

,'CLIENT_SERV'

,'CLIENT_CPRI'

,'CLIENT_CROS'

,'CLIENT_CROW'

,'CLIENT_DATE'

,'CLIENT_DERV'

,'CLIENT_DEUC'

,'CLIENT_20130920141553'

,'CLIENT_20130802104845'

,'CLIENT_20140812120129'

,'CLIENT_20140331140417'

,'CLIENT_DYET'

,'CLIENT_EVAF'

,'CLIENT_20130822155022'

,'CLIENT_EVER'

,'CLIENT_20130930084836'

,'CLIENT_GENE'

,'CLIENT_GRAN'

,'CLIENT_GRAP'

,'CLIENT_20140905161717'

,'CLIENT_IMGL'

,'CLIENT_VIGO'

,'CLIENT_INFI'

,'CLIENT_20140905145423'

,'CLIENT_20130717105328'

,'CLIENT_LATI'

,'CLIENT_HAWK'

,'CLIENT_LONG'

,'CLIENT_LUMA'

,'CLIENT_MECH'

,'CLIENT_MEDI'

,'CLIENT_20140604092610'

,'CLIENT_MERC'

,'CLIENT_METC'

,'CLIENT_NEAR'

,'CLIENT_20140306110224'

,'CLIENT_NOLA'

,'CLIENT_20140425161116'

,'CLIENT_OFFT'

,'CLIENT_PACI'

,'CLIENT_PAGE'

,'CLIENT_20140716101312'

,'CLIENT_PLAN'

,'CLIENT_PIMA'

,'CLIENT_PCPI'

,'CLIENT_PRES'

,'CLIENT_PRIM'

,'CLIENT_PROL'

,'CLIENT_PROT'

,'CLIENT_PROV'

,'CLIENT_PSCM'

,'CLIENT_PSII'

,'CLIENT_PACK'

,'CLIENT_20140123131930'

,'CLIENT_R3ST'

,'CLIENT_RAZO'

,'CLIENT_RELI'

,'CLIENT_RMJA'

,'CLIENT_ROSS'

,'CLIENT_SOSN'

,'CLIENT_SUMM'

,'CLIENT_20130917164048'

,'CLIENT_TELE'

,'CLIENT_20130828124913'

,'CLIENT_TOPN'

,'CLIENT_TOYS'

,'CLIENT_TRUC'

,'CLIENT_UNIQ'

,'CLIENT_20140509145825'

,'CLIENT_VETE'

,'CLIENT_VIVA'

,'CLIENT_20131105131822'

,'CLIENT_TREN'

,'CLIENT_YSNI'

,'CLIENT_20140321102457'

,'CLIENT_ZEON'

,'CLIENT_ZOOB'

)

AND (

l.participation = 'Y'

OR l.participation = 'N'

)

UNION ALL

--this part is for the current month if we don't have the monthly balances yet

SELECT lb.loanid

,lb.clientid

,lb2.beginning_loan_balance AS beginning_loan_balance

,SUM(lb.sales) AS sales

,NULL AS ar_turn

,NULL AS loan_turn

,0 AS minimum_interest

,SUM(lb.collections) AS collections

,lb1.previous_collateral_value AS beginning_collateral_balance

,MAX(lb.float_days) AS float_days

,

--using formula for the yield rate

MAX((

lb.interest_rate + ISNULL(lb.default_rate, 0) + (

CASE

WHEN ISNULL(lb.deemed_rate_floor, 0) > ISNULL(lb.index_rate, 0)

THEN ISNULL(lb.deemed_rate_floor, 0)

ELSE ISNULL(lb.index_rate, 0)

END

) + lb.admin_rate

) * (

CASE

WHEN lb.float_days > 0

THEN (1.05 + 0.05 * lb.float_days)

ELSE 1

END

)) AS yield_rate

,'N' AS minimum_interest_applied

,l.loan_name

,b.customer_description

,SUM(lb.interest_charged + lb.admin_fee_charged) AS total_income

,NULL AS ar_balance

,SUM(isnull(lb.overadvance_fee_portion, 0) + isnull(lb.unused_line_fee_portion, 0)) AS total_other_fees

FROM ef_loan_balances lb

,ef_loan_balances lb1

,ef_loan_balances lb2

,ef_loan_master l

,ef_borrowers b

WHERE b.customer_id = l.client_id

AND lb.loanid = l.loan_id

AND dateadd(month, datediff(month, 0, lb.created) + 0, 0) = dateadd(month, datediff(month, 0, '2014-07-31') + 0, 0)

AND isnull(NULL, lb.clientid) = lb.clientid

AND b.customer_id IN (

'CLIENT_20070910162652'

,'CLIENT_20140807133527'

,'CLIENT_ADVA'

,'CLIENT_AIRT'

,'CLIENT_AISS'

,'CLIENT_ALEX'

,'CLIENT_ALIA'

,'CLIENT_ALUR'

,'CLIENT_ARIK'

,'CLIENT_20140314130938'

,'CLIENT_AVIX'

,'CLIENT_AXIS'

,'CLIENT_ATEC'

,'CLIENT_BARC'

,'CLIENT_BOLI'

,'CLIENT_20140207141829'

,'CLIENT_CAND'

,'CLIENT_CARD'

,'CLIENT_CARG'

,'CLIENT_CENT'

,'CLIENT_CLEA'

,'CLIENT_SERV'

,'CLIENT_CPRI'

,'CLIENT_CROS'

,'CLIENT_CROW'

,'CLIENT_DATE'

,'CLIENT_DERV'

,'CLIENT_DEUC'

,'CLIENT_20130920141553'

,'CLIENT_20130802104845'

,'CLIENT_20140812120129'

,'CLIENT_20140331140417'

,'CLIENT_DYET'

,'CLIENT_EVAF'

,'CLIENT_20130822155022'

,'CLIENT_EVER'

,'CLIENT_20130930084836'

,'CLIENT_GENE'

,'CLIENT_GRAN'

,'CLIENT_GRAP'

,'CLIENT_20140905161717'

,'CLIENT_IMGL'

,'CLIENT_VIGO'

,'CLIENT_INFI'

,'CLIENT_20140905145423'

,'CLIENT_20130717105328'

,'CLIENT_LATI'

,'CLIENT_HAWK'

,'CLIENT_LONG'

,'CLIENT_LUMA'

,'CLIENT_MECH'

,'CLIENT_MEDI'

,'CLIENT_20140604092610'

,'CLIENT_MERC'

,'CLIENT_METC'

,'CLIENT_NEAR'

,'CLIENT_20140306110224'

,'CLIENT_NOLA'

,'CLIENT_20140425161116'

,'CLIENT_OFFT'

,'CLIENT_PACI'

,'CLIENT_PAGE'

,'CLIENT_20140716101312'

,'CLIENT_PLAN'

,'CLIENT_PIMA'

,'CLIENT_PCPI'

,'CLIENT_PRES'

,'CLIENT_PRIM'

,'CLIENT_PROL'

,'CLIENT_PROT'

,'CLIENT_PROV'

,'CLIENT_PSCM'

,'CLIENT_PSII'

,'CLIENT_PACK'

,'CLIENT_20140123131930'

,'CLIENT_R3ST'

,'CLIENT_RAZO'

,'CLIENT_RELI'

,'CLIENT_RMJA'

,'CLIENT_ROSS'

,'CLIENT_SOSN'

,'CLIENT_SUMM'

,'CLIENT_20130917164048'

,'CLIENT_TELE'

,'CLIENT_20130828124913'

,'CLIENT_TOPN'

,'CLIENT_TOYS'

,'CLIENT_TRUC'

,'CLIENT_UNIQ'

,'CLIENT_20140509145825'

,'CLIENT_VETE'

,'CLIENT_VIVA'

,'CLIENT_20131105131822'

,'CLIENT_TREN'

,'CLIENT_YSNI'

,'CLIENT_20140321102457'

,'CLIENT_ZEON'

,'CLIENT_ZOOB'

)

AND (

l.participation = 'Y'

OR l.participation = 'N'

)

--lb1 is the first daily balances record in the current month, it isn't necessary the 1st of the month

AND lb1.loanid = lb.loanid

AND lb1.created = (

SELECT MIN(created)

FROM ef_loan_balances lb1a

WHERE dateadd(month, datediff(month, 0, lb1a.created) + 0, 0) = dateadd(month, datediff(month, 0, '2014-07-31') + 0, 0)

AND lb1a.loanid = lb1.loanid

)

--lb2 is the last daily balances record in the current month, it isn't necessary the 1st of the month

AND lb2.loanid = lb.loanid

AND lb2.created = (

SELECT MAX(created)

FROM ef_loan_balances lb2a

WHERE dateadd(month, datediff(month, 0, lb2a.created) + 0, 0) = dateadd(month, datediff(month, 0, '2014-07-31') + 0, 0)

AND lb2a.loanid = lb2.loanid

)

--monthly loan balances should not exist

AND NOT EXISTS (

SELECT 1

FROM ef_monthly_loan_balances mlb

WHERE mlb.created = '2014-07-31'

AND isnull(NULL, mlb.clientid) = mlb.clientid

)

GROUP BY lb.loanid

,lb.clientid

,l.loan_name

,b.customer_description

,lb2.beginning_loan_balance

,lb1.previous_collateral_value

)