Index Seek - ConvertImplicit

• luiz.sopmac

  Right there with Babe

  Points: 782

  More actions

  January 12, 2017 at 8:49 am

  #317493

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 12, 2017 at 8:59 am

  #1922333

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  Look at the data type of the parameter and the data type of the column to which it is being compared to in the query.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 12, 2017 at 8:59 am

  #1922334

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  You say that the variable you are passing to the parameter is vc60 and the column is vc60...check the parameter datatype isn't nvarchar.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  January 12, 2017 at 9:11 am

  #1922343

  ChrisM@Work - Thursday, January 12, 2017 8:59 AM

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  You say that the variable you are passing to the parameter is vc60 and the column is vc60...check the parameter datatype isn't nvarchar.

  That would make sense because the nvarchar has a higher data type precedence than the varchar.  It's one of the most common implicit casts.  The data type of your parameter must exactly match the data type of the column.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• luiz.sopmac

  Right there with Babe

  Points: 782

  More actions

  January 12, 2017 at 9:14 am

  #1922346

  Lynn Pettis - Thursday, January 12, 2017 8:59 AM

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  Look at the data type of the parameter and the data type of the column to which it is being compared to in the query.

  Hi!

  My parameter has the same DATA TYPE of the column.
  It's basically:

  Declare @par varchar(60)
  SET @par = 'parater to compare'

  EXEC parameter @par

• luiz.sopmac

  Right there with Babe

  Points: 782

  More actions

  January 12, 2017 at 9:16 am

  #1922347

  Ed Wagner - Thursday, January 12, 2017 9:11 AM

  ChrisM@Work - Thursday, January 12, 2017 8:59 AM

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  You say that the variable you are passing to the parameter is vc60 and the column is vc60...check the parameter datatype isn't nvarchar.

  That would make sense because the nvarchar has a higher data type precedence than the varchar.  It's one of the most common implicit casts.  The data type of your parameter must exactly match the data type of the column.

  Yeah,  data type precedence would be a problem, but both parameter and column contains has the same data type (varchar60)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 12, 2017 at 9:16 am

  #1922348

  Ed Wagner - Thursday, January 12, 2017 9:11 AM

  ChrisM@Work - Thursday, January 12, 2017 8:59 AM

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  You say that the variable you are passing to the parameter is vc60 and the column is vc60...check the parameter datatype isn't nvarchar.

  That would make sense because the nvarchar has a higher data type precedence than the varchar.  It's one of the most common implicit casts.  The data type of your parameter must exactly match the data type of the column.

  Good spot Ed - it would read CONVERT_IMPLICIT(nvarchar(60. In which case I'd guess it's a size mismatch, which doesn't affect SARGability. In any case it's the parameter which is converted, not the column.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 12, 2017 at 9:25 am

  #1922352

  luiz.sopmac - Thursday, January 12, 2017 9:14 AM

  Lynn Pettis - Thursday, January 12, 2017 8:59 AM

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  Look at the data type of the parameter and the data type of the column to which it is being compared to in the query.

  Hi!

  My parameter has the same DATA TYPE of the column.
  It's basically:

  Declare @par varchar(60)
  SET @par = 'parater to compare'

  EXEC parameter @par

  What datatype is the parameter declared as - inside the stored procedure?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• luiz.sopmac

  Right there with Babe

  Points: 782

  More actions

  January 12, 2017 at 9:45 am

  #1922363

  ChrisM@Work - Thursday, January 12, 2017 9:25 AM

  luiz.sopmac - Thursday, January 12, 2017 9:14 AM

  Lynn Pettis - Thursday, January 12, 2017 8:59 AM

  luiz.sopmac - Thursday, January 12, 2017 8:49 AM

  Hi all!

  i'm running a stored procedure, which receives a single parameter and returns a single row to the client.
  The query inside is a select statement in a single table and the plan is just a non clustered index seek with a key lookup (to get all the coluns needed by the select).
  The procedure is pretty fast, but when i look into the actual exection plan, i'm seeing something that i was not expecting :

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(60),[@parametro1],0)">

  the variable that i'm passing to my procedure is a varchar(60) and the field on the table is a varchar(60) as well.
  Why SQL server is converting my parameter?

  

  Thanks,

  Luiz

  Look at the data type of the parameter and the data type of the column to which it is being compared to in the query.

  Hi!

  My parameter has the same DATA TYPE of the column.
  It's basically:

  Declare @par varchar(60)
  SET @par = 'parater to compare'

  EXEC parameter @par

  What datatype is the parameter declared as - inside the stored procedure?

  There's no local variables inside the stored procedure.
  it's something like this:

  CREATE PROCEDURE [dbo].[myproc]
  (
  @param varchar(60)
  )
  AS
  BEGIN

  SELECT
  *
  from table
  WHERE column = @param

  END

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  January 12, 2017 at 9:59 am

  #1922368

  However, this conversion isn't preventing the use of statistics or the index. Fix it? Sure. But it's not causing you performance headaches.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• luiz.sopmac

  Right there with Babe

  Points: 782

  More actions

  January 12, 2017 at 10:10 am

  #1922373

  Grant Fritchey - Thursday, January 12, 2017 9:59 AM

  However, this conversion isn't preventing the use of statistics or the index. Fix it? Sure. But it's not causing you performance headaches.

  Yeap! the pland and stats are pretty good. it was just a curiosity 🙂

  Thanks