Nowadays, application performance is the key to success. The amount of processed data by applications is very large. Very often access to the database is a bottleneck in applications. Choice to use a large ORM to access our database is very often first step to performance problems. We can talk for long hours about problems associated with using an ORM, but in this article I will focus on the problem of implicit data conversion.

All the examples in the article are based on NHibernate, which is a mature, open source object-relational mapper for the .NET framework.

Data conversion ? What is going on ?

During working with the SQL Server, you will meet with data conversion for certain. For example suppose you need to compare two numbers, first one configured with a numeric data type and second one configured with a string data type. To perform the comparison, one of those values will be converted to the other’s data type. SQL Server supports two kinds of data type conversion: implicit and explicit. Implicit means that the database engine will convert the data type automatically, in a process invisible for the user. Explicit conversions use the CAST or CONVERT functions where data type must be specified.

The following illustration show all explicit and implicit data type conversions.

Explicit - implicit conversion table

Source: MSDN

The most column-side implicit conversion occurs when the column type is varchar or nvarchar, and the application code passes a parameter that is opposite to declared. For example: column type is varchar but parameter from application is nvarchar. To demonstrate the performance overhead associated with column-side implicit conversions I have created simple test. To simulate this type of scenario I created table with sample data was created.

--Create Table
CREATE TABLE Vehicle
(
  [VehicleId] uniqueidentifier NOT NULL,
  [Name] nvarchar(255) NOT NULL,
  [LicensePlate] varchar(50) NOT NULL
);

In the next step, I wrote parameterized query with correct types:

-- Correct query
DECLARE @Name nvarchar(255)
SET @Name = 'TestVehicle'

DECLARE @LicensePlate varchar(50)
SET @LicensePlate = 'SO11111'

SELECT * 
FROM [SqlImplicitDataConversion].[dbo].[Vehicle]
WHERE [Name] = @Name AND [LicensePlate] = @LicensePlate

The query result:

Implicit data conversion - correct query plan

As we can see query execution plan does not contain warnings and the query is executed correctly.

Now I will show you the incorrect query, where Name column type is declare as varchar and LicnesePlate as nvarchar. Exactly the same query is generated by NHibernate.

-- Query with implicit conversion

DECLARE @Name varchar(255) -- incorrect type
SET @Name = 'TestVehicle'

DECLARE @LicensePlate nvarchar(50) -- incorrect type
SET @LicensePlate = 'SO11111'

SELECT * 
FROM [SqlImplicitDataConversion].[dbo].[Vehicle]
WHERE [Name] = @Name AND [LicensePlate] = @LicensePlate

The query result:

Implicit data conversion - correct query plan

As we can see, the query execution plan contains warning with CONVERT_IMPLICIT, also we can also see. Moreover, we can see which column applies to warning. Conversion of data is time-consuming process, because SQL Server at the time of execution query has to convert all elements in selected column, and only then can execute the query. For complex queries the difference in execution time can be tremendous. To avoid this problem you should know how the ORM converts data types during creating SQL query. SQL Server and the .NET Framework are based on different type systems. Both SQL Server and .NET Framework types are also represented by enumerations in the DBType and SqlDbType classes, which you can use when specifying SqlParameter data types. The following table shows data type mapping used by NHibernate.

Data type mapping in NHibernate

Source: DEVIOBLOG

What we can do ?

In large projects where a lot of different queries are performed, it is hard to verify all execution plans and identify where the data conversion occurs. In this case, dynamic tables available in SQL Server might be helpful. You will find information about completed queries and their execution plans there. Each plan includes information about data conversion occurrences.

SELECT total_worker_time/execution_count AS [Avg CPU Time],
query_plan, qs.*, dest.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as dest
where cast(query_plan as nvarchar(max)) like ('%CONVERT_IMPLICIT%')
order by last_execution_time desc

Real Example

The best way to prove the problem is to present a real example. I wrote a simple query for table with 3 million items. One of the columns is declared as varchar type. The query column was declared as nvarchar deliberately.

GO
DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
DECLARE @BookIdentity nvarchar(50)
SET @BookIdentity = 'bd9828ee-8192-4b79-99a6-feb4e1bc9102'

SET @t1 = GETDATE();
SELECT *
  FROM [dbo].[Book]
  WHERE BookIdentity = @BookIdentity
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;
GO

GO

The following chart shows the time it takes to execute the query depending on the type of data. The difference is huge.

Execution time

NHibernate provides the ability to change the data type in the mapping. When you are using varchar type in a database you should add this line to mapping for each string property.

model.Override<Book>(map => map.Map(x => x.BookIdentity).CustomType("AnsiString"));

Summary

To sum up, use of an ORM as a way to access the database can cause a lot of problems. In most cases we do not know the exact implementation of the used components and we do not have control over the generated queries, so it is good practice to check whether generated queries meet our expectations. For NHibernate you can use NHibernate.Profiler. It is a real-time visual debugger allowing to track executed queries.

Next Post Previous Post

blog comments powered by Disqus