使用 Dapper + Oracle.ManagedDataAccess 查詢 Oacle Date 欄位很慢的原因

先 Google 的話,可以看到這篇解答 Dapper query slow with DateTime type parameter?

明確指出 Oracle.ManagedDataAccess 在設定 DbType 時,會將 DbType.DateTime 轉換為 OracleDbType.TimeStamp,

並且提供修改 SQL 的方向 [ Cast ( As Date) ],讓比對 Oracle Date 欄位的索引才有用的解決方法。

前言

如果你的系統也使用 Dapper,且系統中大部分並不是在做 TimeStamp 比對的話,

而是幾乎都用Oracle Date (年月日時分秒)型態的話,

這裡提供一個如何不從SQL著手,進而從底層修改預設應該是用 Date 來比對的方法,

目前我遇過大部分的應用,Table 幾乎都是開 Oracle Date 型態的欄位。


問題

使用 Dapper 執行 SQL 查詢 where OrderDate = :OrderDate 很慢 (OrderDate 是 Date 型態)

原因

OrderDate 有索引,但索引沒作用,

因 Dapper (SqlMapper.typeMap) 預設將 .NET 的 DateTime 型態轉換為 DbType.DateTime,

            typeMap = new Dictionary<Type, DbType>
                      {
                          [typeof(byte)] = DbType.Byte,
                          [typeof(sbyte)] = DbType.SByte,
                          [typeof(short)] = DbType.Int16,
                          [typeof(ushort)] = DbType.UInt16,
                          [typeof(int)] = DbType.Int32,
                          [typeof(uint)] = DbType.UInt32,
                          [typeof(long)] = DbType.Int64,
                          [typeof(ulong)] = DbType.UInt64,
                          [typeof(float)] = DbType.Single,
                          [typeof(double)] = DbType.Double,
                          [typeof(decimal)] = DbType.Decimal,
                          [typeof(bool)] = DbType.Boolean,
                          [typeof(string)] = DbType.String,
                          [typeof(char)] = DbType.StringFixedLength,
                          [typeof(Guid)] = DbType.Guid,
                          [typeof(DateTime)] = DbType.DateTime,
                          [typeof(DateTimeOffset)] = DbType.DateTimeOffset,
                          [typeof(TimeSpan)] = DbType.Time,

 

而 OracleParameter 在設定 DbType 屬性時,會將 DbType.DateTime 轉換為 OracleDbType.TimeStamp,

this.m_oraDbType = (Oracle.ManagedDataAccess.Client.OracleDbType)OraDb_DbTypeTable.dbTypeToOracleDbTypeMapping[(int)dbType];

 DbType 轉換整數

			OraDb_DbTypeTable.dbTypeToOracleDbTypeMapping[5] = 106;
			OraDb_DbTypeTable.dbTypeToOracleDbTypeMapping[6] = 123;

 整數轉換為 OracleDbType

		TimeStamp = 123,

從這裡就可見欄位型態與參數型態就沒有對應 ,

已經知道來龍去脈,Oracle.ManagedDataAccess 我想是不太能改,

那就從 Dapper 下手,所幸 Dapper 提供 SqlMapper.AddTypeMap 能新增或修改 typeMap 所對應的型態,

從 OracleCommand Object 文檔中,可以知道 Date 型態欄位是對應 DbType.Date,所以我們只要在程式初始化執行一次:

SqlMapper.AddTypeMap(typeof(DateTime), DbType.Date);

 於是只要是 .NET DateTime 型態 → 經過 Dapper 為 DbType.Date → 經過 Oracle.ManagedDataAccess 為 OracleDbType.Date,

這樣預設就能讓 Oracle Date 型態索引有效。


TimeStamp 查詢

如果說你真的需要用 TimeStamp 參數時,

Dapper 的 DynamicParameters.Add 提供自訂 DbType,就能自己傳 DbType.DateTime 來使用。


參考文章:

OracleCommand Object

https://github.com/StackExchange/dapper-dot-net

http://stackoverflow.com/questions/28423294/dapper-query-slow-with-datetime-type-parameter