Question
I have a table in Oracle named MESSAGE and it has a column TERMINALID (VARCHAR2 8 NOT NULL); and there is an index for this column. There are about 300,000,000 records in this table.
Now, this works fine (0.0 seconds):
using (var con = new OracleConnection(connectionString2))
{
try
{
con.Open();
var parameters = new DynamicParameters();
parameters.Add("PTerminalId", value: "04447777", dbType: System.Data.DbType.AnsiString);
Console.WriteLine("ora - messages #{0}", con.Query<decimal>("SELECT COUNT(*) FROM MESSAGE WHERE TERMINALID=:PTerminalId", param: parameters).FirstOrDefault());
}
finally { con.Close(); }
}
And this fails (times out):
using (var con = new OracleConnection(Properties.Settings.Default.GSMConnectionString2))
{
try
{
con.Open();
Console.WriteLine("ora - messages #{0}", con.Query<decimal>("SELECT COUNT(*) FROM GSM.MESSAGE WHERE TERMINALID=:PTerminalId", param: new { PTerminalId = "04447777" }).FirstOrDefault());
}
finally { con.Close(); }
}
Why it's so?
Accepted Answer
Basically: what @Vincent said: a datatype mismatch can make indexes unusable.
Without additional information, dapper makes some assumptions. One of those assumptions is that your string is going to continue to be unicode at the database, so it defaults to DbType.String. In most cases this is either correct or adequate, but it can misbehave in some scenarios - which is why we also provide the facility (via DynamicParameters) to be more explicit about both the type and length of the string parameter to add.