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
CONVERT functions where data type must be specified.
The following illustration show all explicit and implicit data type conversions.
The most column-side implicit conversion occurs when the column type is
nvarchar, and the application code passes a parameter that is opposite to declared.
For example: column type is
varchar but parameter from application is
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:
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
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:
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
SqlDbType classes, which you can use when specifying
SqlParameter data types.
The following table shows data type mapping used by NHibernate.
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
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
The query column was declared as
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.
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"));
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.