September 9, 2014 at 3:28 pm
artisticcheese (9/9/2014)
Hello,I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.
I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?
Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.
Estimated execution plan attached, I can not get actual execution plan because query never completes.
Reason For Early Termination Of Statement Optimization = Time Out.
All bets are off: SQL Server couldn't generate a satisfactory plan from the UNION ALL query. Chances are that the two queries run separately have satisfactory plans.
The answer to this is exactly as Sean has suggested - rewrite the query. Follow his suggestions.
Note that there's a join with no predicate in the second query - that will bite you if and when it does return results.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
September 9, 2014 at 3:34 pm
artisticcheese (9/9/2014)
I narrowed down the issue to statistics, updating statistics on 3 tables used in quyery query complete immediately. No index rebuild necessary.Estimated execution plan before statistics was updated and actual execution plan after statistics was updated as attached. I'm pretty sure it's a bug and I can reproduce it at will since I have backup of DB.
As soon as statistics is updated entire set is returned in 3 s.
These are both timed -out plans too. Forget about statistics. They helped but possibly not for long. Work on the yucky query.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
September 9, 2014 at 3:34 pm
I can not rewrite the query since this query is code generated from third party packaged application. I can created indexes/update statistics etc but not much abount actual code with significant investment of money and time from other people.
So I'm not fully understanding from statements do you know why would UNION ALL behave this way.
By "this way" I mean cause constant high CPU utilization and never completion without updated statistics on table?
September 9, 2014 at 3:41 pm
artisticcheese (9/9/2014)
I can not rewrite the query since this query is code generated from third party packaged application. I can created indexes/update statistics etc but not much abount actual code with significant investment of money and time from other people.So I'm not fully understanding from statements do you know why would UNION ALL behave this way.
By "this way" I mean cause constant high CPU utilization and never completion without updated statistics on table?
SQL Server can't generate a plan good enough within the time allowed, from the query presented i.e. both queries UNIONed together. Each query alone probably has an adequate plan - have a look: right-click on the leftmost operator (SELECT) of the plans and examine the property sheet.
It's nothing to do with UNION (ALL), it's a whole new query and SQL Server chokes on it.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
September 9, 2014 at 3:47 pm
Still don't get it. If it will choke on it then I would assume no results will be returned, instead exactly 88 rows returned and then it hangs with high CPU and no output. Is it expected result from SQL?
Also if it's not a bug then what? SQL figured out how to make execution plans for 2 subqueries but somehow did not figure out how to just join 2 results together, this is UNION ALL, not even UNION, so no additional processing needs to take place, just append one result to another
September 10, 2014 at 1:59 am
artisticcheese (9/9/2014)
Still don't get it. If it will choke on it then I would assume no results will be returned, instead exactly 88 rows returned and then it hangs with high CPU and no output. Is it expected result from SQL?Also if it's not a bug then what? SQL figured out how to make execution plans for 2 subqueries but somehow did not figure out how to just join 2 results together, this is UNION ALL, not even UNION, so no additional processing needs to take place, just append one result to another
Can you post the whole of the second query please? The one which returns 0 rows. Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2014 at 8:33 am
ChrisM@Work (9/10/2014)
artisticcheese (9/9/2014)
Still don't get it. If it will choke on it then I would assume no results will be returned, instead exactly 88 rows returned and then it hangs with high CPU and no output. Is it expected result from SQL?Also if it's not a bug then what? SQL figured out how to make execution plans for 2 subqueries but somehow did not figure out how to just join 2 results together, this is UNION ALL, not even UNION, so no additional processing needs to take place, just append one result to another
Can you post the whole of the second query please? The one which returns 0 rows. Thanks.
Here is it. Statistics update for ef_loan_master and ef_borrowers fixes the issue
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
September 10, 2014 at 9:13 am
Here it is with some formatting.
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 9:20 am
Thanks. Here's that query reformatted for readability, to show how ghastly it is. In addition to points raised by Sean and others, the table loan_balances is read five times where only one read is necessary and some of the date arithmetic prevents index usage.
-- ef_loan_balances is read 5 times.
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, -- first row for the month / loanID
ef_loan_balances lb2, -- last row for the month / loanID
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) -- lb.created between 20140701 and 20140731
AND isnull( null , lb.clientid) = lb.clientid -- silly but logical predicate constructed by client app
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) -- lb1a.created between 20140701 and 20140731
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) -- lb2a.created between 20140701 and 20140731
AND lb2a.loanid = lb2.loanid)
--monthly loan balances should not exist for this clientID
AND NOT EXISTS (
SELECT 1
FROM ef_monthly_loan_balances mlb
WHERE mlb.created = '2014-07-31'
AND isnull( null , mlb.clientid) = mlb.clientid)-- silly but logical predicate constructed by client app
GROUP BY lb.loanid, lb.clientid, l.loan_name, b.customer_description, lb2.beginning_loan_balance, lb1.previous_collateral_value
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2014 at 9:25 am
Heh Fingers on Fire, Sean!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2014 at 9:26 am
Query being produced by some entity framework at front-end, it's not part of SP or manually coded query, so let's move away from bashing how bad query is and think why would this behavior happen assuming that query can not be fixed for whatever reason.
This query returns 0 rows, the other one 92, they execute separately just fine regardless how awfull they are.
UNION ALL of this 92 and 0 rows shall be a piece of cake and yet engine stumbles and hangs process.
September 10, 2014 at 9:32 am
ChrisM@Work (9/10/2014)
Heh Fingers on Fire, Sean!
Nah. I cheated and used poorsql.com. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 9:37 am
Sean Lange (9/10/2014)
ChrisM@Work (9/10/2014)
Heh Fingers on Fire, Sean!Nah. I cheated and used poorsql.com. 😉
"Put your mangled SQL in the box below" - then jump up and down on it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2014 at 9:40 am
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
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply