实体框架缓存的查询计划性能随着不同的参数而降级 [英] Entity Framework cached query plan performance degrades with different parameters

查看：130
本文介绍了实体框架缓存的查询计划性能随着不同的参数而降级的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下问题。



背景



我试图实现一个自动完成选择器MVC3，EF4和jquery表上有450万条记录。



这是表：

  CREATE TABLE [dbo]。[CONSTA]（
 [afpCUIT] nvarchar（11）COLLATE Modern_Spanish_CI_AS NOT NULL，
 [afpNombre] nvarchar（30）COLLATE Modern_Spanish_CI_AS NULL，
 [afpGanancias] varchar（2）COLLATE Modern_Spanish_CI_AS NULL，
 [afpIVA] varchar（2）COLLATE Modern_Spanish_CI_AS NULL，
 [afpMonot] varchar（2）COLLATE Modern_Spanish_CI_AS NULL，
 [afpIntSoc] varchar（1）COLLATE Modern_Spanish_CI_AS NULL，
 [afpEmpl] varchar（1）COLLATE Modern_Spanish_CI_AS NULL，
 [afpAct] varchar（2）COLLATE Modern_Spanish_CI_AS NULL，
 CONSTRAINT [CONSTA_pk] PRIMARY KEY CLUSTERED [afpCUIT]）
）
 ON [PRIMARY] 
 GO 
 
 CREATE NONCLUSTERED INDEX [CONSTA_Nombre_idx] ON [dbo]。[CONSTA] 
（[ afpNombr e]）
 WITH（
 PAD_INDEX = OFF，
 DROP_EXISTING = OFF，
 STATISTICS_NORECOMPUTE = OFF，
 SORT_IN_TEMPDB = OFF，
 ONLINE = OFF，
 ALLOW_ROW_LOCKS = OFF，
 ALLOW_PAGE_LOCKS = OFF）
 ON [PRIMARY] 
 GO

该表是非常静态的（它只需要每月批量更新）和只读。



如果有人关心下载记录（54MB）这是URL：



http://www.afip.gob.ar/genericos/cInscripcion/22102011.zip



这里是记录描述：



http：//www.afip。 gob.ar/genericos/cInscripcion/archivoCompleto.asp



这是应用程序的代码：



控制器：

  public class AltaMasivaController：Controller 
 {
 // 
 // GET：/ AltaMasiva / 
 
 public ActionResult Index（）
 {
 return View（）; 
} 
 
 public JsonResult GetUsers（string query）
 {
 CENT2Entities db = new CENT2Entities（）; 
 bool isCUIT = true; （int j = 0; j< query.Length; j ++）
 if（！Char.IsDigit（query，j））
 {
 isCUIT = false; 
 break; 
} 
 
 if（isCUIT）
 {
 // nvarchar search 
 var x = from db in db.CONSTA 
其中u。 afpCUIT.StartsWith（query）
 orderby u.afpNombre 
 select new {label = u.afpNombre.TrimEnd（），id = u.afpCUIT}; 
 
 return Json（x.Take（50），JsonRequestBehavior.AllowGet）; 
} 
 else 
 {
 // nvarchar搜索
 var x =从db中的u 
其中u.afpNombre.StartsWith（query）
 orderby u.afpNombre 
 select new {label = u.afpNombre.TrimEnd（），id = u.afpCUIT}; 
 
 return Json（x.Take（50），JsonRequestBehavior.AllowGet）; 
} 
} 
}

查看：

  @ {
 viewbag.title =index; 
} 
 
< h2> index< / h2> 
 @ html.textbox（user，，new {style =width：400px;}）
 
< script type =text / javascript> 
 
 $（input＃user）。autocomplete（
 {
 source：function（request，response）
 {
 //定义一个函数调用你的动作（假设usercontroller）
 $ .ajax（
 {
 url：'/ altamasiva / getusers'，键入：post，datatype：json，
 
 //查询将是您的操作方法使用的参数
 data：{query：request.term}，
 
 success：function（data）{
 response （$ .map（data，function（item）{return {label：item.label +（+ item.id +），value：item.label，id：item.id};}））; 
} 
}）
}，
 minlength：1，//从用户至少需要一个字符
}）; 
 
< / script>

现在：



问题



如您所见，代码遵循不同的路径，如果查询字符串只包含数字。



当所有控制器参数的字符是数字（其中u.afpCUIT.StartsWith（query）），查询优化器应该执行聚簇索引搜索（它所做的），并返回它找到的前50行。
当第一个自动完成字符串到达​​（通常最多一个或两个字符）时，查询执行速度非常快，但是当字符串的长度增加时，性能显着降低（几乎在20秒到2分钟与9个或更多的字符）。
令人惊讶的是，在重新启动SQL Server服务之后，如果初始字符串包含10个字符，那么它表现也很好，但是当我们从查询字符串中删除字符串时，性能就会降低，完全相反。 >

为什么会发生这种情况？



当SQL Server编译第一个执行计划时，它会优化其执行速度，大结果集（或反之亦然）。后续查询缩小（或扩展）结果集，需要不同的执行计划...但是...生成的SQL使用commad参数（精确地）避免语句重新编译...



执行执行计划缓存：执行：

  db.ExecuteStoreCommand（DBCC FREEPROCCACHE）;

将性能恢复到出色的响应时间...但它会杀死所有数据库中的计划，从而降低所有其他缓存计划的性能（通常可以执行）。



在对EF sql语句进行一些分析之后，我在查询分析器中执行了DBCC FREEPROCCACHE到sql EF生成，其原理是生成不同的执行计划，所有执行计划都在250ms范围内执行，与参数长度无关：

  DBCC FREEPROCCACHE 
 
 exec sp_executesql N'SELECT TOP（50）
 [Project1]。[C1] AS [C1]，
 [Project1]。[C2] AS [ C2]，
 [Project1]。[afpCUIT] AS [afpCUIT] 
 FROM（SELECT 
 [Extent1]。[afpCUIT] AS [afpCUIT]，
 [Extent1] afpNombre] AS [afpNombre]，
 1 AS [C1]，
 RTRIM（[Extent1]。[afpNombre]）AS [C2] 
 FROM [dbo]。[CONSTA] AS [Extent1 ] 
 WHERE [Extent1]。[afpCUIT] LIKE @ p__linq__0 ESCAPE N''〜''
 ）AS [Project1] 
 ORDER BY [Project1]。[afpNombre] ASC'，N'@ p__linq__0 nvarchar（4000）'，@ p__linq__0 = N'2023291％'



问题



有没有更可贵的替代方案

  db.ExecuteStoreCommand（DBCC FREEPROCCACHE）;

？



令人惊讶的是，的查询（其中u.afpNombre.StartsWith（query））不受相同问题的影响，并且表现很好。显然，当字符串的长度改变时，执行计划不会改变...



我在旧版本的EF中找到了一个ObjectContext参数：

  System.Data.EntityClient.EntityCommand.EnablePlanCaching

但我在EF4找不到，我不知道全局结果是否一样。



我真的很困惑有这个问题，我不知道真正的问题在哪里



差的指数设计？
缺少分区？
SQL SERVER 2008 Express版？
EF生成SQL？
运气好吗？



任何帮助都会很棒。
Thanx提前！

解决方案

有一种方法可以从SQL Server的缓存中删除单个计划。
这里详细解释：
http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx



此外，您可以创建一个存储过程，并将其与实体框架进行映射，而不是使用LINQ2Entities，从而对SQL语法进行更改，并确保它总是一样。


I have the following problem.

Background

I'm trying to implement an autocomplete selector with MVC3, EF4 and jquery over a table wit 4.5 million records.

This is the table:

CREATE TABLE [dbo].[CONSTA] (
  [afpCUIT] nvarchar(11) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [afpNombre] nvarchar(30) COLLATE Modern_Spanish_CI_AS NULL,
  [afpGanancias] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  [afpIVA] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  [afpMonot] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  [afpIntSoc] varchar(1) COLLATE Modern_Spanish_CI_AS NULL,
  [afpEmpl] varchar(1) COLLATE Modern_Spanish_CI_AS NULL,
  [afpAct] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  CONSTRAINT [CONSTA_pk] PRIMARY KEY CLUSTERED ([afpCUIT])
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CONSTA_Nombre_idx] ON [dbo].[CONSTA]
  ([afpNombre])
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = OFF,
  ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY]
GO

The table is pretty static (it only needs a monthly batch update) and read only.

if somebody cares to download the records (54MB) this is the URL:

http://www.afip.gob.ar/genericos/cInscripcion/22102011.zip

and here is the record description:

http://www.afip.gob.ar/genericos/cInscripcion/archivoCompleto.asp

Here is the code of the app:

CONTROLLER:

public class AltaMasivaController : Controller
{
    //
    // GET: /AltaMasiva/

    public ActionResult Index()
    {
        return View();
    }

    public JsonResult GetUsers(string query)
    {
        CENT2Entities db = new CENT2Entities();
        bool isCUIT = true;

        for(int j = 0; j < query.Length; j++)
            if (! Char.IsDigit(query, j))
            {
                isCUIT = false;
                break;
            }

        if (isCUIT)
        {
            // nvarchar search
            var x = from u in db.CONSTA
                    where u.afpCUIT.StartsWith(query)
                    orderby u.afpNombre
                    select new { label = u.afpNombre.TrimEnd(), id = u.afpCUIT };

            return Json(x.Take(50), JsonRequestBehavior.AllowGet);
        }
        else
        {
            // nvarchar search
            var x = from u in db.CONSTA
                    where u.afpNombre.StartsWith(query)
                    orderby u.afpNombre
                    select new { label = u.afpNombre.TrimEnd(), id = u.afpCUIT };

            return Json(x.Take(50), JsonRequestBehavior.AllowGet);
        }
    } 
}

VIEW:

@{
    viewbag.title = "index";
}

<h2>index</h2>
@html.textbox("user", "", new { style="width: 400px;" })

<script type="text/javascript">

$("input#user").autocomplete(
{ 
    source: function (request, response) 
    { 
        // define a function to call your action (assuming usercontroller) 
        $.ajax(
        { 
            url: '/altamasiva/getusers', type: "post", datatype: "json", 

            // query will be the param used by your action method 
            data: { query: request.term }, 

            success: function(data){ 
                response( $.map(data, function (item){ return { label: item.label + " (" + item.id + ")", value: item.label, id: item.id }; })); 
            } 
        }) 
    }, 
    minlength: 1, // require at least one character from the user
});

</script>

And now:

THE PROBLEM

As you can see, The code follows different paths if the query string contains only numbers.

When all the characters of the controller parameter are numbers (where u.afpCUIT.StartsWith(query) ), the query optimizer "is supposed to" perform a clustered index seek (which it does) and return the first 50 rows it finds. When the first "autocomplete" string arrives (usually one or two characters at most) the query performs extraordinarily fast, but, when the lenght of the string increases, the performance degrades notably (it takes almost between 20 seconds to 2 minutes with 9 or more chars). Surprisingly, after "restarting" the SQL Server Service, if the initial string contains 10 chars, it performs great too, but the performance degrades when we delete chars from the "query" string, the complete opposite.

Why is this happening?

When SQL server compiles the first execution plan, it optimizes it to perform really fast with a large result set (or viceversa). Subsequent queries, which narrows (or expands) the resultset, require a different execution plan ... BUT ... EF generated SQL uses commad parameters to (precisely) avoid statement recompiling ...

Cleaning the Execution Plan Cache by executing:

db.ExecuteStoreCommand("DBCC FREEPROCCACHE");

restores the performance to excellent response times ... BUT ... it kills al plans in all databases, thus degrading the performance of all other cached plans (which generally perform OK).

After doing some profiling on the EF sql statements, I executed DBCC FREEPROCCACHE in Query Analyzer prior to the sql EF generates, which turned out to generate different execution plans, all performing in the 250ms range, independently of the parameter length:

DBCC FREEPROCCACHE

exec sp_executesql N'SELECT TOP (50) 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[afpCUIT] AS [afpCUIT]
FROM ( SELECT 
    [Extent1].[afpCUIT] AS [afpCUIT], 
    [Extent1].[afpNombre] AS [afpNombre], 
    1 AS [C1], 
    RTRIM([Extent1].[afpNombre]) AS [C2]
    FROM [dbo].[CONSTA] AS [Extent1]
    WHERE [Extent1].[afpCUIT] LIKE @p__linq__0 ESCAPE N''~''
)  AS [Project1]
ORDER BY [Project1].[afpNombre] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'2023291%'

THE QUESTION

Is there a more ellegant alternative to 

db.ExecuteStoreCommand("DBCC FREEPROCCACHE");

?

Surprisingly, the second path of the query ( where u.afpNombre.StartsWith(query) ) are not affected by the same problem and performs great. Obviously, execution plans do not change when the lenght of the string changes...

I found an ObjectContext parameter in older versions of EF:

System.Data.EntityClient.EntityCommand.EnablePlanCaching

but I couldn't find it in EF4, and I'm not sure if the global results would be the same.

I'm really puzzled with this problem, and I don't know where the real problem lies

Poor index design? Lack of partitions? SQL SERVER 2008 Express edition? EF generated SQL? Lousy luck?

Any help would be great. Thanx in advance!

解决方案

There's a way to remove a single plan from SQL Server's cache. It's explained in detail here: http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx

Also, you can create a Stored Procedure, and map it with Entity Framework instead of using LINQ2Entities, and in this way make spcific changes to the SQL syntax, and make sure it's always the same.

这篇关于实体框架缓存的查询计划性能随着不同的参数而降级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆