Click here to Skip to main content
13,736,491 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

18.7K views
7 bookmarked
Posted 16 May 2013
Licenced CPOL

Be Careful with varchars in Dapper!

, 16 May 2013
Rate this:
Please Sign up or sign in to vote.
In this post, I will show you an interesting problem that we experienced when querying a SQL Server database with Dapper.

In this post, I will show you an interesting problem that we experienced when querying a SQL Server database with Dapper. I will use a simplified data model and a sample application so you could reproduce the issue on your own. Our sample table will look as follows:

create table Stats (
    StatsDay datetime not null,
    EventName varchar(100) not null,
    Item varchar(100) not null,
    Value int null,
    constraint PK_Stats primary key(Item,EventName,StatsDay)
)

For our test scenario, let’s fill the Stats table with aggregated query statistics gathered by SQL Server:

insert into Stats(StatsDay, EventName, Item, Value) 
select CONVERT(date,last_execution_time,101), 'query', 
    convert(varchar(max), query_plan_hash, 1), sum(execution_count) 
    from sys.dm_exec_query_stats group by CONVERT(date,last_execution_time,101), 
    convert(varchar(max), query_plan_hash, 1)

Now, it’s time to query this table with Dapper. Our sample application will return the number of executions for a query plan with hash provided by the user:

using System;
using System.Data.SqlClient;
using Dapper;
public class Program 
{
    class Stat 
    {
        public DateTime StatsDay { get; set; }
        public String EventName { get; set; }
        public String Item { get; set; }
        public int Value { get; set; }
    }
    public static void Main(String[] args) {
        if (args.Length == 0) {
            Console.WriteLine("You must provide query plan hash");
            return;
        }
    
        using (var conn = new SqlConnection(
          "Server=localhost;Database=testdb;Trusted_Connection=true")) {
            conn.Open();
            
            var query = conn.Query<Stat>(
              "select * from Stats where Item = @queryPlanHash " + 
              "and EventName = 'query' and StatsDay > @startDate", 
              new { queryPlanHash = args[0], startDate = DateTime.Today.AddDays(-7) });
            
            foreach (var st in query) {
                Console.WriteLine("Number of executions of {0} on {1} was {2}", 
                                  args[0], st.StatsDay, st.Value);
            }
        }
    }
}

After running the application with SQL Profiler listening, we should find in its log a query similar to the one below:

exec sp_executesql N'select * from Stats where Item = @queryPlanHash and 
  EventName = ''query'' and StatsDay > @startDate',N'@queryPlanHash 
  nvarchar(4000),@startDate datetime',@queryPlanHash=N'0x00D2A282AC36D843',
  @startDate='2013-05-08 00:00:00'

Query plan:

Capture

When the number of rows is high, you may even end up with an index scan. So why the query didn’t result in a simple index seek? SQL Server 2012 gives us a hint, showing a warning on the SELECT node:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(100),[testdb].[dbo].[Stats].[Item],0)) may affect “CardinalityEstimate” in query plan choice.

When looking closer at the query generated by Dapper, we can see that the text parameters are by default sent as nvarchars, when in our Stats table, the item column is of type varchar. This forces SQL Server to convert a parameter from multi- to single-byte string. Fortunately, we can easily fix this by just informing Dapper that our parameter should be a single-byte string (there is a special DbString type in Dapper for this purpose):

var query = conn.Query<Stat>(
  "select * from Stats where Item = @queryPlanHash and EventName = 'query' and StatsDay > @startDate", 
  new { queryPlanHash = new DbString() { Value = args[0], IsAnsi = true, Length = 100 }, 
  startDate = DateTime.Today.AddDays(-7) });

After the fix, the query looks as follows...

exec sp_executesql N'select * from Stats where Item = @queryPlanHash 
  and EventName = ''query'' 
  and StatsDay &gt; @startDate',N'@queryPlanHash 
  varchar(100),@startDate datetime',
  @queryPlanHash='0x00D2A282AC36D843',@startDate='2013-05-08 00:00:00'

...and generates the anticipated index seek in the query plan:

Capture2

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sebastian Solnica
Software Developer (Senior)
Poland Poland
Interested in tracing, debugging and performance tuning of the .NET applications.

My twitter: @lowleveldesign
My website: http://www.lowleveldesign.org

You may also be interested in...

Comments and Discussions

 
QuestionIt's worse than you think Pin
jcoehoorn28-Apr-15 9:13
memberjcoehoorn28-Apr-15 9:13 
GeneralI've seen similar problems in SSIS Pin
PIEBALDconsult7-Nov-14 14:03
protectorPIEBALDconsult7-Nov-14 14:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.180920.1 | Last Updated 16 May 2013
Article Copyright 2013 by Sebastian Solnica
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid