CODEWITHSUNDEEP
c#dapper
beehuang
beehuang

Reputation: 369

Dapper sql too slow when in parameter is list string

I use raw SQL with parameters in Dapper. Query speed is normal.

Something like this:

string sql = "SELECT * FROM SomeTable WHERE messages IN ('Volvo', 'BMW', 'Ford', 'Mazda')"
var results = conn.Query(sql);

When I change parameters with @messages, the speed is too slow:

string sql = "SELECT * FROM SomeTable WHERE messages IN (@cars)"
string[] cars = { "Volvo", "BMW", "Ford", "Mazda" };
var results = conn.Query(sql, new {cars});

PS: string[] size is almost 300.

Upvotes: 1

Views: 2723

Answers (2)

tyeem
tyeem

Reputation: 41

If you pass string as parameter, Dapper treats it as nvarchar type. So if your messages is varchar type and the SomeTable is huge, even if it's indexed, the speed could be really slow. DbString may solve this problem.

cars.ToList().Select(x => new DbString { Value = x, IsFixedLength = false, IsAnsi = true });

Upvotes: 4

Cetin Basoz
Cetin Basoz

Reputation: 23797

(would be a mess as a comment) (I think there might be some syntax error in second one and there shouldn't be parentheses.)

The two queries would generate different SQL sent to backend. ie: with MS SQL server they would look like:

First one:

SELECT * FROM SomeTable WHERE messages IN ('Volvo', 'BMW', 'Ford', 'Mazda')

Second one:

exec sp_executesql N'SELECT * FROM SomeTable WHERE messages IN (@cars1, @cars2, @cars3, @cars4)',
N'@cars1 nvarchar(4000),@cars2 nvarchar(4000),@cars3 nvarchar(4000),@cars4 nvarchar(4000)',
@cars1=N'Volvo',@cars2=N'BMW',@cars3=N'Ford',@cars4=N'Mazda'

IMHO there are better ways of doing IN queries depending on your backend.

Upvotes: 1

Related Questions