Dapper use sql + parameter efficient

[bossjien26]

why is the performance using DynamicParameters in the SQL IN clause more than 3 times slower than actually using a composed sql query?

may i ask which parts can optimize the performance of parameters, or declare the type of parameters?

Ex : select * from table where column IN :test.

var list = new List(){"1","2"}
var parameters = new DynamicParameters();
parameters.Add(":test",list);
var table_datas = connection.Query(sql, parameters);

[mgravell]

Well, to start with: there's no need to use DynamicParameters here - just:

var parameters = new { test = list };

should be fine; however, internally, Dapper needs to recognize what your intent is and rewrite this as a multi-parameter query, i.e.

select * from table where column IN (:test0, :test1)

(or something similar). I'd need to put together an intentional benchmark to comment on any specific scenario, but you're also trading your own time in this mix, and security, and a bunch of other things. Note that on some platforms we might also use alternative mechanisms like string_split which does not require us to do quite as much rewriting work (although still some).