Query causing compilation timeouts and huge cost = 8.28104

Question

Post reply

Query causing compilation timeouts and huge cost = 8.28104

stevec883

SSC-Addicted

Points: 440
More actions
August 24, 2021 at 9:56 pm
Go to Answer
#3923673

I have found a view written by a developer that is causing a compilation time out, and an execution plan that has about 50 operators in the graph. Should this select statement be written with the joins outside of the "FROM" clause, or does that not matter? It is causing "nested" loops in the Stmt Text.
SELECT TOP (100) PERCENT dbo.f_Lease_7_Status.Lease_ID, dbo.c_Lease_Cost_Total.Lease_Amount, dbo.c_Lease_Cost_Total.Proposed_Total, dbo.v_Lease_Info.LeaseInfo, dbo.v_Lease_Info.LeaseInfo2, dbo.v_Lease_Info.Area,
dbo.v_Lease_Info.Contact_ID, dbo.v_Lease_Info.Contact_Name, dbo.v_Lease_Info.Lease_Building_ID, dbo.v_Lease_Info.Building_Name, dbo.v_Lease_Info.Lease_Year, dbo.v_Lease_Info.Lease_Number,
dbo.v_Lease_Info.Lease_Modification_Number, dbo.v_Lease_Info.Lease_CYFY, dbo.v_Lease_Info.Lease_Type, dbo.v_Lease_Info.NumCostElem, dbo.v_Lease_Info.Contact_Title, dbo.v_Lease_Info.Building_Size_SF,
dbo.v_Lease_Info.City, dbo.v_Lease_Info.State, dbo.v_Lease_Info.Proposal_Date, dbo.v_Lease_Info.Lease_Term_Start, dbo.v_Lease_Info.Lease_Term_End, dbo.v_Lease_Info.Status_Date, trim(dbo.v_Lease_Info.Lease_CYFY)
+ trim(CONVERT(varchar(4), dbo.v_Lease_Info.Lease_Year)) + N'-' + trim(CONVERT(varchar(4), dbo.v_Lease_Info.Lease_Modification_Number)) AS YearMOD, trim(dbo.v_Lease_Info.Building_Name)
+ N', ' + trim(dbo.v_Lease_Info.City) + N', ' + trim(dbo.v_Lease_Info.State) AS Building_NCS, dbo.c_Lease_Cost_Total.Final_COSTperSFperYR, dbo.c_Lease_Cost_Total.Proposed_COSTperSFperYR,
dbo.c_Lease_Cost_Total.Offset_Total, dbo.c_Lease_Cost_Total.Proposed_Total_Offset, dbo.v_Lease_Info.Status_Notes, dbo.c_LeaseNo.LeaseNo, dbo.v_Lease_Info.T15, dbo.V_Max90StartDate.MaxStart90Date,
dbo.v_Max90ExtDate.MaxExtended90Date, dbo.v_Lease_Info.Status_Abrev, dbo.v_Lease_Info.Status_Description, dbo.v_Lease_Info.Status_Abrev + N' - ' + dbo.v_Lease_Info.Status_Description AS StatusAbrevDesc,
dbo.c_Lease_Cost_Total.LeaseAmount105l, dbo.c_Lease_Cost_Total.ProRatedAmount, dbo.v_Lease_Info.Contact_Name + N' - T' + dbo.v_Lease_Info.T15 AS Contact_Name_T15,
COALESCE (dbo.c_b_addr_Lease_No_Short.HFDS_inst_no + N'-', N'') + trim(dbo.v_Lease_Info.Building_Name) + N', ' + trim(dbo.v_Lease_Info.City) + N', ' + trim(dbo.v_Lease_Info.State) AS HFDS_and_Bldg_Name,
dbo.c_b_addr_Lease_No_Short.HFDS_inst_no
FROM dbo.f_Lease_7_Status INNER JOIN
dbo.v_Lease_Info ON dbo.f_Lease_7_Status.Lease_ID = dbo.v_Lease_Info.Lease_ID INNER JOIN
dbo.c_LeaseNo ON dbo.v_Lease_Info.Lease_ID = dbo.c_LeaseNo.Lease_ID LEFT OUTER JOIN
dbo.c_b_addr_Lease_No_Short ON dbo.v_Lease_Info.Lease_No_Short = dbo.c_b_addr_Lease_No_Short.Inst_Name_Lease_No_Short LEFT OUTER JOIN
dbo.v_Max90ExtDate ON dbo.v_Lease_Info.Lease_ID = dbo.v_Max90ExtDate.Lease_ID LEFT OUTER JOIN
dbo.V_Max90StartDate ON dbo.v_Lease_Info.Lease_ID = dbo.V_Max90StartDate.Lease_ID LEFT OUTER JOIN
dbo.c_Lease_Cost_Total ON dbo.v_Lease_Info.Lease_ID = dbo.c_Lease_Cost_Total.Lease_ID

frederico_fonseca

SSCoach

Points: 16033
More actions
August 26, 2021 at 5:36 pm
Answer
#3924268

does not look like it - no duration, neither actual rows - only expected executions.
did you capture it yourself while executing it or through a monitor tool like SQL Monitor?
but regardless - issue here is views over views over views - remove all this complexity and query will be easier for SQL to compile and performance will likely improve.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16033 More actions · Answer 1

you have other views there - and views over views over views always cause issues with the query processor.

the actual explain plan for this will be useful - and all the DDL for the views involved might help.

(and do teach the developer the use of table alias - none of them has one and having the full schema/tablename on each column on the select just causes code to be harder to follow/mantain)

stevec883 SSC-Addicted Points: 440 More actions · Answer 2

The execution plan is huge. Should I paste it as XML? I can't do a screen shot because it goes way off the screen in both directions.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

If you save the plan as a SQLPLAN file and attach that would be best, failing that use https://www.brentozar.com/pastetheplan/ and share the output link

stevec883 SSC-Addicted Points: 440 More actions · Answer 4

Thanks, I pasted the plan;

https://www.brentozar.com/pastetheplan/?id=B1Jo9m4WY

frederico_fonseca SSCoach Points: 16033 More actions · Answer 5

that is an estimated plan, not the a actual execution plan - can you get the actual one please.

and also the view (and all sub called views).

in all likelihood all this should be rewritten to use the tables required directly instead of overusing views.

select code below - slightly better for viewing

SELECT TOP (100) PERCENT fl7.Lease_ID
     , lct.Lease_Amount
     , lct.Proposed_Total
     , lein.LeaseInfo
     , lein.LeaseInfo2
     , lein.Area
     , lein.Contact_ID
     , lein.Contact_Name
     , lein.Lease_Building_ID
     , lein.Building_Name
     , lein.Lease_Year
     , lein.Lease_Number
     , lein.Lease_Modification_Number
     , lein.Lease_CYFY
     , lein.Lease_Type
     , lein.NumCostElem
     , lein.Contact_Title
     , lein.Building_Size_SF
     , lein.City
     , lein.State
     , lein.Proposal_Date
     , lein.Lease_Term_Start
     , lein.Lease_Term_End
     , lein.Status_Date
     , trim(lein.Lease_CYFY)
       + trim(CONVERT(varchar(4), lein.Lease_Year)) 
       + N'-' 
       + trim(CONVERT(varchar(4), lein.Lease_Modification_Number)) AS YearMOD
     , trim(lein.Building_Name)
       + N', ' 
       + trim(lein.City) 
       + N', ' 
       + trim(lein.State) AS Building_NCS
     , lct.Final_COSTperSFperYR
     , lct.Proposed_COSTperSFperYR
     , lct.Offset_Total
     , lct.Proposed_Total_Offset
     , lein.Status_Notes
     , cln.LeaseNo
     , lein.T15
     , dbo.V_Max90StartDate.MaxStart90Date
     , dbo.v_Max90ExtDate.MaxExtended90Date
     , lein.Status_Abrev
     , lein.Status_Description
     , lein.Status_Abrev 
       + N' - ' 
       + lein.Status_Description AS StatusAbrevDesc
     , lct.LeaseAmount105l
     , lct.ProRatedAmount
     , lein.Contact_Name 
       + N' - T' 
       + lein.T15 AS Contact_Name_T15
     , COALESCE (adrln.HFDS_inst_no + N'-', N'') 
       + trim(lein.Building_Name) 
       + N', ' 
       + trim(lein.City) 
       + N', ' 
       + trim(lein.State) AS HFDS_and_Bldg_Name
     , adrln.HFDS_inst_no

FROM dbo.f_Lease_7_Status fl7
INNER JOIN dbo.v_Lease_Info lein
    ON fl7.Lease_ID = lein.Lease_ID
INNER JOIN dbo.c_LeaseNo cln
    ON lein.Lease_ID = cln.Lease_ID
LEFT OUTER JOIN dbo.c_b_addr_Lease_No_Short adrln
    ON lein.Lease_No_Short = adrln.Inst_Name_Lease_No_Short
LEFT OUTER JOIN dbo.v_Max90ExtDate
    ON lein.Lease_ID = dbo.v_Max90ExtDate.Lease_ID
LEFT OUTER JOIN dbo.V_Max90StartDate
    ON lein.Lease_ID = dbo.V_Max90StartDate.Lease_ID
LEFT OUTER JOIN lct lct
    ON lein.Lease_ID = lct.Lease_ID

stevec883 SSC-Addicted Points: 440 More actions · Answer 6

Frederico - that is the Actual Execution Plan (captured after the query ran).

stevec883 SSC-Addicted Points: 440 More actions · Answer 7

Thanks Fred - Yes, I think this one is almost too convoluted to repair, need to start over with a new query.

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Your multiple views use the same tables over and over again.

For example, table [Lease] is mentioned in your query plan 20 times, [User_area] - 21 times.

I did not bother counting [Lease_Status], etc.

I'm pretty sure it's not necessary.

Views on views are not a problem, it's chaotic piling up of repeating unnecessary calls to the same bunch of tables which creates the problem.

"Open" those views, build a proper query which is doing what you need to do, and the problem will go away.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 100950 More actions · Answer 9

For the actual tables, post the DDL, including all indexes.

For any views, post the view definition.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 10

Sergiy wrote:

Your multiple views use the same tables over and over again.
For example, table [Lease] is mentioned in your query plan 20 times, [User_area] - 21 times.
I did not bother counting [Lease_Status], etc.
I'm pretty sure it's not necessary.
Views on views are not a problem, it's chaotic piling up of repeating unnecessary calls to the same bunch of tables which creates the problem.
"Open" those views, build a proper query which is doing what you need to do, and the problem will go away.

Start with Sergiy's post above... This needs to be a redesign and full redaction of the code. Don't even look at the old code because you'll just end up making the same mistake. Define the problem and write the code to solve that problem and only that problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stevec883 SSC-Addicted Points: 440 More actions · Answer 11

Thanks Jeff and Sergiy - I was thinking that as well, that this is beyond repair and no amount of indexing or other tuning can fix it. Thanks for clarifying. I need to start with one that is no so badly written... (that is fixable).