Home > Performance, SQL Server > The evils of implicit conversions

The evils of implicit conversions

January 18, 2009 Leave a comment Go to comments

I wanted to put up a brief post showing the impact of an implicit conversion on the performance of a query (or not……). In the example i will show an implicit conversion negatively impacting query performance and an implicit conversion that does not impact performance…..

So, we need to setup the test environment using the code below.


SET ANSI_WARNINGS OFF
--********      Create Test Data           ******************
CREATE TABLE #data_test(ukey INT IDENTITY(1,1) PRIMARY KEY , first   VARCHAR(200),second VARCHAR(200))
DECLARE    @first INT@second INT
SELECT
@first = 1
WHILE @first < 250000
BEGIN
INSERT 
#data_test
SELECT  REPLICATE(@first,@first), REPLICATE(@first,@first)
SELECT  @first = @first +1
END
CREATE NONCLUSTERED INDEX
stuf_1 ON #data_test (first)
--********      End of Test Data            ******************

 

Now, with the test data in place we can run the following 2 queries and observe the differences.

/* This uses a variable declared as an NVARCHAR */
EXEC sp_executesql N'SELECT * FROM #data_test WHERE first = (@p0) ', N'@p0 nvarchar(200)',@p0 = N'1'
--Scan count 1, logical reads 3093, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

/* This uses a variable declared as an VARCHAR */
EXEC sp_executesql N’SELECT * FROM #data_test WHERE first = (@p0)’, N’@p0 varchar(200)’,@p0 = ‘1’
–Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Below each statement is the io incurred and the difference on this tiny little table is > 3000 IO’s just because we used unicode (nvarchar) instead of non-unicode (varchar) and i`m sure you can imagine that on a larger table this becomes a significant overhead. So, why has this happened? Lets take a look at the plans.

Query Plan 1

We have 2 very different plans just because we have used a datatype in our query that varies from our underlying datatype. What we see happening here is that SQL Server has introduced a CONVERT_IMPLICIT into the query plan which leads to the additional io.

I also mentioned that the implicit conversion may not always lead to a performance penalty so lets explore that point. To illustrate this we need to change the underlying datatypes in our test table from varchar to nvarchar. I dropped the test table and ran the new create table below and repopulated it with data using the code at the beginning of the post.

CREATE TABLE #data_test(ukey INT IDENTITY(1,1) PRIMARY KEY , first   NVARCHAR(200),second NVARCHAR(200))

Next we run the same queries that we did earlier.

/* This uses a variable declared as an NVARCHAR */
EXEC sp_executesql N’SELECT * FROM #data_test WHERE first = (@p0) ‘, N’@p0 nvarchar(200)’,@p0 = N’1′
–Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* This uses a variable declared as an VARCHAR */
EXEC sp_executesql N’SELECT * FROM #data_test WHERE first = (@p0)’, N’@p0 varchar(200)’,@p0 = ‘1’
–Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The performance is identical! But surely we should still be incurring a conversion overhead because this time the conversion should be from non-unicode data to unicode data. Maybe there is still a conversion so lets take a look at the plans.

image

As you can see, graphically the queries appear to be identical but when we bring up the tooltip for the index seek on the second query which is the one with the non-unicode data we can see the conversion is occurring! The difference here is that the convert_implicit has moved to the seek predicates so it is occurring during the index seek where as previously it was showing as a predicate. I think this is because when you convert from non-unicode to unicode there is no potential for data loss but when you go from unicode to non-unicode there is but i may be wrong and if anyone knows i would be interested to hear from you.

It is possible that there is some additional CPU overhead being incurred by the conversion but i did not detect any notable differences in my testing but it may show on very large data sets.

So to conclude, it is important to know your underlying datatypes and map to them appropriately. It is also worth noting that sometimes code which is automatically generated by a tool may also fall foul of the implicit conversion trap. If a conversion is required i would recommend making it explicitly rather than implicitly. Some conversions may not be obvious as pointed out by Jason Massie who also highlighted a very useful query from Umachandar Jayachandran that will identify cached plans with the convert_implicit operator so that you can examine them in more detail.

Advertisements
Categories: Performance, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: