April 14, 2016 at 12:27 pm
Lynn Pettis (4/14/2016)
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.
I have a Guruism I preach often: "Anything that allows developers to slap code together more quickly is inversely proportional to the performance (and likely concurrency) you will get from said code."
Things that fall into that realm include but are certainly not limited to: views (especially the umpteen-deep ones), UDFs, ORMs, Graphical Reporting Tools (Business Objects is a real disaster, Cognos not much better).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2016 at 3:54 pm
Lynn Pettis (4/14/2016)
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.
The concept of triggers makes sense. In practice in SQL Server, it is a killer.
The concept of cursors makes sense. In practice in SQL Server, it is a killer.
The concept of GUID makes sense. In practice in SQL Server, it is a killer.
The concept of EAV makes sense. In practice in SQL Server, it is a killer.
Don't you smell a tiny piece of BS here?
Or maybe not so tiny.
The last parts of all those statements are true only with a mandatory add-on - when done wrong and applied inappropriately.
Views on views do not cause any performance issues, if you follow the rules.
The only problem which used to cause issues and frustration about nested views was the limitations on nesting and total number of tables involved.
But even when I had over 200 tables in a chain of views the response time of the query was still under 1 second, and, naturally, no users were complaining.
_____________
Code for TallyGenerator
April 14, 2016 at 4:06 pm
TheSQLGuru (4/14/2016)
I have a Guruism I preach often: "Anything that allows developers to slap code together more quickly is inversely proportional to the performance (and likely concurrency) you will get from said code."Things that fall into that realm include but are certainly not limited to: views (especially the umpteen-deep ones), UDFs, ORMs, Graphical Reporting Tools (Business Objects is a real disaster, Cognos not much better).
So true.
But those are so common and glorified so many practices that developers don't even have an idea how damaging they are.
They fail in SQL because of huge amounts of data (comparing to an ordinary front-end application) the code has to process when accessing databases directly.
But in front-end application nobody cares about those several extra CPU cycles that method adds to the process. It's only users left scratching their heads why the batteries on their phones get drained faster and faster.
_____________
Code for TallyGenerator
April 14, 2016 at 7:15 pm
Sergiy (4/14/2016)
Lynn Pettis (4/14/2016)
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.
The concept of triggers makes sense. In practice in SQL Server, it is a killer.
The concept of cursors makes sense. In practice in SQL Server, it is a killer.
The concept of GUID makes sense. In practice in SQL Server, it is a killer.
The concept of EAV makes sense. In practice in SQL Server, it is a killer.
Don't you smell a tiny piece of BS here?
Or maybe not so tiny.
The last parts of all those statements are true only with a mandatory add-on - when done wrong and applied inappropriately.
Views on views do not cause any performance issues, if you follow the rules.
The only problem which used to cause issues and frustration about nested views was the limitations on nesting and total number of tables involved.
But even when I had over 200 tables in a chain of views the response time of the query was still under 1 second, and, naturally, no users were complaining.
Wow, Sergiy. The only problem here is that most developers do use them inappropriately.
April 15, 2016 at 7:21 am
Sergiy (4/14/2016)
Lynn Pettis (4/14/2016)
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.
The concept of triggers makes sense. In practice in SQL Server, it is a killer.
The concept of cursors makes sense. In practice in SQL Server, it is a killer.
The concept of GUID makes sense. In practice in SQL Server, it is a killer.
The concept of EAV makes sense. In practice in SQL Server, it is a killer.
Don't you smell a tiny piece of BS here?
Or maybe not so tiny.
The last parts of all those statements are true only with a mandatory add-on - when done wrong and applied inappropriately.
Views on views do not cause any performance issues, if you follow the rules.
The only problem which used to cause issues and frustration about nested views was the limitations on nesting and total number of tables involved.
But even when I had over 200 tables in a chain of views the response time of the query was still under 1 second, and, naturally, no users were complaining.
Well then apparently you think myself and many others are constantly speaking nothing but BS??? You forgot to mention scalar functions and multi-statement table valued functions. You have been around this technology long enough to know that all of these things make sense in the right time and place. The problem is that they very often get over used and abused to the point where entire systems are crippled. We see it time and time again on forums here and everywhere else.
_______________________________________________________________
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 15, 2016 at 8:29 pm
Heh... wow... this thread certainly jumped the track. 😉
The original post is pretty clear. Everything flies when the last condition is removed even though a shedload of views and functions have been used. That kind of makes all the talk about views and functions being a poison to performance a bit moot.
The OP needs to use the extra criteria that he removed. Any help there?
--Jeff Moden
April 16, 2016 at 10:09 am
Jeff Moden (4/15/2016)
Heh... wow... this thread certainly jumped the track. 😉The original post is pretty clear. Everything flies when the last condition is removed even though a shedload of views and functions have been used. That kind of makes all the talk about views and functions being a poison to performance a bit moot.
The OP needs to use the extra criteria that he removed. Any help there?
That was exactly the salient point of my initial response to the OP. But I completely missed that he EDITED one of his responses to add in the definitions!!
Hey Mike, I don't see the definition of [User_DISC], which is the table referenced in the view that is causing problems with the IN clause. Be sure to provide indexing on that table. Also, how many rows does that table have?
Oh, looking at the query plan I see this for the table scan being done on [User_DISC]:
[CM_ZCM].[dbo].[User_DISC].[Full_User_Name0]=CONVERT_IMPLICIT(nvarchar(25),[@Assignee],0)
So your table def is an Nvarchar(25) and you are declaring and using a Varchar(25) parameter. New rule: YOU WILL, ALWAYS WITHOUT EXCEPTION, USE EXACTLY THE SAME DATA TYPE AS THE COLUMN TO THE BEST OF YOUR LANGUAGES ABILITY TO DO SO! I cannot stress this strongly enough. It is in fact the single worst thing I see in aggregate in my performance tuning consulting work. And trust me when I tell you that is saying something.
Point 2: Doesn't seem likely that you can put two+ name thingies in @Assignee due to the size. If that is the case the IN is completely unnecessary and inappropriate. Just do this:
and vru.Full_User_Name0 = (@Assignee)
Try those things out and report back. Also, I still want to see the table def.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2016 at 7:05 pm
Well, performance of the query seems to be irrelevant, as it's built incorrectly.
declare @Assignee as varchar(25); set @Assignee = 'Smith, John'
select ...
from ...
left join v_R_User vru on vru.Distinguished_Name0 = vrs.managedBy0
join ....
where ...
and vgs.SystemRole0 IN (@Machine_Class)
and vnd.CategoryInstanceName IN (@Vendor)
and vru.Full_User_Name0 IN (@Assignee)
Logical errors:
1. Having a column from LEFT JOINed table in WHERE clause.
It implicitly converts (may I use this wording?) LEFT JOIN to INNER JOIN.
Which changes the logic of the query.
It's a totally different query, with different execution plan, different recordset returned.
And different performance counts, of course.
2. IN does not work this way.
It checks values in a column against a set of values, like content of a column in a table.
Comma delimited values stored in a variable are not a set of 2 values.
A variable is a single string value "Smith, John".
Probably that's the right vru.Full_User_Name0 which needs to be found, then please use proper SQL syntax:
and vgs.SystemRole0 = @Machine_Class
and vnd.CategoryInstanceName = @Vendor
and vru.Full_User_Name0 = @Assignee
_____________
Code for TallyGenerator
April 18, 2016 at 2:29 am
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 * INTO #temp FROM fn_CICategoryInfo_All(@lcid)
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 #temp vnd ON vnd.CI_ID = ui.CI_ID
AND vnd.CategoryTypeName = 'Company'
JOIN #temp 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 )
DROP TABLE #temp;
April 18, 2016 at 7:51 am
rockyshi (4/18/2016)
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 * INTO #temp FROM fn_CICategoryInfo_All(@lcid)
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 #temp vnd ON vnd.CI_ID = ui.CI_ID
AND vnd.CategoryTypeName = 'Company'
JOIN #temp 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 )
DROP TABLE #temp;
Perhaps an explanation along with a bunch of t-sql would help. I see the biggest problem so far in this thread is still present. A variable is a scalar value and as such does NOT behave like many people think it should when using IN.
_______________________________________________________________
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 18, 2016 at 8:48 am
TheSQLGuru (4/16/2016)
New rule: YOU WILL, ALWAYS WITHOUT EXCEPTION, USE EXACTLY THE SAME DATA TYPE AS THE COLUMN TO THE BEST OF YOUR LANGUAGES ABILITY TO DO SO!
THAT IS A HUGE MISTAKE!
An implicit conversion of a variable/static value(s) is just fine with me, since the main column is still fully SEEKable.
Thus, my coding roles insist that one avoids explicitly using unicode strings unless it's absolutely required. Because:
1) if I use a plain string literal/variable and the column is unicode, SQL will implicitly convert the literal/variable, with no adverse performance effect
2) if I use a unicode string, and the column is not unicode, SQL will implicitly convert the column to unicode, causing performance issues, possibly severe ones.
Similarly, when comparing any form of date/time to a specific value(s), I prefer varchar literal values to explicitly CASTing the variables as the matching data type. That is, I will convert the strongly-typed matching variable type into a literal date/time varchar in the query condition when possible.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 18, 2016 at 9:39 am
ScottPletcher (4/18/2016)
TheSQLGuru (4/16/2016)
New rule: YOU WILL, ALWAYS WITHOUT EXCEPTION, USE EXACTLY THE SAME DATA TYPE AS THE COLUMN TO THE BEST OF YOUR LANGUAGES ABILITY TO DO SO!
THAT IS A HUGE MISTAKE!
...
Given that this is in aggregate the single worst thing I see in my consulting business (and I have been consulting on SQL Server since the mid 1990's), we are going to have to agree to disagree on this one Scott. And even your statement that non-harm-causing implicit conversions of parameters/variables is OK is amazing. That is a non-zero amount of work being done by SQL Server that is simply caused by a lazy developer not looking at the schema and using the correct data type. There is absolutely no excuse for that, I don't care if they are up at 0238 in the morning slapping together a critical fix. And I have worked on systems (especially virtualized ones) where those unnecessary CPU ticks are actually important.
Two more Guruism's apply here:
1) Some work divided by no work is an infinite performance improvement. :w00t:
2) The fastest thing you can do on SQL Server is NOTHING! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 18, 2016 at 10:25 am
TheSQLGuru (4/18/2016)
ScottPletcher (4/18/2016)
TheSQLGuru (4/16/2016)
New rule: YOU WILL, ALWAYS WITHOUT EXCEPTION, USE EXACTLY THE SAME DATA TYPE AS THE COLUMN TO THE BEST OF YOUR LANGUAGES ABILITY TO DO SO!
THAT IS A HUGE MISTAKE!
...
Given that this is in aggregate the single worst thing I see in my consulting business (and I have been consulting on SQL Server since the mid 1990's), we are going to have to agree to disagree on this one Scott. And even your statement that non-harm-causing implicit conversions of parameters/variables is OK is amazing. That is a non-zero amount of work being done by SQL Server that is simply caused by a lazy developer not looking at the schema and using the correct data type. There is absolutely no excuse for that, I don't care if they are up at 0238 in the morning slapping together a critical fix. And I have worked on systems (especially virtualized ones) where those unnecessary CPU ticks are actually important.
Two more Guruism's apply here:
1) Some work divided by no work is an infinite performance improvement. :w00t:
2) The fastest thing you can do on SQL Server is NOTHING! 😀
It's absolutely not coding "laziness". It's simply to allow for data type changes later without causing performance issues in the code.
I can't believe anyone would even try to claim that implicitly converting variable values, which occurs one time while the code is being compiled, is enough overhead to even consider. A single mis-match from trying to rigidly match data type will cause vastly more overhead than 5 years of allowing implicit variable conversions.
We a currently removing unicode settings from some columns to save disk space. With implicit conversions, no code changes are needed. With explicit conversions, all the code must be changed.
Btw, do you really write code like this:
WHERE id = CAST(5 AS int)
rather than:
WHERE id = 5
Or this:
--DECLARE @start_date date, @end_date date
WHERE when_sold >= CAST(@start_date AS datetime) AND when_sold < CAST(@end_date AS datetime)
rather than:
WHERE when_sold >= @start_date AND when_sold < @end_date
Wow, lots of extra coding work.
I use explicit conversion to avoid implicit conversions. For example, if an int variable is to be compared to a smallint column.
WHERE smallint_column = CAST(@int_variable AS smallint)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 18, 2016 at 10:34 am
We had a situation where a single procedure was generating 2 billion logical reads across a few thousand executions of a particular statement. This had a definite and palpable effect on the performance of the system. Using DPA we were able to identify that the source of the problem was a case where an input parameter to a compiled query was NVARCHAR and it was used in a WHERE clause against a VARCHAR column for a table with several million rows. Such an implicit conversion caused it to do a table scan every time it ran and not use the index that was supposed to be used. Change that parameter to VARCHAR and the performance instantly and dramatically improved. I have to agree on the implicit conversions being bad, and only being invisible if you are very lucky.
April 18, 2016 at 10:58 am
I have to jump on with Jeff & Kevin on this one. By and large one should use the appropriate data types in the appropriate place. Is it a non-issue depending on the situation? Yes. Is it very frequently an issue, and a non-trivial one at that? Oh heck yes. Pretty much every rule has exceptions, but that doesn't mean the rule isn't a good idea as a general approach.
----------------------------------------------------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
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply