November 9, 2015 at 11:01 pm
While going through execution plan i observed this warning
Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). Will this cause performance issue or this warning can be ignored??
November 9, 2015 at 11:51 pm
David Rich (11/9/2015)
While going through execution plan i observed this warningType conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). Will this cause performance issue or this warning can be ignored??
Chances are it could and generally these warnings should not be ignored.
😎
November 10, 2015 at 7:07 am
CONVERT_IMPLICIT is a custom stored function ?
You probably need to expose the columns specified in the function to eliminate this message.
November 10, 2015 at 7:46 am
mar.ko (11/10/2015)
CONVERT_IMPLICIT is a custom stored function ?You probably need to expose the columns specified in the function to eliminate this message.
That is not a custom function. It is a function that can appear in the execution plan and usually indicates that there is a datatype mismatch.
_______________________________________________________________
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/
November 10, 2015 at 11:42 am
David Rich (11/9/2015)
While going through execution plan i observed this warningType conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). Will this cause performance issue or this warning can be ignored??
It should be that it is being converted from nvarchar(255). When you specify literals, don't specify unicode (N'...') unless you have to, use simple '...' instead. SQL will implicitly convert the varchar literal to nvarchar if/when it needs to, but specifying nvarchar when the column is varchar will force an implicit column conversion. Many people seem to automatically use N'...' for all literals, but that's actually backwards from what you should do.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 10, 2015 at 11:56 am
ScottPletcher (11/10/2015)
David Rich (11/9/2015)
While going through execution plan i observed this warningType conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). Will this cause performance issue or this warning can be ignored??
It should be that it is being converted from nvarchar(255). When you specify literals, don't specify unicode (N'...') unless you have to, use simple '...' instead. SQL will implicitly convert the varchar literal to nvarchar if/when it needs to, but specifying nvarchar when the column is varchar will force an implicit column conversion. Many people seem to automatically use N'...' for all literals, but that's actually backwards from what you should do.
Or if you're using variables, be sure that they're defined as varchar instead of nvarchar.
November 10, 2015 at 1:35 pm
David Rich (11/9/2015)
While going through execution plan i observed this warningType conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). Will this cause performance issue or this warning can be ignored??
Find where you match (either in JOIN or in WHERE) values in varchar(255) column [e].[abc] to a numeric value (a column or a variable).
Avoid implicit conversions by al means.
_____________
Code for TallyGenerator
November 10, 2015 at 1:43 pm
Sergiy (11/10/2015)
David Rich (11/9/2015)
While going through execution plan i observed this warningType conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). Will this cause performance issue or this warning can be ignored??
Find where you match (either in JOIN or in WHERE) values in varchar(255) column [e].[abc] to a numeric value (a column or a variable).
Avoid implicit conversions by all means.
I believe the ", 0" in the CONVERT is a style code/type, not actual data being converted. Thus, I don't believe there's any numeric value being converted.
Implicit conversions on the static/literal side of a query are fine. For example, if you specify:
col1 = 'abc'
and SQL has to implicitly convert 'abc' to N'abc', that is OK; that is, in fact, exactly what you want to see.
But, yes, always avoid if possible any table column(s) being implicitly converted.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply