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.
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 });
(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.
messagesis indexed or not.string[] size is almost 300.did you try with a raw string with 300 items? It's not possible to pass an array of values as a parameter, so Dapper will actually generate anINclause with the values. The two queries should be identical.sometableis too small, the server may decide to use a scan instead of a seek, especially since it needs to retrieve all columns. BTW without the table schema and row counts, we're as blind as the server.