Performance Tuning–Data Type Mismatch February 16th, 2011

Vinod Kumar

I was recently at a customer discussion and something strange happened during the code review. As best practice, it is advised when using stored procedure not make mistakes of sending the data in wrong data types. These induce an implicit conversion that can cause performance bottlenecks inside SQL Server. These basics are often forgotten and missed as part of the process.

For illustration purposes I used a very simple table for example.

CREATE TABLE test
  (
     col1 VARCHAR(10),
     col2 CHAR(200)
  ) 

The stored procedure is also made simple. The catch here is the parameter as NVARCHAR whilst the column is VARCHAR. In many places this confusion is common. In the name of internationalization I have seen people just use the NVARCHAR and forget the same while writing the SPs.

CREATE PROCEDURE Proc_test_mismatch @MyParam NVARCHAR(10)
AS
  SELECT *
  FROM   test
  WHERE  col1 = @MyParam 

SET statistics profile ON
GO
EXEC Proc_test_mismatch N’1000000′
GO
SET statistics profile OFF 

Execution of the above SP creates an interesting Plan that one needs to keep an eye on. The hidden perf deterrent is the IMPLICIT CONVERT that is happening inside SQL Server.

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Test].[idx1]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[Test].[col1],0)=[@MyParam]))

You might argue what is the biggest drawback because of this. Actually, behind the scenes there are two problems that can be anticipated.

  1. First, when SQL Server has to convert a column, it is no longer able to use index.  
  2. Additionally, cardinality estimate will be off as well.  It can no longer use statistics on the column being converted

So something that look as simple as not using the “N” in front of the data types can help you manage and improve performance just because the data is designed like that. The solution is as simple as possible.

  1. If it’s a stored procedure, the easiest solution is to change the stored procedure parameters to match the data type of the column the parameter is comparing against.
  2. If the columns of two tables don’t match when joining, one of the column’s data type needs to be changed to match.

Once we do the simple steps, the execution plans will become clean. Check the SEEK operation on execution of the same SP now after the changes.

  |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Test].[idx1]), SEEK:([tempdb].[dbo].[Test].[col1]=[@MyParam]) ORDERED FORWARD)

So, just go around and check if you have such mistakes in your products code. And drop me a line here if you found one. All the Best !!!

Tags: , , , , , ,

This entry was posted on Wednesday, February 16th, 2011 at 12:22 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


5 Responses to “Performance Tuning–Data Type Mismatch”

  1. Nice Post, Quite helpful for SQL Developers/DBA.

  2. Excellent post! I will add this to my favourites and reference it whenever I need to ask a developer to rework in order to rectify a data-type mismatch.

  3. Srikanth says:

    Very Very good and simple example…

  4. […] This post was mentioned on Twitter by Vinod Kumar M (SQL), kashyapa. kashyapa said: RT @vinodk_sql: Performance Tuning–Data Type Mismatch http://f.ast.ly/Vhuhg […]

Leave a Reply