3

Yes, you read correctly, 59%. Some of these are undoubtedly programmer error where they are comparing an INTEGER with a VARCHAR value, but of the ones I've surveyed (out of the 5139 execution plans with the problem) the only conversion has been a varchar to varchar comparison, like this ...

ScalarString="CONVERT_IMPLICIT(varchar(250),[CAMMS].[Entity].[t_PersonName].[LastName] as [pn].[LastName]+', '+[CAMMS].[Entity].[t_PersonName].[FirstName] as [pn].[FirstName]+' '+[CAMMS].[Entity].[t_PersonName].[MiddleName] as [pn].[MiddleName],0)"

This part of the stored proc is concatenating name parts. The varchar(250) reference above appears to come from the fact that the Full-Name value is being inserted into a table with a column of type varchar(250)

CREATE TABLE #TmpMatter (
    ObjectType      varchar(50),
    ObjectID        int, 
    MatterEntityID int,
    CaseNumber      varchar(100) null, 
    Title           varchar(50),
    DefendantName   varchar(250) null ...

I checked COLLATION on both my database and on TempDB ... they are the same. Checked COLLATION on all text columns and that is correct. I have 12 columns whose ANSI PADDING is OFF but these CONVERT_IMPLICIT errors are showing up only on columns with it ON, so I'm not seeing any mismatch there.

One post said they fixed this problem by addressing the ANSI PADDING issue, but that can't be it ... unless my temp tables are being created with PADDING = OFF. But TempDB (and all system dbs) have PADDING = ON.

I'm stuck on this one. Anyone have any ideas?

  • It may be useful to show us the datatypes of the original columns (in [CAMMS].[Entity].[t_PersonName]), to confirm those datatypes. I imagine you've already looked at this yourself, but showing us would help clarify things. – RDFozz Jun 5 '18 at 16:52
  • Good point that I forgot to mention. There are no NVARCHAR columns involved. All temp tables use varchars and all database tables involved are using varchars. In this case, First and Middle names are varchar(50) and Last is varchar(100). The thing is, they don't add up to varchar(250). Could that be the problem? – Danny Lesandrini Jun 5 '18 at 16:55
  • I just tried some things when creating the temp table. Added SET ANSI_PADDING OFF; and checked for CONVERT_IMPLICIT, but no change. Tried SET ANSI_PADDING ON; but no change. Tried forcing COLLATION of columns with COLLATE database_default but no change. Then I changed the column sizes in the temp table to match the fields used to populate them. Still no change. The Execution Plan still uses CONVERT_IMPLICIT on 7 fields in that temp table all populated from various different tables in the database. Weird! – Danny Lesandrini Jun 5 '18 at 17:03
  • This is not that weird. You are applying expressions to the source columns. If you add strings to columns they're no longer the same size as the columns. If you hook a trailer to your truck, it may no longer fit in your garage. Might I suggest keeping the source data as is, and only applying frivolous prettifying at the presentation layer? – Aaron Bertrand Jun 5 '18 at 17:16
3

Not every implicit conversion is a problem that needs to be fixed. Is this contributing to any actual performance issue? How do you know this?

The reason an implicit conversion comes into play here is not because of data type or collation but rather because of precision. SQL Server will come up with a guess that this expression:

LastName + ', ' + FirstName + ' ' + MiddleInitial

Will need to be:

varchar
(
  datalength(LastName) 
  + 2 
  + datalength(FirstName) 
  + 1 
  + datalength(MiddleInitial)
)

You can prove this by selecting a row from Person.Person in AdventureWorks into a new #temp table using the same expression:

Output (note that in AdventureWorks these are nvarchar, so every character counts as two. So adding up ', ' and ' ' adds 6 characters to the string:

enter image description here

Your conversion is happening the other way around - your expression, which is:

varchar
(
   50
   + 2
   + 50
   + 1
   + 100
)
= 203

The implicit conversion (which is totally unnecessary and effectively does nothing) is making your expression (203 characters) conform to the column (250 characters). Again, this has nothing to do with type. Not all conversions involve varchar/nvarchar.

If you want to get rid of the warning, you can wrap the expression in an explicit convert(varchar(250), or add a computed column in the source table that does the same, but that won't really change anything about how the code works or how fast it executes.

Finally, you should probably consider using nvarchar for proper names. You may not think you need it today, but you probably will at some point. Conversion is typically more painful later.

  • Thank you. This was one of my questions. Is CONVERT_IMPLICIT always evil? Yes, we do have major performance issues, but honestly I believe it is because of how the database was designed. However, I was hoping that I could find another reason, flip a switch and be a hero. You appear to be telling me I'm not that lucky today. – Danny Lesandrini Jun 5 '18 at 17:12
  • 1
    @DannyLesandrini No, this particular change is not going to save the world, not even a single baby. It may save a few KBs in plan cache storage. – Aaron Bertrand Jun 5 '18 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.