Кеширование дорогостоящего SQL-запроса в памяти или в базе данных?

Question

Позвольте мне начать с описания сценария. У меня есть приложение MVC 3 с SQL Server 2008. На одной из страниц мы отображаем список продуктов, который возвращается из базы данных и является уникальным для каждого вошедшего в систему пользователя. SQL-запрос (фактически VIEW), используемый для возврата списка продуктов, ОЧЕНЬ дорог.

• Он основан на очень сложных бизнес-требованиях, которые нельзя изменить на данном этапе.
• Схема базы данных не может быть изменена или изменена, так как она используется другими приложениями.
• Существует 50 000 товаров и 5 000 пользователей (каждый пользователь может иметь доступ от 1 до 50 000 товаров).

Для отображения страницы "Продукты" для вошедшего в систему пользователя мы используем:

SELECT TOP X * FROM [VIEW] WHERE UserID = @UserId -- where 'X' is the size of the page

Приведенный выше запрос возвращает максимум 50 строк (максимальный размер страницы). Предложение WHERE ограничивает количество строк максимум 50k (продукты, к которым пользователь имеет доступ). Загрузка страницы занимает от 5 до 7 секунд, и это именно то время, которое требуется SQL-запросу для выполнения в SQL.Проблема:

Пользователь переходит на страницу "Продукты" и, скорее всего, использует подкачку страниц, пересортирует результаты, переходит на страницу сведений и т. Д., А затем возвращается к списку. И каждый раз для отображения результатов требуется 5-7 секунд.

Это недопустимо, но в то же время бизнес-группа признала, что при первой загрузке страницы "Продукты" это может занять 5–7 с. Поэтому мы подумали о кешировании.

Теперь у нас есть два варианта на выбор, самый "очевидный", по крайней мере для меня, - это использование.Net Caching (в памяти / в proc). (Обратите внимание, что в настоящее время распределенный кэш не разрешен из-за технических ограничений с нашим провайдером / хостинг-партнером).

Но мне это не очень удобно. В результате может появиться много продуктов в памяти (когда одновременно зарегистрировано 50 или 100 пользователей), что может вызвать другие проблемы на сервере, например,.Net постоянно удаляет элементы кэша, чтобы освободить место, в то время как наш код вставляет новые элементы.

ВТОРОЙ вариант:

Основная проблема здесь в том, что очень дорого генерировать представление "Пользователь x продукт x Доступ", поэтому мы подумали, что можем создать плоскую таблицу (или, другими словами, CACHE всех продуктов x пользователей в базе данных). Эта таблица будет в точности результатом просмотра. Однако результаты могут измениться в любое время, если будут добавлены новые продукты, изменены пользовательские разрешения и т. Д. Поэтому нам необходимо постоянно обновлять таблицу (что может занять несколько секунд), и это начало становиться немного сложнее.

Точно так же, хотя мы могли бы реализовать своего рода Cache Provider и, по запросу пользователя, мы выполняли исходный SQL-запрос и выбирали продукты из представления (5-7 с, приемлемо только один раз) и сохраняли этот результат в плоском виде. Таблица под названием ProductUserAccessCache в SQL. В следующем запросе мы получили бы значения из этой кэшированной таблицы (поскольку мы могли легко идентифицировать результаты, которые были кэшированы для этого конкретного пользователя) с быстрым запросом без вычислений в SQL. Каждый раз, когда продукт добавлялся или менялось разрешение, мы усекали кешированную таблицу, и по новому запросу таблица заполнялась для запрошенного пользователя. Это не кажется мне слишком сложным, но то, что мы делаем здесь, в основном, это создание НОВОГО "провайдера" кеша.

• Есть ли у кого-нибудь опыт работы с такого рода проблемами?
• Было бы лучше использовать.Net Caching (в proc)?
• Какие-либо предложения?

Answer 1

Мы столкнулись с подобной проблемой некоторое время назад, и мы думали об использовании EF-кэширования, чтобы избежать задержки при получении информации. Наша проблема была 1 - 2 секунды. задержка. Вот некоторая информация, которая может помочь в том, как кэшировать таблицу, расширяющую EF. Одним из недостатков кэширования является то, насколько свежая информация вам нужна, поэтому вы соответственно устанавливаете срок действия кэша. В зависимости от этого срока, пользователям может потребоваться подождать, чтобы получить свежую информацию больше, чем они хотели бы, но если ваши пользователи могут согласиться, что они просматривают устаревшую информацию, чтобы избежать задержки, то компромисс стоил бы того.

В нашем сценарии мы решили лучше иметь свежую информацию, чем быстро, но, как я уже говорил, наш период ожидания был не таким длинным.

Надеюсь, поможет