实体框架缓存的查询计划性能随不同参数而降低

Question

我有以下问题。

背景

我正在尝试使用 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(), 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) 
    { 
        // 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 或 9 个字符时，几乎需要 20 秒到 2 分钟）更多字符）。 令人惊讶的是，在“重新启动”SQL Server 服务后，如果初始字符串包含 10 个字符，它的性能也很好，但是当我们从“查询”字符串中删除字符时，性能会下降，完全相反。

为什么会发生这种情况？

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

通过执行清理执行计划缓存：

db.ExecuteStoreCommand("DBCC FREEPROCCACHE");

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

对 EF sql 语句进行一些分析后，我在 EF 生成 sql 之前在查询分析器中执行 DBCC FREEPROCCACHE，结果生成了不同的执行计划，所有执行计划都在 250 毫秒范围内执行，与参数长度

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？ 运气差？

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

Answer 1

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

另外，您可以创建一个存储过程，并将其映射到实体框架而不是使用 LINQ2Entities，并通过这种方式对 SQL 语法进行特定更改，并确保它始终相同。

Answer 2

正如您所指出的，SQL Server 编译计划以针对具有大型结果集的一个参数值进行优化。当结果集缩小时，查询性能不佳。

这种情况需要在查询中使用“选项（重新编译）”提示，因此将为收到的每个值重新编译查询。

使用实体框架来做到这一点并不那么容易。您将需要创建一个 DbCommandInterceptor 以在查询中包含选项（重新编译）。另一种选择是在 SQL Server 中创建计划指南以将“选项（重新编译）”添加到查询中。

您可以在此处找到有关 DbCommandInterceptor 的信息 - 添加查询提示调用表值函数

关于计划指南，您将需要与此类似的内容：

EXEC sp_create_plan_guide   
'planguidename',   
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',
'SQL',   
NULL,   
N'@p__linq__0 nvarchar(4000)',
N'OPTION (recompile)'