GET Function - Performance

[eddynaka]

Hi,

I'm using Dapper Extensions for a while and my colleague (that is DBA) showed me some querys that the Dapper Extensions are making are not very good. For example: Table

• Id (pk, int, auto-increment)
• NameField (varchar - 150)

When you create the code to get by NameField, for example: var predicate = Predicates.Field<Audience>(f => f.NameField, Operator.Eq, "something"); var obj = cnx.Get<Table>(predicate);

It generates a query casting as nvarchar, which is losing all my indexed field.

I would like to know if you can set the field type to avoid this kind of operation. If it doesn't exist, do you suggest any other way?

Thanks

[STIHLFredGuanzon]

I am having the same issue. I am using the Predicate to generate the SELECT from conn.GetList<MyItem>(predicate).SingleOrDefault.

My POCO object has the key as type string.

In my SQL Server table the key column is of type varchar(255).

However, when Dapper Extensions autogenerates the SQL statement for the DB, the key column in the query is NVARCHAR(255). This causes a CONVERT_IMPLICIT INDEX SCAN against the autogenerated sql lookup and my column's index, which results in high SQL Server CPU usage.

Is there a way to make the autogenerated SQL use VARCHAR instead?

Otherwise I have to convert my POCO string columns from VARCHAR to NVARCHAR to reduce the high CPU utilization.

Thanks.

[STIHLFredGuanzon]

I'm just going to switch my columns from VARCHAR to NVARCHAR to reduce the CPU usage from Dapper's generated SQL.

However, it would be nice to document that NVARCHAR is required for string columns in the Dapper.Extensions wiki in case anyone else has this performance issue.

[tmsmith]

DapperExtensions does not declare the parameters it names them and passes the value as an object to Dapper. I will look into setting the DBType when passing it to Dapper and see if they corrects the problem.