实体框架核心优先查询与后续查询性能

最近我一直在想,在实体框架中,第一个查询和随后的相同查询性能之间存在巨大差异的原因是什么?是不是因为实体框架有某种内部缓存来缓存查询结果,而对同一查询的所有后续调用都使用该缓存结果?

为了正确理解这个问题,下面是我目前为止进行的一些测试

for (var i = 0; i < 20; i++)
{
    var sw = Stopwatch.StartNew();

    var departments = _sampleContext.Departments.AsNoTracking().ToList();

    sw.Stop();

    Console.WriteLine($"Run {i+1} took {sw.ElapsedMilliseconds} ms");
}

Run 1 took 2708 ms
Run 2 took 350 ms
Run 3 took 421 ms
Run 4 took 300 ms
Run 5 took 329 ms
Run 6 took 319 ms
Run 7 took 301 ms
Run 8 took 303 ms
Run 9 took 310 ms
Run 10 took 342 ms
Run 11 took 284 ms
Run 12 took 322 ms
Run 13 took 359 ms
Run 14 took 297 ms
Run 15 took 291 ms
Run 16 took 288 ms
Run 17 took 268 ms
Run 18 took 309 ms
Run 19 took 299 ms
Run 20 took 298 ms

在这里您可以看到第一次和第二次运行之间有很大的区别。

这背后的原因是什么?

除此之外,我还尝试复制相同的场景,而不使用下面这样的实体框架

var sqlConnection = new SqlConnection(_configuration.GetSection("ConnectionStrings:DefaultConnection").Value);

sqlConnection.Open();

for (var i = 0; i < 20; i++)
{
    var sw = Stopwatch.StartNew();

    var sqlCommand = new SqlCommand("select * from Departments", sqlConnection);

    using var sqlDataReader = sqlCommand.ExecuteReader();

    if (sqlDataReader.HasRows)
    {
        while (sqlDataReader.Read())
        {
            var id = sqlDataReader.GetInt32(0);
            var name = sqlDataReader.GetString(1);
        }
    }

    sw.Stop();

    Console.WriteLine($"Run {i + 1} took {sw.ElapsedMilliseconds} ms");
}

Run 1 took 499 ms
Run 2 took 300 ms
Run 3 took 276 ms
Run 4 took 275 ms
Run 5 took 273 ms
Run 6 took 256 ms
Run 7 took 288 ms
Run 8 took 309 ms
Run 9 took 285 ms
Run 10 took 280 ms
Run 11 took 292 ms
Run 12 took 308 ms
Run 13 took 283 ms
Run 14 took 267 ms
Run 15 took 290 ms
Run 16 took 276 ms
Run 17 took 277 ms
Run 18 took 286 ms
Run 19 took 283 ms
Run 20 took 273 ms

第一次运行和第二次运行之间也有区别,但并不像实体框架那样激烈。

我使用的是EF核心3.1

根据@Larnu的评论,这可能是因为sqlserver的查询计划缓存,所以我尝试在每个迭代中运行稍微不同的查询,如下所示

ADO.NET公司版本

var sqlCommand = new SqlCommand($"select * from Departments where ID > {i}", sqlConnection);

输出:

Run 1 took 494 ms
Run 2 took 274 ms
Run 3 took 304 ms
Run 4 took 276 ms
Run 5 took 731 ms
Run 6 took 475 ms
Run 7 took 576 ms
Run 8 took 276 ms
Run 9 took 275 ms
Run 10 took 291 ms
Run 11 took 271 ms
Run 12 took 253 ms
Run 13 took 269 ms
Run 14 took 262 ms
Run 15 took 270 ms
Run 16 took 303 ms
Run 17 took 261 ms
Run 18 took 296 ms
Run 19 took 275 ms
Run 20 took 661 ms

var departments = _sampleContext.Departments.Where(x=>x.ID > i).AsNoTracking().ToList();

输出:

Run 1 took 2377 ms
Run 2 took 274 ms
Run 3 took 272 ms
Run 4 took 260 ms
Run 5 took 276 ms
Run 6 took 281 ms
Run 7 took 319 ms
Run 8 took 506 ms
Run 9 took 318 ms
Run 10 took 265 ms
Run 11 took 269 ms
Run 12 took 276 ms
Run 13 took 283 ms
Run 14 took 256 ms
Run 15 took 253 ms
Run 16 took 258 ms
Run 17 took 277 ms
Run 18 took 298 ms

我想第一个查询和第二个查询之间的差别还是很大的,那么这仅仅是因为SQL Server的查询计划缓存还是其他什么原因?