Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Необязательность параметра. Как "правильно" проверить  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Всем доброе время суток.

Когда я задавал подобные вопросы относительно входных параметров процедуры когда параметры необязательные и если он не передан, то мы его просто не проверяем.

and (@param is null or @param = t.fieldName)


В целом это решается "правильным динамическим СКЛ" и sp_executesql. Актуально когда параметров много и когда в зависимости от этих параметров нужен/не нужен join-оператор.

Теперь задача "усложняется". Параметры поиска лежат в таблице.

  from 
....

 cross
  join #cteSettings settings
 
...
  
 where 
   ...
   
   /*Stored procedure input parameters may be created*/
   and (@BatchID is null or cli.BatchID = @BatchID)
   
   and (@ClaimLineItemID is null or cli.ClaimLineItemID = @ClaimLineItemID)
   
   /*
   Check enabled options fore each bucket
   I will think how to optimize logig "(@variable is null or tableName.filedName = @variable)"
   it's will not easy because really we don't have "@variable". We have table with settings and different buckets may have different 
   settings. Part of parameters should be ignored for some buckets 
   */
   and (settings.carrierMustBeAssignedToTheSameUser is null or cli.AssignedToUserID = carr.AssignToUserID)
   and (settings.ClaimItemStatusID is null or  cli.StatusID = settings.ClaimItemStatusID)
   and (
        settings.DueDateLessOrEqualThanToday is null 
        or cli.DueDate <= @DateEnd or settings.DueDateLessOrEqualThanToday = 1
        or cli.DueDate > @DateEnd or settings.DueDateLessOrEqualThanToday = 0
        
       )
   and (settings.carrierBillAmountAndPaidAmoutEqual is null 
        
        or settings.carrierBillAmountAndPaidAmoutEqual = 0 /**/ 
       and (cli.sumPaidAmountCarrier <> cli.sumBillAmountCarrier)
        
        or settings.carrierBillAmountAndPaidAmoutEqual = 1 /**/ 
       and (cli.sumPaidAmountCarrier = cli.sumBillAmountCarrier)
        
       )
   and (settings.sumBillAmountCarrier is null or cli.sumBillAmountCarrier = settings.sumBillAmountCarrier)
   and (settings.sumPaidAmountCarrierMoreThan is null or cli.sumPaidAmountCarrier > settings.sumPaidAmountCarrierMoreThan)
   
   --Additional Work Needed bucket procedure             
   and (settings.checkIfDateIsExpired is null 
        or checkIfDateIsExpired = 0 or cli.expiredDate <= @DateEnd
        or checkIfDateIsExpired = 1 or cli.expiredDate > @DateEnd
       )
   and (settings.findBacketByStatusCarrierSettinsBacketID is null or stCar.BucketID is not null)
   and (settings.carrierHasWrittenOffPayment is null or cli.carrierHasWrittenOffPayment is not null)
   and (settings.sumBillAmountCarrierMoreThan is null or settings.sumBillAmountCarrierMoreThan >= cli.sumBillAmountCarrier)
   and (settings.sumPaidAmountCarrier is null or settings.sumPaidAmountCarrier = cli.sumPaidAmountCarrier)


Можно сказать что каждая строчка "отдельный набор параметров" соотвественно "все варианты поиска" находятся одноврменно.
В целом какая-та строчка может удовлетворить "более одному условию поиска".

Мне НЕ нравится тот подход что я использовал.
Особенно мне это не нравится потому что потом будет расширение набора условий поиска. Их должен будет задавать пользователь. Таблица "#cteSettings settings" будет представлять что-то вроде "таблицы истиности" с третьим состоянием (null).

Что бы ораганизовать условия ">, <, >=, <=, =' мне столько операторов "or" прийдётся захардкодить что просто ужас.

далее мне не нравится то что "критерии поиска" должны быть определены (и захардкожены) изначально.
Если бы это было не классическая структура БД, а какая-нить Entity-Attribute-Value, которая остаётся статичной при изменеии структуры сущностей, то тут у меня хотя бы идеи есть как всё оптимизировать, как выбрать количество атрибутов, которое участвует в условии "and" (одна строчка из таблицы) и количество совпадений для условия, потом их сравнить.
Тут хотя бы не прийдётся для каждого параметра поиска писать ( or or or or).

Выполнять поиски в курсоре отдельно для каждой строки я тоже не хочу.

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

Заранее спасибо!
17 янв 12, 11:57    [11916410]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31961
NIIIK
Тут хотя бы не прийдётся для каждого параметра поиска писать ( or or or or).
Для сохранения эффективности нужно использовать динамический SQL или перекомпилить запрос при каждом вызове.

NIIIK
Особенно мне это не нравится потому что потом будет расширение набора условий поиска. Их должен будет задавать пользователь.
Ещё вариант генерить процедур поиска на сервере. Много-много процедур.
17 янв 12, 12:48    [11916823]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
alexeyvg
NIIIK
Тут хотя бы не прийдётся для каждого параметра поиска писать ( or or or or).
Для сохранения эффективности нужно использовать динамический SQL или перекомпилить запрос при каждом вызове.

Согласен с коллегой, динамический SQL.

NIIIK
Особенно мне это не нравится потому что потом будет расширение набора условий поиска. Их должен будет задавать пользователь.
Ещё вариант генерить процедур поиска на сервере. Много-много процедур.


Есть ещё вариант, на вход получать XML с параметрами фильтрации, далее разбор и применение параметров на динSQL. Выигрыш - интерфейс вызова сводится к 1му параметру.
17 янв 12, 16:37    [11919339]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
>Для сохранения эффективности нужно использовать динамический SQL или перекомпилить запрос при каждом вызове.
Выполняется сразу "для всех строчек"
Динамический запрос подошёл бы если бы я хотел выполнить "построчно" поиск как будто каждый раз вызываю процедуру отлельно с отдельным набором параметров.

>Ещё вариант генерить процедур поиска на сервере. Много-много процедур.
Тогда их много много раз запускать надо так же.
Проще динамику чем много процедур для каждой вариации параметров.

>Есть ещё вариант, на вход получать XML с параметрами фильтрации,
>далее разбор и применение параметров на динSQL. Выигрыш - интерфейс вызова сводится к 1му параметру.
Ну предположим "передал я свою таблицу (которая у меня и так может хранится) ХМЛкой".
Толку, я его опять преобразую к таблице "#cteSettings settings" и буду выполнять таким же макаром с проверкой на "is null" если для конкретной строчки мне НЕ важно передан ли данный параметр.

От таких строчек " and (@BatchID is null or cli.BatchID = @BatchID)" я могу уйти Динамическим СКЛ.
От таких "and (settings.ClaimItemStatusID is null or cli.StatusID = settings.ClaimItemStatusID)" НЕТ.

Только если построчно буду выполнять поиск в грёбаном курсоре и для каждой строки отдельно генерировать логику. Но это менять "шило на мыло".
17 янв 12, 19:10    [11920558]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ещё вариант (один из), оставить это Entity Framework-у (если я не путаю).
Т.е. всё тот же динамический запрос, но генерируемый заточенной програмной средой.
Т.е. вопрос к архитектурному подходу.

Другой вариант (использую) - UNION ALL по соответствуюшим комбинациям запроса.
При этом UNION ALL не обязательно плоские / одномерные. Бывает честенько и много уровневые (обычно двух). Когда каждый набор параметров собирает / фильтрует данные на своём уровне / комбинации.
Куски плана отключаются (по значению параметров).

Недостаток - т.к. план один большой, есть вероятность что будет выпадать (на чрезмерно монструозных системах).
Приемущество - код в одном месте. Статика.
Объём плана в кеше может быть меньше их суммы (если по отдельности). Но с другой стороны, если какието комбинации слишком редки в запуске, планы процедур (когда всё отдельно) могут и отсутсвовать (не занимать кэшь).
17 янв 12, 19:25    [11920680]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31961
NIIIK
>Для сохранения эффективности нужно использовать динамический SQL или перекомпилить запрос при каждом вызове.
Выполняется сразу "для всех строчек"
Динамический запрос подошёл бы если бы я хотел выполнить "построчно" поиск как будто каждый раз вызываю процедуру отлельно с отдельным набором параметров.
Всё нормально, строчки у вас - просто список условий с OR

Так что можно строить большой динамический запрос, используя данные из #cteSettings, сваливая все условия в большие блроки, разделённые OR (по количеству строк в #cteSettings).
17 янв 12, 20:17    [11920997]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
>Только если построчно буду выполнять поиск в грёбаном курсоре и для каждой строки отдельно генерировать логику. Но это менять "шило на мыло".

У вас там ожидается стотыщмильёнов строк в таблице с параметрами? Если нет то в чем проблема с разбором этой таблицы, пусть и курсором? Дальше формирование динамического SQL или через OR или через UNION для каждой строки. С OR могут возникнуть большие проблемы, ибо оптимизатор не очень любит OR и иногда строит жуткие планы. Я бы сделал динамику через UNION с выбором только PK IDs во временную таблицу, а основной запрос можно даже в статике оставить. Хотя зависит от запроса.
17 янв 12, 21:07    [11921239]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Каждая строчка это как бы "результат поиска"

В принципе, можно считать две строчки как "ор" для одного и того же результата, если значение поля "результат поиска" одинаковое.

Табличка #cteSettings settings (пока хреновый хард код из кучи юнионов, потом будет настраиваемая) что-то вроде таблицы истиности. Только переменных много (карты Карно на такое рисовать не вариант, да и оптимизировать в динамическом СКЛе не получится), поэтому получение "true" для каждого из условий отдельно без оптимизации.

Приведу упорёщённый пример. У нас есть "Список людей" (но не одна сущность "человек", а куча связанных).
Стоит задача по каким-то "правилам" (включающим разные атрибуты, статусы) найти людей, добавить их в какой-то "список/отчёт" и потом поменять им значения каких-то атрибутов (после изменения этих значений при поиске он уже не будет удовлетворять условию поиска), например "статус".

Получается что-то вроде "предсохранённых отчётов/списков".
а-ля
1) Список людей от 18 до 27
2) Список людей от 18-20 мужского пола
....
N) "Список N" (муж от 18 до 20, женщины от 18 до 22) - будет реализован двумя строчками в #cteSettings как "OR".


После того как одно из правил сработало (сейчас привязываются к списку) - выполняются действия по изменению других атрибутов (пусть тот же "пол" или какой-то "статус", этот атрибут среди возможных параметров поиска)

Соотвественно для каждой строчки "отдельно" запуск делать не хочется.

Единственная "радость" что отфильтровать "какие экземлпря нужны" можно дополнительными параметрами, а потом по факут мы ищем только в какие списки этот экземпляр попадает (хотя может и не в какие).

Надеюсь что НЕ запутал и дал понимание что за проблема.
18 янв 12, 11:20    [11923349]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
NIIIK
Каждая строчка это как бы "результат поиска"
...
N) "Список N" (муж от 18 до 20, женщины от 18 до 22) - будет реализован двумя строчками в #cteSettings как "OR".



Идентификатор из #cteSettings - тоже результат запроса и используется для "вставки" в последствии.
18 янв 12, 11:23    [11923375]     Ответить | Цитировать Сообщить модератору
 Re: Необязательность параметра. Как "правильно" проверить  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31961
NIIIK
NIIIK
Каждая строчка это как бы "результат поиска"
...
N) "Список N" (муж от 18 до 20, женщины от 18 до 22) - будет реализован двумя строчками в #cteSettings как "OR".



Идентификатор из #cteSettings - тоже результат запроса и используется для "вставки" в последствии.
Тогда динамический запрос с UNION ALL для каждой строчки #cteSettings

Это самый быстрый вариант, даже не самый, а единственный :-)

Можно параметризированный динамический запрос, что бы эффективнее работал кеш (но будет опасность получить иногда неэффективный план)
18 янв 12, 14:41    [11925584]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить