背景

我正在尝试使用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？
糟糕的运气？

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