我如何初始化我的实体框架的查询，以加快它们吗？ [英] How do I initialize my Entity Framework queries to speed them up?

问题描述

我有执行一个非常简单的EF查询，以确定用户是否有效的登录页面。在第一次运行该查询需要6秒左右运行。在随后的运行需要不到一秒多。

I have a login page that executes a very simple EF query to determine if a user is valid. On the first run this query takes about 6 seconds to run. On subsequent runs it takes much less than a second.

我看着那个谈到使用的文章<一个href=\"http://weblogs.asp.net/scottgu/archive/2009/09/15/auto-start-asp-net-applications-vs-2010-and-net-4-0-series.aspx\">Application自动启动以及我的问题是：有没有来触发这个查询造成任何缓存需要，而不实际调用查询发生的一种方式，或者是否有必要对我来说，只是调用查询与虚拟组参数呢？

I've looked at an article that talked about using Application Auto-Start and my question is this: Is there a way to trigger this query to cause whatever caching needs to happen without actually calling the query, or is it necessary for me to just call the query with a dummy set of arguments?

编辑：：当我说6秒我指需要得到查询的时间。 code看起来像这样（注意在这种情况下的ContactID是一个可空int和设置为null）：​​

When I say six seconds I'm referring the time it takes to get the query. Code looks something like this (note in this case contactID is a nullable int and set to null):

return from contact in _context.Contacts 
             where contact.District == Environment.District &&
             contact.ContactId == (contactID ?? contact.ContactId)
             select contact;

这是一个SQLServer 2008年，我已经运行一个分析器来检查SQL和它返回的时间是41ms对于最终得到执行的查询。 6或7秒的延迟发生在查询甚至达到了SQL虽然之前。我想现在设置一瞥，看它是否能给我可能在同一时间去处理其他事情的更多细节。

This is a SqlServer 2008 and I've run a profiler to check the SQL and the duration it returns is 41ms for the query that ultimately gets executed. The 6 or 7 second delay happens before the query even reaches SQL though. I'm trying to setup glimpse now to see if it can give me more details on other things that may be going on at the same time.

推荐答案

这真的听起来像什么是所谓的冷查询。冷查询其主要表现是botteneck生成视图，这是每个应用程序的AppDomain中执行一次。通常情况下，效果是第一个查询 - 它并不重要哪一个 - 是缓慢的，后续的查询速度快

This really sounds like what is called a "cold query". The main performance botteneck for cold queries is "View Generation" which is performed once per AppDomain of your application. Typically the effect is that your first query - and it doesn't matter which one - is slow and subsequent queries are fast.

它必须不一定是这可能是缓慢的查询。如果您在您的应用程序EF所做的第一个操作是插入，这将是缓慢的。甚至是连接不触及数据库都将是缓慢为好。 （这是一个好简单的测试用例的方式：添加 context.Users.Attach（新用户（））进入应用程序启动并在调试看它需要多长时间通过这条线。）

It must not necessarily be a query that could be slow. If the first operation you are doing with EF in your application is an Insert that would be slow. Or even an Attach that doesn't touch the database at all would be slow as well. (That's a good simple test case by the way: Add a context.Users.Attach(new User()) into application start and watch in the debugger how long it takes to pass that line.)

在时间通过在内存中建立一个内部数据结构消耗的所有案件 - 当地查询意见（他们有没有关系数据库表视图） - 即每个AppDomain发生一次

In all cases the time is consumed by building an internal data structure in memory - the local query "views" (they have nothing to do with database table views) - that takes place once per AppDomain.

查看生成描述这里更详细和的这里在这里你还可以找到资源，如何pre-产生那些意见作为构建过程的一部分，在部署之前。 （注：你必须更新这些pre-生成您的每一次变化模型，并重新部署应用程序）

View Generation is described here in more detail and here where you also can find resources how to "pre-generate" those views as part of your build process and before deployment. (Note: You have to update these pre-generated every time your change your model and redeploy your application.)

另一种方法是触发加载Web应用程序定期启动（例如通过一些过程，撞击点）。在应用程序启动，你会运行之上的任何虚拟查询或连接事物或手动调用初始化EF：

The alternative way is to trigger loading your web application start periodically (by some process for example that hits the site). In application start you would run any dummy query or the Attach thing above or call the EF initialisation manually:

using (var context = new MyContext())
{
    context.Database.Initialize(false);
}

修改

我忘了最后的解决方案。只是忽略6或7秒。如果您的网站获得著名的，具有合理的交通这么寒冷的查询将变得不太可能发生，因为IIS工作进程很少会关闭AppDomain中。谁打的网站，当这样一个关机了右以前发生过夜间偶尔用户可能是太累了，甚至没有注意到延迟。

I forgot the last solution. Just ignore the 6 or 7 seconds. If your site gets famous and has reasonable traffic such a cold query will become unlikely to occur because the IIS worker process will rarely shut down the AppDomain. The occasional user who hits the site in the night when such a shut down has happened right before is probably too tired to even notice the delay.

这篇关于我如何初始化我的实体框架的查询，以加快它们吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！