Dapper query slow with DateTime type parameter?

dapper oracle parameterized

Question

eg.1

string sql="select * from A where time>=:begin_time and time<=:end_time";

DateTime bt = new DateTime(2013, 04, 19);
DateTime et = new DateTime(2013, 04, 20, 15, 0, 0);
conn.Query<Object>(sql,new {begin_time=bt,end_time=et}).ToList()

the query is slow, the time is 00:00:02.2142132


eg.2

string sql="select * from A where time>=to_date(:begin_time,'yyyy-mm-dd      hh24:mi:ss') and time<to_date=(:end_time,'yyyy-mm-dd hh24:mi:ss')";

conn.Query<Object>(sql,new {begin_time="2013-04-19 0:00:00",end_time="2013-04-20 15:00:00"}).ToList()

the query is fast, the time is 00:00:00.4604229


The following is a new test case. all test record count is 39 from database.

group1: Use Oracle.ManagedDataAccess

test1: 00:00:01.9456767. use dapper and datetime is datetime type(eg1. :begin_time)

test2: 00:00:00.6667549. use dapper and datetime is string type(eg2. to_date(:begin_time,format) )

test3: 00:00:01.8552286. use ado.net/OracleCommand/OracleDataAdapter and datetime is datetime type(eg1. :begin_time)

test4: 00:00:00.0592419. use ado.net/OracleCommand/OracleDataAdapter and datetime is string type(eg2. to_date(:begin_time,format))


group2: Use System.Data.OracleClient

test5: 00:00:00.0184799. use dapper and datetime is datetime type(eg1. :begin_time)

test6: 00:00:00.1158088. use dapper and datetime is string type(eg2. to_date(:begin_time,format) )

test7: 00:00:00.0193022. use ado.net/OracleCommand/OracleDataAdapter and datetime is datetime type(eg1. :begin_time)

test8: 00:00:00.0185583. use ado.net/OracleCommand/OracleDataAdapter and datetime is string type(eg2. to_date(:begin_time,format))

I found if use System.Data.OracleClient two ways are fast. only use Oracle.ManagedDataAccess, the eg1 is sill slow and eg2 is fast.

May be the cause of Oracle.ManagedDataAccess.

Popular Answer

I've been having a similar problem with Oracle.ManagedDataAccess, and I've discovered that DateTime parameters are converted to Timestamp inside the query. If your time column is a Date instead of a Timestamp, Oracle will implicitly convert it to a Timestamp, and it won't be able to use any indexes on that column.

Try rewriting your query to be:

select * from A where time>=cast(:begin_time as date) and time<=:cast(end_time as date)

And see if that improves performance.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why