Entity Framework 8查询Oracle性能劣于直接命令的问题及优化咨询

问题描述 #

我在开发C#项目时，需要从Oracle 19数据库获取数据，使用Entity Framework 8处理查询时，发现其性能远低于直接执行Oracle命令；且EF生成带脚本的查询而非简单SQL，怀疑这是问题根源。现咨询：

1. 为何两者性能差异显著？
2. 有哪些EF优化手段可缩短查询耗时？

测试代码与环境 #

简化版测试代码 #

var cnxStr = "......";

using UlisContext ulisRivpDc = new UlisContext( cnxStr );

string p = "PL/2672262";

double withAdo;
Stopwatch sw = Stopwatch.StartNew();
using( var cnx = new Oracle.ManagedDataAccess.Client.OracleConnection( cnxStr ) )
using( var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand( "select BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD FROM BIEVA WHERE BIDOS_NUM = :p", cnx ) ) {
    cmd.Parameters.Add( ":p", p );
    cnx.Open();
    using( var reader = cmd.ExecuteReader() ) {
        while( reader.Read() ) {
            //
        }
    }
}
withAdo = sw.Elapsed.TotalMilliseconds;
Console.WriteLine( "ADO : " + withAdo ); // 424.7524

using( var cnx = new Oracle.ManagedDataAccess.Client.OracleConnection( cnxStr ) )
using( var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand( "SELECT BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD FROM BIEVA WHERE BIDOS_NUM = :p", cnx ) ) {
    cmd.Parameters.Add( ":p", p );
    cnx.Open();
    using( var reader = cmd.ExecuteReader() ) {
        while( reader.Read() ) {
            //
        }
    }
}
withAdo = sw.Elapsed.TotalMilliseconds;
Console.WriteLine( "ADO (2nd time) " + withAdo ); // 433.4318

Console.WriteLine();
Console.WriteLine();

ulisRivpDc.BIEVAs.AsNoTracking().First();

sw.Restart();
List<BIEVA> it = ulisRivpDc.Database.SqlQuery<BIEVA>( $"SELECT BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD, BIDOS_NUM FROM BIEVA WHERE BIDOS_NUM = {p}" ).ToList();
double withEfSqlQuery = sw.Elapsed.TotalMilliseconds;
Console.WriteLine("EF SqlQuery : " + withEfSqlQuery ); // 3416.5022
sw.Restart();
it = ulisRivpDc.Database.SqlQuery<BIEVA>( $"SELECT BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD, BIDOS_NUM FROM BIEVA WHERE BIDOS_NUM = {p}" ).ToList();
withEfSqlQuery = sw.Elapsed.TotalMilliseconds;
Console.WriteLine( "EF SqlQuery (2nd time) : " + withEfSqlQuery ); // 1865.5861

Console.WriteLine();
Console.WriteLine();

Console.WriteLine( ulisRivpDc.Database.SqlQuery<BIEVA>( $"SELECT BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD, BIDOS_NUM FROM BIEVA WHERE BIDOS_NUM = {p}" ).ToQueryString() );

// Sql Procedure generated by EF
/*
DECLARE
l_sql     varchar2(32767);
l_cur     pls_integer;
l_execute pls_integer;

BEGIN
l_cur := dbms_sql.open_cursor;
l_sql := 'SELECT BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD, BIDOS_NUM FROM BIEVA WHERE BIDOS_NUM = :p0';
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, 'p0', N'PL/2672262');
l_execute:= dbms_sql.execute(l_cur);
dbms_sql.return_result(l_cur);
END;
*/

sw.Restart();
it = ulisRivpDc.BIEVAs.AsNoTracking().Where( it => it.BIDOS_NUM == p ).ToList();
double withDbSet = sw.Elapsed.TotalMilliseconds;
Console.WriteLine( "DbSet FirstOrDefault (1st time) : " + withDbSet ); // 1876.4951
sw.Restart();
it = ulisRivpDc.BIEVAs.AsNoTracking().Where( it => it.BIDOS_NUM == p ).ToList();
double withDbSet2 = sw.Elapsed.TotalMilliseconds;
Console.WriteLine( "DbSet FirstOrDefault (2nd time) : " + withDbSet2 ); // 1864.8654
sw.Restart();
it = ulisRivpDc.BIEVAs.AsNoTracking().Where( it => it.BIDOS_NUM == p ).ToList();
double withDbSet3 = sw.Elapsed.TotalMilliseconds;
Console.WriteLine( "DbSet FirstOrDefault (3rd time) : " + withDbSet3 ); // 1870.6577

Console.WriteLine();
Console.WriteLine();
Console.WriteLine(ulisRivpDc.BIEVAs.Where( it => it.BIDOS_NUM == p ).ToQueryString() );

/*
DECLARE
l_sql     varchar2(32767);
l_cur     pls_integer;
l_execute pls_integer;

BEGIN
l_cur := dbms_sql.open_cursor;
l_sql := 'SELECT "b"."BIDOS_NUM", "b"."BIEVA_NUM", "b"."BIPDO_NUM", "b"."BIPCD_NUM", "b"."BITEV_COD"
FROM "BIEVA" "b"
WHERE "b"."BIDOS_NUM" = :p_0';
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ':p_0', N'PL/2672262');
l_execute:= dbms_sql.execute(l_cur);
dbms_sql.return_result(l_cur);
END;
*/

实体类定义 #

[Table( "BIEVA" ), PrimaryKey( nameof( BIDOS_NUM ), nameof( BIEVA_NUM ), nameof( BIPDO_NUM ) )]
internal class BIEVA {
    public int BIEVA_NUM { get; set; }
    public int BIPCD_NUM { get; set; }
    public int BIPDO_NUM { get; set; }
    public string? BITEV_COD { get; set; }
    public string BIDOS_NUM { get; set; } = null!;
}

上下文类定义 #

class UlisContext : DbContext {
    private readonly string CnxStr;

    public UlisContext( string cnxStr ) : base() {
        CnxStr = cnxStr;
    }

    protected override void OnConfiguring( DbContextOptionsBuilder optionsBuilder ) {
        optionsBuilder.UseOracle( CnxStr );
    }

    public DbSet<BIEVA> BIEVAs { get; set; }
}

性能差异原因分析 #

1. PL/SQL包装脚本的额外开销
   EF针对Oracle默认使用dbms_sql包封装查询，这个过程需要创建游标、解析SQL、绑定变量、执行并返回结果，比ADO.NET直接提交简单SQL多了一层PL/SQL执行逻辑，数据库端需要处理更多步骤，自然增加了耗时。

2. EF初始化与查询编译开销
   即使做了预热（调用First()），EF第一次执行查询时仍需完成模型元数据加载、查询表达式树编译等工作，后续查询虽有缓存，但整体初始化成本比ADO.NET高。

3. 参数类型映射问题
   EF生成的脚本中字符串参数被标记为Unicode类型（N'PL/2672262'），如果Oracle表中BIDOS_NUM是非Unicode字符类型，会触发隐式类型转换，导致索引失效或额外的转换开销；而ADO.NET直接绑定参数，类型匹配更精准。

4. 查询计划缓存效率
   Oracle对直接提交的SQL语句缓存查询计划的效率更高，而EF生成的PL/SQL包装脚本结构复杂，变量绑定方式可能导致查询计划复用率降低，每次执行都需要额外的解析步骤。

EF优化手段 #

• 禁用PL/SQL执行包装
  修改上下文配置，强制EF直接执行原生SQL而非PL/SQL脚本，这是最直接的优化方式：

protected override void OnConfiguring( DbContextOptionsBuilder optionsBuilder ) {
    optionsBuilder.UseOracle(CnxStr, o => o
        .UseOracleSQLCompatibility("19")
        .DisablePLSQLExecution());
}

• 使用参数化查询，避免字符串拼接
  你的SqlQuery用了字符串插值，存在SQL注入风险且无法复用查询计划，改成参数化形式：

var it = ulisRivpDc.Database.SqlQuery<BIEVA>(
    "SELECT BIEVA_NUM, BIPCD_NUM, BIPDO_NUM, BITEV_COD, BIDOS_NUM FROM BIEVA WHERE BIDOS_NUM = :p",
    new OracleParameter(":p", p)
).ToList();

• 提前预热EF上下文
  在应用启动阶段初始化上下文并执行一次简单查询，让EF完成模型编译、元数据加载等初始化工作，避免业务查询时的首次开销。

• 确保数据库索引优化
  检查BIEVA表的BIDOS_NUM字段是否创建了索引，这是提升查询性能的基础——不管用EF还是ADO.NET，全表扫描的性能都会远低于索引查询。

• 坚持使用AsNoTracking()
  确保所有只读查询都加上该方法，避免EF的变更追踪带来的内存和性能开销。

• 优化查询编译缓存
  EF 8支持查询编译缓存，确保查询逻辑是可缓存的（比如使用参数而非动态字符串），这样后续相同查询可以直接复用编译好的计划。

• 更新Oracle客户端版本
  确保使用的Oracle.ManagedDataAccess.Client是最新稳定版，与Oracle 19c完全兼容，旧版本可能存在性能或兼容性问题。

内容的提问来源于stack exchange，提问作者Théo Uzan