Производительность кэшированного плана запросов Entity Framework снижается с различными параметрами
У меня следующая проблема.
Фон
Я пытаюсь реализовать селектор автозаполнения с MVC3, EF4 и jquery над таблицей с 4,5 миллионами записей.
Это таблица:
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
Таблица довольно статична (требуется только ежемесячное пакетное обновление) и доступна только для чтения.
если кто-то захочет загрузить записи (54 МБ), это 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 строк, которые он найдет. Когда поступает первая строка "автозаполнение" (обычно не более одного или двух символов), запрос выполняется чрезвычайно быстро, но, когда длина строки увеличивается, производительность заметно снижается (это занимает от 20 секунд до 2 минут с 9 или больше символов). Удивительно, но после "перезапуска" службы SQL Server, если начальная строка содержит 10 символов, она тоже работает отлично, но производительность снижается, когда мы удаляем символы из строки "запроса", что полностью противоположно.
Почему это происходит?
Когда SQL-сервер компилирует первый план выполнения, он оптимизирует его для действительно быстрого выполнения с большим набором результатов (или наоборот). Последующие запросы, которые сужают (или расширяют) набор результатов, требуют другого плана выполнения... НО... Сгенерированный SQL EF использует запятые параметры, чтобы (точно) избежать перекомпиляции операторов...
Очистка кэша плана выполнения путем выполнения:
db.ExecuteStoreCommand("DBCC FREEPROCCACHE");
восстанавливает производительность до превосходного времени отклика... НО... убивает все планы во всех базах данных, тем самым снижая производительность всех других кэшированных планов (которые обычно работают нормально).
После некоторого профилирования операторов EF sql я выполнил DBCC FREEPROCCACHE в Query Analyzer до генерации sql EF, которая, как оказалось, генерировала разные планы выполнения, все выполняющиеся в диапазоне 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)) не подвержен той же проблеме и работает великолепно. Очевидно, что планы выполнения не меняются при изменении длины строки...
Я нашел параметр ObjectContext в более старых версиях EF:
System.Data.EntityClient.EntityCommand.EnablePlanCaching
но я не смог найти его в EF4, и я не уверен, что глобальные результаты будут такими же.
Я действительно озадачен этой проблемой, и я не знаю, где настоящая проблема
Плохой дизайн индекса? Отсутствие перегородок? SQL SERVER 2008 Express Edition? EF генерируется SQL? Паршивая удача?
Любая помощь будет отличной. Спасибо заранее!
2 ответа
Есть способ удалить один план из кеша SQL Server. Это подробно объясняется здесь: http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx
Кроме того, вы можете создать хранимую процедуру и сопоставить ее с Entity Framework вместо использования LINQ2Entities, и таким образом внести особые изменения в синтаксис SQL и убедиться, что он всегда одинаков.
Как вы определили, SQL Server компилирует план для оптимизации для одного значения параметра с большим набором результатов. Когда набор результатов сужается, запрос не работает хорошо.
Этот сценарий требует использования подсказки "option (Recompile)" в запросе, поэтому запрос будет перекомпилирован для каждого полученного значения.
Это не так просто сделать с помощью структуры лица. Вам нужно будет создать 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)'