, если вы используете runnable, вы можете сохранить пространство, чтобы перейти к любому другому классу.
Динамически изменяющиеся поисковые запросы, основанные на данных параметрах, являются сложным предметом и делают это один за другим, даже с очень незначительной разницей, могут иметь серьезные последствия для производительности. Ключ должен использовать индекс, игнорировать компактный код, игнорировать беспокойство о повторении кода, вы должны составить хороший план выполнения запроса (использовать индекс).
Прочитайте это и рассмотрите все методы. Ваш лучший метод будет зависеть от ваших параметров, ваших данных, вашей схемы и фактического использования:
Условия динамического поиска в T-SQL by Erland Sommarskog
Проклятие и благословения динамического SQL от Erland Sommarskog
Если у вас есть соответствующая версия SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) и более поздняя версия), вы может использовать этот небольшой трюк для фактического использования индекса:
Добавить OPTION (RECOMPILE)
в ваш запрос, см. статью Erland , а SQL Server разрешит OR
из (@LastName IS NULL OR LastName= @LastName)
до того, как план запроса будет создан на основе значений времени выполнения локальных переменных и будет использоваться индекс.
Это будет работать для любой версии SQL Server (вернет правильные результаты), но включит только OPTION (RECOMPILE), если вы используете SQL Server 2008 SP1 CU5 (10.0.2746) и позже. OPTION (RECOMPILE) перекомпилирует ваш запрос, только перечисленная версия перекомпилирует его на основе текущих значений времени выполнения локальных переменных, что даст вам лучшую производительность. Если не в этой версии SQL Server 2008, просто оставьте эту строку выключенной.
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR (FirstName = @FirstName))
AND (@LastName IS NULL OR (LastName = @LastName ))
AND (@Title IS NULL OR (Title = @Title ))
OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
END
Пять лет опоздали на вечеринку.
В приведенных ссылках принятого ответа упоминается, но я думаю, что он заслуживает явного ответа на SO - динамическое построение запроса на основе предоставленных параметров. Например:
Настройка
-- drop table Person
create table Person
(
PersonId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Person PRIMARY KEY,
FirstName NVARCHAR(64) NOT NULL,
LastName NVARCHAR(64) NOT NULL,
Title NVARCHAR(64) NULL
)
GO
INSERT INTO Person (FirstName, LastName, Title)
VALUES ('Dick', 'Ormsby', 'Mr'), ('Serena', 'Kroeger', 'Ms'),
('Marina', 'Losoya', 'Mrs'), ('Shakita', 'Grate', 'Ms'),
('Bethann', 'Zellner', 'Ms'), ('Dexter', 'Shaw', 'Mr'),
('Zona', 'Halligan', 'Ms'), ('Fiona', 'Cassity', 'Ms'),
('Sherron', 'Janowski', 'Ms'), ('Melinda', 'Cormier', 'Ms')
GO
Процедура
ALTER PROCEDURE spDoSearch
@FirstName varchar(64) = null,
@LastName varchar(64) = null,
@Title varchar(64) = null,
@TopCount INT = 100
AS
BEGIN
DECLARE @SQL NVARCHAR(4000) = '
SELECT TOP ' + CAST(@TopCount AS VARCHAR) + ' *
FROM Person
WHERE 1 = 1'
PRINT @SQL
IF (@FirstName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @FirstName'
IF (@LastName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @LastName'
IF (@Title IS NOT NULL) SET @SQL = @SQL + ' AND Title = @Title'
EXEC sp_executesql @SQL, N'@TopCount INT, @FirstName varchar(25), @LastName varchar(25), @Title varchar(64)',
@TopCount, @FirstName, @LastName, @Title
END
GO
Использование
exec spDoSearch @TopCount = 3
exec spDoSearch @FirstName = 'Dick'
Плюсы:
Минусы:
Не прямой ответ, но связанный с проблемой aka большой картиной
Обычно эти фильтры хранимые процедуры не плавают вокруг, а вызываются из некоторого уровня обслуживания. Это оставляет возможность отменить бизнес-логику (фильтрацию) с SQL на уровень обслуживания.
В одном примере используется LINQ2SQL для генерации запроса на основе предоставленных фильтров:
public IList<SomeServiceModel> GetServiceModels(CustomFilter filters)
{
var query = DataAccess.SomeRepository.AllNoTracking;
// partial and insensitive search
if (!string.IsNullOrWhiteSpace(filters.SomeName))
query = query.Where(item => item.SomeName.IndexOf(filters.SomeName, StringComparison.OrdinalIgnoreCase) != -1);
// filter by multiple selection
if ((filters.CreatedByList?.Count ?? 0) > 0)
query = query.Where(item => filters.CreatedByList.Contains(item.CreatedById));
if (filters.EnabledOnly)
query = query.Where(item => item.IsEnabled);
var modelList = query.ToList();
var serviceModelList = MappingService.MapEx<SomeDataModel, SomeServiceModel>(modelList);
return serviceModelList;
}
Плюсы :
Минусы:
Вы можете сделать в следующем случае:
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR FirstName = @FirstName) AND
(@LastNameName IS NULL OR LastName = @LastName) AND
(@Title IS NULL OR Title = @Title)
END
однако зависят от данных, иногда лучше создавать динамический запрос и выполнять их.
Расширьте условие WHERE
:
WHERE
(FirstName = ISNULL(@FirstName, FirstName)
OR COALESCE(@FirstName, FirstName, '') = '')
AND (LastName = ISNULL(@LastName, LastName)
OR COALESCE(@LastName, LastName, '') = '')
AND (Title = ISNULL(@Title, Title)
OR COALESCE(@Title, Title, '') = '')
i. е. объединить различные случаи с булевыми условиями.
Ответ от @KM хорош, насколько это возможно, но не может полностью следить за одним из его ранних советов,
..., игнорировать компактный код, игнорировать беспокойство о повторении кода, ...
blockquote>Если вы хотите достичь максимальной производительности, вы должны написать индивидуальный запрос для каждой возможной комбинации дополнительных критериев. Это может показаться экстремальным, и если у вас есть много дополнительных критериев, то это может быть, но производительность часто является компромиссом между усилиями и результатами. На практике может существовать общий набор комбинаций параметров, который может быть нацелен на заказные запросы, а затем общий запрос (согласно другим ответам) для всех других комбинаций.
CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = null AS BEGIN IF (@FirstName IS NOT NULL AND @LastName IS NULL AND @Title IS NULL) -- Search by first name only SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE FirstName = @FirstName ELSE IF (@FirstName IS NULL AND @LastName IS NOT NULL AND @Title IS NULL) -- Search by last name only SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE LastName = @LastName ELSE IF (@FirstName IS NULL AND @LastName IS NULL AND @Title IS NOT NULL) -- Search by title only SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE Title = @Title ELSE IF (@FirstName IS NOT NULL AND @LastName IS NOT NULL AND @Title IS NULL) -- Search by first and last name SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE FirstName = @FirstName AND LastName = @LastName ELSE -- Search by any other combination SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE (@FirstName IS NULL OR (FirstName = @FirstName)) AND (@LastName IS NULL OR (LastName = @LastName )) AND (@Title IS NULL OR (Title = @Title )) END
Преимущество этого подхода состоит в том, что в обычных случаях, обрабатываемых запросами на заказ, запрос является настолько эффективным, насколько это возможно, - нет никакого влияния на неподдерживаемые критерии. Кроме того, индексы и другие улучшения производительности могут быть нацелены на конкретные запросы на заказ, а не на попытки удовлетворить все возможные ситуации.
Это также работает:
...
WHERE
(FirstName IS NULL OR FirstName = ISNULL(@FirstName, FirstName)) AND
(LastName IS NULL OR LastName = ISNULL(@LastName, LastName)) AND
(Title IS NULL OR Title = ISNULL(@Title, Title))