Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs. For more advanced querying scenarios, Dapper Extensions provides a predicate system. The goal of this library is to keep your POCOs pure by not requiring any attributes or base class inheritance.
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(f => f.NameField, Operator.Eq, "something");
var obj = cnx.Get
(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?
I am having the same issue. I am using the Predicate to generate the SELECT from conn.GetList(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.
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.
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.
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
When you create the code to get by NameField, for example: var predicate = Predicates.Field(f => f.NameField, Operator.Eq, "something");
var obj = cnx.Get(predicate);
STIHLFredGuanzon
commented
8 years ago
STIHLFredGuanzon
commented
8 years ago
tmsmith
commented
8 years ago
- © Githubissues.
- Githubissues is a development platform for aggregating issues.
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
I am having the same issue. I am using the Predicate to generate the SELECT from conn.GetList(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.
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.
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.