Implicit Conversions
This page will demonstrate the effect of querying data with an different data type in the statement compared to the row in the actual table.
Another performance killer to look for within an execution plan is something called ‘Implicit Conversions’. This is when two different data types used either in a join or simple select statement (as shown below) are different. For example if one table has a column data type varchar and the query is using Nvarchar you will see an Implicit conversion in the execution plan. Not only is this extra work for sql server, it will mean that any indexes that may have been used to help the query will be negated.
In the examp0le below, I am using a copy of the stackoverflow database. I first made a copy of the users table to run my tests on and named the table User2. I chose a query that selects a highly selective ‘displayname’ from the table (returns 1 row) and I also made an non clustered index to help the query.
So when I ran the above two SELECT statements the results where as follows. These results returned in under a second, so you be thinking “so what is the problem…?”
If you look into the execution plan that was generated by these two queries (note they where run as a batch) you will start to see something very interesting.
Below is the execution plan of the two statements:
You can see the top execution plan has a cost of 100% relative to the batch whereas the bottom query is 0%. *Discalimer* – these numbers are not always a hard and fast rule which tells you which part of a query batch is the most expensive, but when good data exists on your statistics then it’s a good indication of where you can start to look to improve things.
You can see that from the plans generated the top plan is doing an ‘Index Scan’ while the bottom one is doing an ‘index seek’. Why is that? both queries where selecting the same data?
Well the answer to that also lies within the top plan and the yellow warning triangle on the SELECT operator.
If you mouse over the operator you will what sql server is complaining about – AN IMPLICIT CONVERSION.
This is because of the slight difference in the first SELECT query. The where clause is asking for data type N’VARCHAR which is ‘unicode’. Don’t forget, we changed the column ‘DisplayName’ data type of the users table to type varchar(50)
Furthermore, if you look at the amount of Logical reads the two queries produced, you can see why on a busy system if a query like this was running thousands of times per second, you would definitely care about it!!
The top query produces 8616 logical reads whereas the second one does just 4 – a massive difference.
click image to view
Summary
As you can see from the above example, choosing the correct data type at both the physical design stage of the database and using the correct data type in your code matters. If the two data types are not compatible SQL server will convert them implicitly and forgo any use of any perfectly crafted indexes you have developed to make the query run fast.
NB if the column was Nvarchar in the above example you don’t get the issue.
sqlnights.com 