Как отфильтровать GET-запрос веб-приложения Blazor-Server для небольшого количества записей из таблицы в миллион строк
У меня есть веб-приложение сервера C# Blazor с Entity Framework Core + база данных SQL Server. Мне нужно несколько фильтров выборки/GET, и я хочу знать, как лучше всего их закодировать на C# и T-SQL. Таблица базы данных может содержать несколько миллионов строк. Когда мы запрашиваем отфильтрованные данные из этой таблицы, мы ожидаем, что будет возвращено не более 500 строк.
Я ищу лучший/самый быстрый способ вернуть данные на страницу браузера пользователя - либо с помощью запроса С#, подобного этому...
// Filter query for the customer.
IEnumerable<Table1> getQuery =
from recs in _dbContext.Table1()
where (recs.UID_CUSTOMER == pUID_CUSTOMER)
select recs;
// Fetch the data
return getQuery.ToList();
Или... от использования хранимых процедур в этом примере.
int customerUID = 7;
FormattableString uspCommand = $"EXEC {storedProcName} @CustomerUID = {customerUID}";
returnValue = _dbContext.Table1.FromSqlInterpolated(sqlCommand).ToList();
Какой способ лучше?
Второй метод, если я не ошибаюсь, открыт для инъекций. Вы не параметризуете параметры процедуры, вы их вводите.
Том... Спасибо за ответ. Я думал, что предложение «FromSqlInterpolated» решает проблему инъекции. Я видел код, в котором параметры определяются в объекте new(). Я могу найти пример и применить его к своему коду, который мы видим в этом вопросе.
Спасибо @marc_s за ваш ответ. Показывает ли какой-нибудь мой код в этом вопросе выполнение «параметризованного запроса T-SQL»? Я не уверен, является ли приведенный выше код запроса запросом C# или SQL-запросом. Если можете, предоставьте мне действительный запрос, о котором вы говорите. Если да, то на С#. Спасибо.
FromSqlInterpolated избегайте инъекций, только если вы используете FormattableString полностью. Не используйте string посередине. Еще один вариант, который вы не рассмотрели: использование хранимой процедуры с параметром @UID_CUSTOMER.
Спасибо @Charlieface... Я полностью принимаю ваш комментарий относительно FormattableString... Я исправлю свой код. Спасибо. Кроме того, я рассмотрел хранимую процедуру, передающую UID_CUSTOMER, поскольку она ограничивает количество строк специально для этого клиента. :-) Тогда я смогу "фильтровать" из этого огромного списка, меньшего, чем несколько миллионов строк. Спасибо.
Есть ли конкретная причина, по которой вы используете FromSqlInterpolated вместо создания запроса IQueryable к определенному DbSet в DbContext?
Ответы 1
Самый быстрый способ в EF: используйте проекцию, чтобы получить от сущностей именно то, что вам нужно.
Этот код ужасен:
IEnumerable<Table1> getQuery =
from recs in await this.GetAllCustomers()
where (recs.UID_CUSTOMER == pUID_CUSTOMER)
select recs;
... когда "GetAllCustomers()" делает что-то вроде:
await _dbContext.Table1!.ToListAsync();
Вышеупомянутое загружает ВСЕ строки в память.
Лучше было бы просто:
IEnumerable<Table1> getQuery = await _dbContext.Table1
.Where (recs.UID_CUSTOMER == pUID_CUSTOMER)
.ToListAsync();
Это указывает EF сгенерировать SQL-запрос, чтобы вернуть только X интересующих вас строк.
Еще лучше было бы посмотреть, какие поля из Таблицы 1 вам нужны, и составить ViewModel только из этих столбцов. Например, если таблица Table1 состоит из 50 столбцов, но вам нужно только 10, и/или есть значения, которые нужно извлечь из связанных таблиц, тогда использование Select позволит EF построить эффективный SQL для извлечения только необходимых столбцов, а не для извлечения всех 50. столбцы и/или создание декартова произведения (быстрая загрузка) или сценария SELECT N+1 (ленивая загрузка)
IEnumerable<Table1ViewModel> getQuery = await _dbContext.Table1
.Where (recs => recs.UID_CUSTOMER == pUID_CUSTOMER)
.Select(recs => new Table1ViewModel
{
Id = recs.Id,
Value1 = recs.Value1,
// ...
}).ToListAsync();
Отсюда вы можете проверить использование индекса и при необходимости оптимизировать его. Хорошей идеей является разработка сортировки и нумерации страниц при извлечении данных, чтобы гарантировать, что пользователи сначала получают наиболее релевантные данные и избежать мин, где «большая часть» данных должна представлять собой управляемый объем, но в некоторых крайних случаях наблюдаются большие и медленные наборы результатов. потянул.
Для операций чтения используйте проекцию. Для операций обновления извлекайте объекты, поскольку вы обычно будете читать отдельные строки по PK, что происходит очень быстро.
В общем, правильно параметризованный SQL-запрос (и EF Core превращает ваши запросы Linq в правильно параметризованные запросы T-SQL) и хранимая процедура при выполнении действительно одинаковы, после того как план выполнения был изначально разработан. Хранимая процедура здесь не имеет никакого «магического» преимущества в скорости, но ее обычно гораздо сложнее использовать из EF Core. Поэтому я рекомендую использовать правильные запросы Linq и убедиться, что ваши базовые таблицы базы данных правильно проиндексированы.