如何异步查询数据库需要0个tick？ [英] How can it take 0 ticks to query the database asynchronously?
问题描述
我试图使用 IDbInterceptor 将Entity Framework的查询执行时间尽可能准确地实现乔纳森·艾伦的回答类似的问题 ：
public class PerformanceLogDbCommendInterceptor：IDbCommandInterceptor
{
static readonly ConcurrentDictionary< DbCommand，DateTime> _startTimes =
new ConcurrentDictionary< DbCommand，DateTime>（）;
public void ReaderExecuted（DbCommand命令，
DbCommandInterceptionContext< DbDataReader> interceptionContext）
{
Log（command，interceptionContext）;
}
public void NonQueryExecuted（DbCommand命令，
DbCommandInterceptionContext< int> interceptionContext）
{
Log（command，interceptionContext）;
}
public void ScalarExecuted（DbCommand命令，
DbCommandInterceptionContext< object> interceptionContext）
{
Log（command，interceptionContext）;
}
private static void Log< T>（DbCommand命令，
DbCommandInterceptionContext< T> interceptionContext）
{
DateTime startTime;
TimeSpan持续时间;
如果（！_ startTimes.TryRemove（command，out startTime））
{
//日志异常
return;
}
DateTime now = DateTime.UtcNow;
duration = now - startTime;
字符串requestGUID = Guid.Empty.ToString（）;
var context = interceptionContext.DbContexts.SingleOrDefault（）;
if（context == null）
{
//日志异常
}
else
{
var businessContext = context as MyDb;
if（businessContext == null）
{
//日志异常
}
else
{
requestGUID = businessContext.RequestGUID.ToString （）;
}
}
string message;
var parameters = new StringBuilder（）;
foreach（DbParameter param in command.Parameters）
{
parameters.AppendLine（param.ParameterName ++ param.DbType
+=+ param.Value）;
}
if（interceptionContext.Exception == null）
{
message = string.Format（$数据库调用花费
+ $ {duration.TotalMilliseconds.ToString（N3）} ms。
+ $RequestGUID {requestGUID}
// + $\r\\\
Command：\r\\\
{ parameters.ToString（）+ command.CommandText}）;
}
else
{
message = string.Format（$EF数据库调用失败后，
+ ${duration.TotalMilliseconds.ToString（N3 ）} ms。
+ $RequestGUID {requestGUID}
+ $\r\\\
Command：\r\\\
{（parameters.ToString（）+ command.CommandText）}
+ $\r\\\
Error：{interceptionContext.Exception}）;
}
if（duration == TimeSpan.Zero）
{
message + = $\r\\\
Time：start：{startTime.ToString（hh：mm ：ss fffffff）}
+ $| now：{now.ToString（hh：mm：ss fffffff）}
+ $\r\\\
\r\\ \\ nCommand：\r\\\
+ ${parameters.ToString（）+ command.CommandText};
}
System.Diagnostics.Debug.WriteLine（message）;
}
public void NonQueryExecuting（DbCommand命令，
DbCommandInterceptionContext< int> interceptionContext）
{
OnStart（command）;
}
public void ReaderExecuting（DbCommand命令，
DbCommandInterceptionContext< DbDataReader> interceptionContext）
{
OnStart（command）;
}
public void ScalarExecuting（DbCommand命令，
DbCommandInterceptionContext< object> interceptionContext）
{
OnStart（command）;
}
private static void OnStart（DbCommand命令）
{
_startTimes.TryAdd（command，DateTime.UtcNow）;
}
}
而且奇怪的是，每10次查询或所以需要0个tick来执行。它只是在异步运行的同时，几次查询似乎就会发生。另外要注意的是，当我再次查询相同数量的查询时，并不总是相同的查询需要0个tick。
此外，我的数据库目前正在测试，位于本地网络，而不是本地机器 - ping时间为0-1ms - 即使数据被缓存，我看不到可以采取0滴滴。
在次要注意事项上，大多数查询可疑地接近1,2和3 ms（例如0.997ms至1.003ms）。对我来说听起来像OS旋转线程cpu-time和/或1ms睡眠。我不介意这是发生的，但我只想知道为什么，所以我可以解释结果的不准确。
可能有一些事情要做与 ConcurrentDictionary 。但是当我现在正在测试时，我目前只调用一个异步（WCF）方法，等待每个异步数据库调用，所以它不应该一次启动更多的调用，以达到我的理解。这是一个所谓的例子：
public async任务< IEnumerable< DTJobAPPOverview>> GetJobOverviewAsync（）
...
var efResponsibleUserFullName = await dbContext.tblUsers.Where（u =>
u.UserID == efJob.ResponsibleUserID
）.Select（u = > u.FullName）
.FirstOrDefaultAsync（）;
dtJob.ResponsibleUserName = efResponsibleUserName;
var efCase = await dbContext.tblCases.FirstOrDefaultAsync（c =>
c.ID == efJob.FK_CaseID）;
dtJob.Case = Mapper.Map< DTCase>（efCase）; // Automapper
...
}
顺便说一下，我知道我应该将整个应用程序转换为使用导航属性，但这是我们目前所用的，所以请与我一起。
p> Kudos到你的网络管理员 - 很少看到一个这样低的延迟的网络。
DateTime.UtcNow 具有与系统定时器相同的分辨率（很奇怪，因为系统定时器更新当前时间:)）。默认情况下，在Windows NT上，这是10ms - 所以在一个干净的系统上，你只能获得10ms的精度。 10ms的值可能意味着操作根本就没有时间，或者花了9.9ms，或者取决于你的运气需要19。
在你的系统中，某些应用程序更改了定时器频率（Chrome和其他动画大型应用程序是频繁滥用），或者您正在运行Windows 8+，它们转移到无定时计时器系统。无论如何，您的定时精度为1ms - 这正是您在日志中看到的。
如果您想要更高的精度/准确度，您需要使用秒表。 DateTime 不是为了您正在使用它而设计，尽管它经常工作得很好，只要你不太依赖它（DST /飞跃）秒是非常有趣:)）。 秒表是。
最后，确保你的字典的键以你所假设的方式工作。你确定那些 DbCommand 有你需要的那种身份吗？不符合 DbCommand 有参考标识的合同要求，或者EntityFramework不要重复使用 DbCommand 实例。 / p>
I'm trying to use IDbInterceptor to time Entity Framework's query executions, as accurately as possible, implementing a variant of Jonathan Allen's answer to a similar question:
public class PerformanceLogDbCommendInterceptor : IDbCommandInterceptor
{
static readonly ConcurrentDictionary<DbCommand, DateTime> _startTimes =
new ConcurrentDictionary<DbCommand, DateTime>();
public void ReaderExecuted(DbCommand command,
DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Log(command, interceptionContext);
}
public void NonQueryExecuted(DbCommand command,
DbCommandInterceptionContext<int> interceptionContext)
{
Log(command, interceptionContext);
}
public void ScalarExecuted(DbCommand command,
DbCommandInterceptionContext<object> interceptionContext)
{
Log(command, interceptionContext);
}
private static void Log<T>(DbCommand command,
DbCommandInterceptionContext<T> interceptionContext)
{
DateTime startTime;
TimeSpan duration;
if(!_startTimes.TryRemove(command, out startTime))
{
//Log exception
return;
}
DateTime now = DateTime.UtcNow;
duration = now - startTime;
string requestGUID = Guid.Empty.ToString();
var context = interceptionContext.DbContexts.SingleOrDefault();
if (context == null)
{
//Log Exception
}
else
{
var businessContext = context as MyDb;
if (businessContext == null)
{
//Log Exception
}
else
{
requestGUID = businessContext.RequestGUID.ToString();
}
}
string message;
var parameters = new StringBuilder();
foreach (DbParameter param in command.Parameters)
{
parameters.AppendLine(param.ParameterName + " " + param.DbType
+ " = " + param.Value);
}
if (interceptionContext.Exception == null)
{
message = string.Format($"Database call took"
+ $" {duration.TotalMilliseconds.ToString("N3")} ms."
+ $" RequestGUID {requestGUID}"
//+ $" \r\nCommand:\r\n{parameters.ToString() + command.CommandText}");
}
else
{
message = string.Format($"EF Database call failed after"
+ $" {duration.TotalMilliseconds.ToString("N3")} ms."
+ $" RequestGUID {requestGUID}"
+ $" \r\nCommand:\r\n{(parameters.ToString() + command.CommandText)}"
+ $"\r\nError:{interceptionContext.Exception} ");
}
if (duration == TimeSpan.Zero)
{
message += $" \r\nTime: start: {startTime.ToString("hh:mm:ss fffffff")}"
+ $" | now: {now.ToString("hh:mm:ss fffffff")}"
+ $" \r\n \r\nCommand:\r\n"
+ $"{parameters.ToString() + command.CommandText}";
}
System.Diagnostics.Debug.WriteLine(message);
}
public void NonQueryExecuting(DbCommand command,
DbCommandInterceptionContext<int> interceptionContext)
{
OnStart(command);
}
public void ReaderExecuting(DbCommand command,
DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
OnStart(command);
}
public void ScalarExecuting(DbCommand command,
DbCommandInterceptionContext<object> interceptionContext)
{
OnStart(command);
}
private static void OnStart(DbCommand command)
{
_startTimes.TryAdd(command, DateTime.UtcNow);
}
}
And the weird thing is, that every 10th query or so, it takes 0 ticks to execute. It only seems to happen when I run it asynchronously, with a handful of queries at the same time. Another thing to note, is that when I query the same handful of queries again, it's not always the same queries that take 0 ticks.
Moreover, the database I'm currently testing on, is located on the local network, not the local machine - and the ping time is 0-1ms to it - so even if the data was cached, I can't see how it could take 0 ticks.
On a minor note, most of the queries take suspiciously close to 1, 2 and 3 ms (e.g. 0.997ms to 1.003ms). To me that sounds like the OS rotating thread cpu-time and/or 1ms sleeping. I don't mind that it happens, but I just want to know why, so I can account for the inaccuracies in the results.
It could probably have something to do with the ConcurrentDictionary. But when I'm testing right now, I'm currently only calling an asynchronous (WCF) method once, awaiting every asynchronous DB call, so it shouldn't even fire up more calls at once, to my understanding. This is an example of what is called:
public async Task<IEnumerable<DTJobAPPOverview>> GetJobOverviewAsync()
...
var efResponsibleUserFullName = await dbContext.tblUsers.Where(u =>
u.UserID == efJob.ResponsibleUserID
).Select(u => u.FullName)
.FirstOrDefaultAsync();
dtJob.ResponsibleUserName = efResponsibleUserName;
var efCase = await dbContext.tblCases.FirstOrDefaultAsync(c =>
c.ID == efJob.FK_CaseID);
dtJob.Case = Mapper.Map<DTCase>(efCase); //Automapper
...
}
By the way, I know that I should probably convert the entire application to use navigation properties, but this is what we have currently, so please bear with me.
Kudos to your network admin - it's quite rare to see a network with such low latencies.
DateTime.UtcNow has a resolution that's the same as the system timer (hardly surprising, since the system timer updates the current time :)). By default, on Windows NT, this is 10ms - so on a clean system, you only get an accuracy of 10ms. A value of 10ms may mean the operation took no time at all, or that it took 9.9ms, or that it took 19.9ms, depending on your luck.
On your system, either some application changed the timer frequency (Chrome and other animation-heavy applications are frequent abusers), or you're running onw Windows 8+, which moved to a tickless timer system. In any case, your timer accuracy is 1ms - and that's what you're seeing in your logs.
If you want a higher precision/accuracy, you'll need to use Stopwatch. DateTime isn't designed for what you're using it for anyway, though it often works well enough as long as you don't rely on it too much (DST/leap seconds are so much fun :)). Stopwatch is.
Finally, make sure that your dictionary's keys work the way you assume they do. Are you sure those DbCommands have the kind of identity you require? It's not like there's a contractual requirement for DbCommand to have reference identity, or for EntityFramework not to reuse DbCommand instances.
这篇关于如何异步查询数据库需要0个tick？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
