sql-server – 实体框架缓存查询打算机能跟着差异参数而低落

我有以下题目.

配景

我正在实行行使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]
  ([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

该表很是静态(它只必要每月批量更新)而且只读.

假若有人体谅下载记录(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;

        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(),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) 
    { 
        // 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>

此刻：

题目

如您所见,假如查询字符串仅包括数字,则代码遵循差异的路径.

当节制器参数的全部字符都是数字(个中u.afpCUIT.StartsWith(query))时,查询优化器“应该”执行聚簇索引搜刮(它执行)并返回它找到的前50行.
当第一个“自动完成”字符串达到??时(凡是最多只有一个或两个字符),查询执行速率很是快,可是,当字符串的长度增进时,机能会显著降落(在9可能约莫必要20秒到2分钟之间)更多的人物).
令人惊奇的是,在“从头启动”SQL Server处事之后,假如初始字符串包括10个字符,它也示意得很好,可是当我们从“查询”字符串中删除字符机遇能会低落,完全相反.

为什么会这样？

当SQL Server编译第一个执行打算时,它会优化它以行使大的功效集(或反之亦然)执行得很是快.后续查询(缩小(或扩展)功效集)必要差异的执行打算……可是…… EF天生的SQL行使commad参数来(准确地)停止语句从头编译…

通过执行以下操纵整理执行打算缓存：

db.ExecuteStoreCommand("DBCC FREEPROCCACHE");

将机能规复到精彩的相应时刻……可是……它会杀死全部数据库中的打算,从而低落全部其他缓存打算(凡是执行正常)的机能.

在对EF sql语句举办一些说明之后,我在sql EF天生之前在查询说明器中执行了DBCC FREEPROCCACHE,功效天生了差异的执行打算,全部执行打算都在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(查询))不受统一题目的影响而且示意很好.显然,当字符串的长度产生变革时,执行打算不会改变……

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

System.Data.EntityClient.EntityCommand.EnablePlanCaching

但我在EF4中找不到它,我不确定全局功效是否沟通.

我真的很狐疑这个题目,我不知道真正的题目在那边

指数计划不佳？
穷乏分区？
SQL SERVER 2008 Express版？
EF天生SQL？
糟糕的命运？

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!