April 13, 2016 at 3:37 pm
Greetings,
I'm new to SQL and trying to figure out why this query takes so long to return results. I'm sure there's something I've done that's inefficient. If I comment out the last condition "and vru.Full_User_Name0 IN (@Assignee)", it returns results instantly. Otherwise it takes 3 minutes. Thanks for any help!
declare @locale as varchar(25); set @locale = 'User!Language'
declare @lcid as int; set @lcid = dbo.fn_LShortNameToLCID(@locale)
declare @Machine_Class as varchar(25); set @Machine_Class = 'Server'
declare @Vendor as varchar(25); set @Vendor = 'Microsoft'
declare @Assignee as varchar(25); set @Assignee = 'Smith, John'
select distinct vrs.Name0 AS 'Machine_Name'
, vru.Full_User_Name0 AS 'Assignee'
, vgs.SystemRole0 AS 'Machine_Class'
, vrs.Operating_System_Name_and0 AS 'OS'
, UI.BulletinID AS 'Bulletin_ID'
, vnd.CategoryInstanceName AS 'Vendor'
, cls.CategoryInstanceName AS 'Classification'
, UI.Title AS 'Title'
, CASE ui.Severity
WHEN 10 THEN 'Critical'
WHEN 8 THEN 'Important'
WHEN 6 THEN 'Moderate'
WHEN 2 THEN 'Low'
ELSE 'Undefined'
END AS 'Severity'
, UI.DatePosted AS 'Release_Date'
, DATEDIFF("day",ui.DatePosted,GETDATE()) AS 'Release_Age'
, UI.DateRevised AS 'Last_Revised_Date'
, DATEDIFF("day",ui.DateRevised,GETDATE()) AS 'Last_Revised_Age'
, UCS.LastStatusChangeTime AS 'Last_Status_Change'
, UI.IsDeployed AS 'IsDeployed'
from v_Update_ComplianceStatus UCS
join v_R_System vrs on UCS.ResourceID = vrs.ResourceID
join v_GS_System vgs on UCS.ResourceID = vgs.ResourceID
left join v_R_User vru on vru.Distinguished_Name0 = vrs.managedBy0
join v_UpdateInfo UI on UCS.CI_ID = UI.CI_ID
join fn_CICategoryInfo_All(@lcid) vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
join fn_CICategoryInfo_All(@lcid) cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
join v_UpdateDeploymentSummary UDS on UCS.CI_ID = UDS.CI_ID
where UCS.Status = '2'
and vgs.SystemRole0 IN (@Machine_Class)
and vnd.CategoryInstanceName IN (@Vendor)
and vru.Full_User_Name0 IN (@Assignee)
April 13, 2016 at 4:02 pm
Did you look at the execution plan for the SQL statement? It should tell you where the problems are.
If you're using scalar functions in there, then that could be a problem.
April 13, 2016 at 4:15 pm
Yes, I included the execution plan. There is an icon called 'Key Lookup (Clustered)' that accounts for 68% of the cost.
Physical Operation: Key Lookup
Logical Operation: Key Lookup
Actual/Estimated Execution Modes: Row
Storage: RowStore
Actual number of rows: 1594
Estimated number of rows: 541,558
Ordered: True
Node ID: 57
April 13, 2016 at 5:01 pm
To be honest, there is a lot that can be going on in this query, including some things that won't work as you expect them do work.
First, a casual glance at the code reveals that you are joining multiple views together. Since we can't see what you see it is hard to know if these views are a each a simple view built over a single table or if each view is in its self a query of multiple views or tables.
Second, since you didn't post the execution plan (as a .sqlplan) we have no way of knowing what is going on either.
What you really need to do is post the DLL for the views and tables including the indexes on the tables, the execution plan for the query involved.
You should read the second article I reference in my signature block, it will show you what we need and how to post it to help with performance issues.
April 14, 2016 at 7:04 am
Mike Frazer (4/13/2016)
Yes, I included the execution plan. There is an icon called 'Key Lookup (Clustered)' that accounts for 68% of the cost.Physical Operation: Key Lookup
Logical Operation: Key Lookup
Actual/Estimated Execution Modes: Row
Storage: RowStore
Actual number of rows: 1594
Estimated number of rows: 541,558
Ordered: True
Node ID: 57
As Lynn already stated we need some actual details to provide some actual help. You have view after view after view and then a couple of table valued functions. Those functions could potentially be a performance problem. So could all of the views. Do those views pull data from other views? This is called nested views and they are plain evil. The are a maintenance nightmare and the performance is unbelievably bad. It seems so logical but nested views will kill performance.
From the snippet of your execution plan here it seems that you very likely have some stale statistics which can severely hamper performance. Can't tell how to update the stats in this case because you didn't provide enough details.
_______________________________________________________________
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/
April 14, 2016 at 7:38 am
You're also calling a function twice. If that's a multi-statement table function, that might be causing problems.
Your clause "and vru.Full_User_Name0 IN (@Assignee)" is causing that your left join becomes an inner join. You might want to review that logic.
As mentioned before, you need to provide more information. Lynn has this article on his signature, but if you didn't see it, here it is again to know what you need to post and how.
http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
April 14, 2016 at 7:49 am
Often a query as complex (and potentially WICKEDLY suboptimal) can take many hours to tune. Fortunately for you there seems to be a magic-bullet: and vru.Full_User_Name0 IN (@Assignee)
So with some more information from you about that (and ALL of the things it hits) we may be able to help you without the full definitions of all of the stuffs in play here.
BTW, UDFs (Scalar and Multi-statement Table Valued Functions) are UNBELIEVABLY BAD!!!! If you want some details, grab a copy of the SQL Server MVP Deep Dives 2 book (proceeds go to charity) and read my chapter entitled "Death by UDF". It's the best chapter in the book!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2016 at 8:02 am
This is a major tuning effort. I'd definitely be able to say more if I could see the execution plan. However, the other comments, JOINs on views, what appears to be multi-statement UDF's, and this: IN (@Assignee), are all very likely leading to the root cause of your poor performance. These are very common code smells.
And Kevin's chapter in the MVP Deep Dives book is the second best one. Mine on parameter sniffing is the best. 😛
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 14, 2016 at 9:54 am
How do I post the execution plan? Do you want me to post the text of it here? I don't see an option to upload it to this thread.
April 14, 2016 at 10:26 am
When you reply to the thread, check the lower-right of the window. Button called "Edit Attachments".
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2016 at 11:23 am
Thank you...attached.
Edit: I added the DDL for the related objects as scripts.txt
April 14, 2016 at 11:45 am
Like I said, I would rather see the code of the UDF and the objects it hits more than the query plan in this (very rare) case since when you take it out you have great performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2016 at 11:53 am
Mike Frazer (4/14/2016)
Thank you...attached.Edit: I added the DDL for the related objects as scripts.txt
Quick look at the script file. Looks like you have nested views in the views. No code for the functions. No code for the tables or indexes either.
April 14, 2016 at 12:11 pm
Mike Frazer (4/14/2016)
Thank you...attached.Edit: I added the DDL for the related objects as scripts.txt
Those nested views are going to kill your performance something fierce. They seem so logical but in reality they are plain evil. Check out this article on the topic. You will have to scroll all the way to the bottom to see the part about nested views. https://www.simple-talk.com/sql/performance/the-seven-sins-against-tsql-performance/[/url]
_______________________________________________________________
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/
April 14, 2016 at 12:17 pm
Sean Lange (4/14/2016)
Mike Frazer (4/14/2016)
Thank you...attached.Edit: I added the DDL for the related objects as scripts.txt
Those nested views are going to kill your performance something fierce. They seem so logical but in reality they are plain evil. Check out this article on the topic. You will have to scroll all the way to the bottom to see the part about nested views. https://www.simple-talk.com/sql/performance/the-seven-sins-against-tsql-performance/[/url]
The concept of code reuse makes sense. In practice in SQL Server, it is a killer.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply