У меня следующая проблема.
Задний план
Я пытаюсь реализовать селектор автозаполнения с 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-сервер компилирует первый план выполнения, он оптимизирует его для очень быстрой работы с большим набором результатов (или наоборот). Последующие запросы, которые сужают (или расширяют) набор результатов, требуют другого плана выполнения... НО... Сгенерированный EF SQL использует параметры-запятые, чтобы (точно) избежать перекомпиляции оператора...
Очистка кэша плана выполнения путем выполнения:
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? EF сгенерировал SQL? Паршивая удача?
Любая помощь будет здорово. Спасибо заранее!
