Обработка нескольких параметров в SQL [дубликат]

, если вы используете runnable, вы можете сохранить пространство, чтобы перейти к любому другому классу.

159
задан dckuehn 25 August 2016 в 19:07
поделиться

6 ответов

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

Прочитайте это и рассмотрите все методы. Ваш лучший метод будет зависеть от ваших параметров, ваших данных, вашей схемы и фактического использования:

Условия динамического поиска в 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
231
ответ дан Community 19 August 2018 в 19:11
поделиться
  • 1
    Будьте осторожны с приоритетом AND / OR. И имеет приоритет над OR, поэтому без соответствующих скобок этот пример не даст ожидаемых результатов ... Так что shoudl читает: (@FirstName IS NULL ИЛИ (FirstName = @FirstName)) AND (@LastNameIS NULL OR (LastName = @LastName)) AND (@TitleIS NULL OR (Title = @Title)) – Bliek 10 October 2011 в 12:51
  • 2
    @Bliek, спасибо, я исправил его. – KM. 10 October 2011 в 16:05
  • 3
    ... (@FirstName IS NULL ИЛИ (FirstName = @FirstName) должно быть ... (FirstName = Coalesce (@ firstname, FirstName)) – fcm 27 February 2018 в 17:25

Пять лет опоздали на вечеринку.

В приведенных ссылках принятого ответа упоминается, но я думаю, что он заслуживает явного ответа на 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'

Плюсы:

  • легко писать и понимать гибкость
  • - легко генерировать запрос для более сложных фильтров (например, динамический TOP)

Минусы:

  • возможные проблемы с производительностью в зависимости от предоставленных параметров, индексов и объема данных

Не прямой ответ, но связанный с проблемой 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;
    }

Плюсы :

  • динамически сгенерированный запрос на основе предоставленных фильтров. Нет необходимости в настройке параметра sniffing или recompile
  • несколько проще для тех, кто в мире ООП
  • , как правило, дружественный к производительности, поскольку «Простые» запросы будут выдаваться (соответствующие индексы все еще необходимы)

Минусы:

  • Ограничения LINQ2QL могут быть достигнуты и принуждение к переходу на LINQ2Objects или Возвращаясь к чистому решению SQL в зависимости от случая
  • , неосторожное письмо LINQ может генерировать ужасные запросы (или много запросов, если загружены свойства навигации)
4
ответ дан Alexei 19 August 2018 в 19:11
поделиться

Вы можете сделать в следующем случае:

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

однако зависят от данных, иногда лучше создавать динамический запрос и выполнять их.

19
ответ дан Michael Pakhantsov 19 August 2018 в 19:11
поделиться

Расширьте условие 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. е. объединить различные случаи с булевыми условиями.

7
ответ дан nalply 19 August 2018 в 19:11
поделиться

Ответ от @KM хорош, насколько это возможно, но не может полностью следить за одним из его ранних советов,

..., игнорировать компактный код, игнорировать беспокойство о повторении кода, ...

Если вы хотите достичь максимальной производительности, вы должны написать индивидуальный запрос для каждой возможной комбинации дополнительных критериев. Это может показаться экстремальным, и если у вас есть много дополнительных критериев, то это может быть, но производительность часто является компромиссом между усилиями и результатами. На практике может существовать общий набор комбинаций параметров, который может быть нацелен на заказные запросы, а затем общий запрос (согласно другим ответам) для всех других комбинаций.

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

Преимущество этого подхода состоит в том, что в обычных случаях, обрабатываемых запросами на заказ, запрос является настолько эффективным, насколько это возможно, - нет никакого влияния на неподдерживаемые критерии. Кроме того, индексы и другие улучшения производительности могут быть нацелены на конкретные запросы на заказ, а не на попытки удовлетворить все возможные ситуации.

23
ответ дан Rhys Jones 19 August 2018 в 19:11
поделиться
  • 1
    Разумеется, было бы лучше написать отдельную хранимую процедуру для каждого случая. Тогда не беспокойтесь о спуфинге и перекомпиляции. – Jodrell 10 May 2018 в 08:07
  • 2
    Само собой разумеется, что этот подход быстро становится кошмаром для обслуживания. – Atario 24 May 2018 в 22:58
  • 3
    @Atario Простота обслуживания и производительности - общий компромисс, этот ответ ориентирован на производительность. – Rhys Jones 31 May 2018 в 18:41

Это также работает:

    ...
    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))
-3
ответ дан v2h 19 August 2018 в 19:11
поделиться
Другие вопросы по тегам:

Похожие вопросы: