Выборка с учетом параметра или выборка всех значений. | |
---|---|
sphinx Автор Сообщений: 31184 Откуда: Каменск-Уральски Дата регистрации: 22.11.2006 |
Допустим, написана процедура с параметром, имеющая запрос, в который этот передается параметр передается. Если он указан явно - должно получиться что-то такое:
Если параметр не указан, делается полная выборка:
На самом деле это можно решить 4 способами: 1) Написать динамический запрос. Но кода много, порой сам суть теряю, провожусь с ним. Да и в дальнейшем сопровождать тяжелее. Не хотелось бы. 2) Тупо поставить условие проверки и сделать два запроса по IF/CASE. Опять ненужное увеличение кода, тяжелее в сопровождении. 3) Написать процедуру на основе курсора
Она отработает (чисто интуитивно предполагаю) куда медленнее, чем сразу вся выборка. Маслать будет INSERT/UPDATE для каждой записи. 4) В конце концов остановился на такой идее - оздать темповую таблицу, а в условие WHERE прописать n_trade
Пока на такую конструкцию ругается, надо поставить оракловый девелопер, он показывает все ошибки (пока сижу на привычном PL/SQL Developer). Такая конструкция взлетит? Если есть ошибки - пишите, чтоб время не терял. ------------------ "Veni, vidi, vici!"(с) Исправлено 2 раз(а). Последнее : sphinx, 19.06.17 18:51 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Аспид Сообщений: 3475 Откуда: Москва Дата регистрации: 01.04.2005 |
Не знаю как в оракл.
В MS SQL решается например... pTrade если не передан, то null ... WHERE n_trade=isnull(pTrade,n_trade) Есть еще способы, но этот самый простой. По моему тут все анси - стандартно? ------------------ |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Igor Korolyov Сообщений: 34580 Дата регистрации: 28.05.2002 |
Плохо оптимизируемо, но вполне читаемо. Два параметра, второй "отключает" использование первого.
Динамический запрос - ну идея так себе, хотя и вполне рабочая. Проблема конкретно для такой задачи в том, что придётся ВСЁ РАВНО 2 ветки кода делать - одну С параметром а вторую БЕЗ. Единственный заметный плюс - "общая" часть запроса 1 раз пишется. Условие проверки и 2 запроса - вполне себе рабочий вариант - проблему создаёт лишь то что запрос может быть на 100500 строк, и его придётся повторять за исключением малой части. Если же там нет мега-вычислений и по сути "общая часть" тривиальна, то это, пожалуй, будет самый оптимальный способ. Про процедуру не уловил (т.к сама задача не озвучена)... Чушь какая-то, если честно. Не в смысле синтаксиса кода, а в смысле самой идеи. Какие-то временные таблицы непонятно зачем и чем заполняемые... Озвучь лучше ЦЕЛИКОМ ЗАДАЧУ. ------------------ WBR, Igor |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
sphinx Автор Сообщений: 31184 Откуда: Каменск-Уральски Дата регистрации: 22.11.2006 |
Интересная идея! Т.е. если параметр задан - n_trade будет сравниваться с pTrade, а если он NULL - то получится ничего не ограничивающее равенство n_trade=n_trade. Красиво! Цитата: Не догнал. Чему равно значение pNoTrade? Как мы определим факт, что pTrade - не NULL и надо использовать все значения n_trade? ------------------ "Veni, vidi, vici!"(с) Исправлено 1 раз(а). Последнее : sphinx, 19.06.17 21:26 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Igor Korolyov Сообщений: 34580 Дата регистрации: 28.05.2002 |
Только оптимизатору эта "красота" как ножиком по печени...
------------------ WBR, Igor |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
sphinx Автор Сообщений: 31184 Откуда: Каменск-Уральски Дата регистрации: 22.11.2006 |
А какие? А то Игорь пишет, что
------------------ "Veni, vidi, vici!"(с) |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
ssa Сообщений: 13008 Откуда: Москва Дата регистрации: 23.03.2005 |
------------------ Лень - это неосознанная мудрость. |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
AndyNigmatec Сообщений: 1574 Откуда: Волгоград Дата регистрации: 28.06.2015 |
как-то было дело аналогично выкрутился - передавался числовой параметр в запрос, суть - либо выбрать какое-либо ну назовем "подразделение" соотв. параметру, либо все вместе ежели параметр = 0 ...
сделал так: select ... тра-та-та ... where pole=:param or 0=:param тож побоялся что это похоронит запрос ... но по факту испытаний - все норм оказалось, индексы заюзались, время работы не пострадало, полагаю что оптимизатор просто умнее и обошел этот OR )))) - утверждать не буду - не помню деталей Исправлено 1 раз(а). Последнее : AndyNigmatec, 19.06.17 21:49 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Igor Korolyov Сообщений: 34580 Дата регистрации: 28.05.2002 |
В том то и дело что для варианта "выбрать всё" индекс пользовать не нужно - это как раз не оптимально будет.
Впрочем, если таблица не на 100500 записей, то потерями от неоптимальности такого запроса можно пренебречь. Не думаю что в оракле или MSSQL настолько умный оптимизатор что он сможет "понять смысл" подобной конструкции и соорудить 2 разных плана, оптимальные для этих очень разных случаев - выборка небольшого множества и выборка "всего". Хотя надо проверять на практике, может трюк и удастся - с заведомо маленьким значением или с тем же null-ом (в статистике по колонке есть и минимальное и максимальное значения - так что инфу о том что "это условие скорее всего вернёт 0 записей" оптимизатор имеет). Если не забуду, завтра проверю как оно работает ------------------ WBR, Igor |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Аспид Сообщений: 3475 Откуда: Москва Дата регистрации: 01.04.2005 |
А ты не слушай Игоря))) Ты попробуй, может и сгодится. Я везде такое леплю, не заметил тормоза. Правда как правило, все равно это ограниченная выборка, и как правило, это 1 из параметров (или несколько) Сергей привел вариант. Есть варианты, когда от типа зависит, ну и пишешь, если нул весь даипазон типа. Как правило, все же ориентируешься на конкретную ситуацию, таблицу. Но если 100тыщ записей, мой вариант, работает вполне шустро. (да вообще у него тормозов не замечал) ------------------ |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
pasha_usue Сообщений: 3650 Откуда: Е-бург Дата регистрации: 06.10.2006 |
Если высота таблицы 5-10-100 записей, то да, можно не слушать Игоря. А вот если должен использоваться индекс по этому полю, то вариант Игоря оптимальнее. Опять же, Игорь рассказал, когда его вариант теряет оптимальность. |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
PaulWist Сообщений: 14621 Дата регистрации: 01.04.2004 |
У меня решено решено через динамику, те в ХП передаётся "сформированная строка where .... order by ...." (ну не совсем так, для простоты примем), затем условие просто склеивается с запросом, схематично так:
------------------ Есть многое на свете, друг Горацио... Что и не снилось нашим мудрецам. (В.Шекспир Гамлет) Исправлено 2 раз(а). Последнее : PaulWist, 20.06.17 10:04 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Аспид Сообщений: 3475 Откуда: Москва Дата регистрации: 01.04.2005 |
Полагаю речь идет о тысячах.
В начале процедуры выясняешь что с параметром.
------------------ |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
spinz Сообщений: 5263 Дата регистрации: 21.01.2016 |
Это самое правильное. Запрос должен выполнять свое прямое назначение, а не анализировать параметры процедуры и строить условия в зависимости от них. |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Igor Korolyov Сообщений: 34580 Дата регистрации: 28.05.2002 |
В общем провёл небольшое исследование для такого рода запросов...
Тестовые данные (~40Мб таблица, ~17Мб индекс - в общем очень скромные, но этого вполне достаточно для сравнения): Тестовый стенд: Запросы с параметрами и их результат: 1 группа - выборка с ограничением (0.1% всех записей) 2 группа - выборка БЕЗ ограничений (все записи). Вывод прост. Для выборки всех записей любой вариант работает одинаково и индекс не пытается использовать (и это можно признать оптимальным способом). Для выборки "с условием" оптимально работают 2 варианта: "очевидный" и НЕ универсальный
И вариант с "хитрым NVL"
На 11-й версии сервера поведение аналогичное. Более древних нет под руками, да и неактуально. Итого: Условие SOME = NVL( :PSOME, SOME) рулит, все прочие варианты (исключая "многословный" - когда ДВА запроса пишутся и во время исполнения нашим собственным кодом выбирается один из них) - фтопку. ------------------ WBR, Igor |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Igor Korolyov Сообщений: 34580 Дата регистрации: 28.05.2002 |
Динамика в запросах совершенно аналогична макро в фоксе. Должна использоваться ТОЛЬКО в случаях когда другие варианты не работают. И, как и с тем же самым макро, в реальной практике 90% динамических запросов написанных разработчиками не нужны - задачи могут быть решены другим способом и не менее эффективно. В общем это достаточно опасный инструмент, и часто применяется не по назначению - во вред программе. ------------------ WBR, Igor |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
AndyNigmatec Сообщений: 1574 Откуда: Волгоград Дата регистрации: 28.06.2015 |
ну как бэээ ... смотря к чему параметр, поясню на примере
здесь в примере таблы filial, hprice - совсем скромные tmed ~ 110k tprice ~ 1750k 1). полный без ограничений по дивизиону: 2). Полный с вариантом pole=:param or 0=:paramp 3) полный с вариантом NVL разницы то я и не увидел .... теперь даю param конкретное значение: 1). прямой вариант pole=:paramp 2). вариант pole=:paramp or 0=:param 3). вариант c NVL и снова выполнение в принципе равнозначно по затратам Но здесь как видно параметр задавался маленькой табле ... и как видно из плана индекс по этому полю ни в одном запросе не использовался - т.е. ожидаемый результат. Ситуация в корне меняется если в выборке участвует индекс по полю с нашим параметром: в случае конкретного значения: 1). pole=:param 2) pole=:param or 0=:param 3). то же с NVL т.е. индекс в 2) и 3) идет лесом - и соотв. скорость тож ... Исправлено 12 раз(а). Последнее : AndyNigmatec, 20.06.17 19:36 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
Igor Korolyov Сообщений: 34580 Дата регистрации: 28.05.2002 |
Не, ну очевидно что если "ограничивающее действие" параметра где-то настолько "сбоку" что есть оно или нет его один чёрт, то и париться смысла нет
Но если таки оно важно, то стоит искать вариант где оптимизатор сработает... Для оракла это оказался вариант с NVL() - для MSSQL (или что это за сервер тестировался?) вполне может быть нужен совсем другой... Может быть даже какой-нить корявый UNION с "условно" отключающимися частями... Оракловский план, кстати, это примерно и есть такой по смыслу UNION - параметр null - идём в одну ветку, параметр не null - идём в другую ветку... А вот OR он не может (или не хочет) так "разложить". Хотя я особо не мучился - м.б. путём хитрого сочетания скобок или "обманок" типа some between :p1*:p_min and :p1*:p_max с игрой этими доппараметрами и можно добиться желаемого поведения - или даже проще, убрать p1 и рулить уже собственно значениями для параметров - либо "от 10 до 10", либо "от -100500 до +100500" Современные сервера (их оптимизаторы) достаточно сложны, и способны реально менять план для "буквально одинаковых" запросов в зависимости от значений параметров. По крайней мере для оракла такая фишка декларируется. Он может "на лету" перейти от полного сканирования к работе по индексу и наоборот, "подсмотрев" значения параметров. ------------------ WBR, Igor Исправлено 1 раз(а). Последнее : Igor Korolyov, 20.06.17 21:01 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
AndyNigmatec Сообщений: 1574 Откуда: Волгоград Дата регистрации: 28.06.2015 |
это firebird, ну это так к слову ... и кстати насчет union - действительно, кое-где именно его использовал, но не для "разветвления" запроса - а для ускорения работы (как бы это странно не прозвучало, но факт)
З.Ы. я для чего про параметр "сбоку" - может у в задаче ТС параметр также достаточно "сбоку" Исправлено 1 раз(а). Последнее : AndyNigmatec, 20.06.17 21:59 |
Re: Выборка с учетом параметра или выборка всех значений. | |
---|---|
spinz Сообщений: 5263 Дата регистрации: 21.01.2016 |
В данном конкретном случае ты закладываешься на то, что оракловый оптимизатор правильно "разрулит" подобное условие. Вот выйдет завтра новая версия оракла, где по неведомым причинам логика оптимизатора будет несколько иной - и придется переписывать запрос. Или этот запрос надо использовать под другой СУБД, где оптимизатор работает по-другому. Зачем в данном случае эти сложности, если несколькими строчками кода можно прилепить требуемое условие к тексту запроса и забыть о реализации движка СУБД? |
© 2000-2024 Fox Club  |