Русский
Русский
English
Статистика
Реклама

Эволюция моих SQL запросов

Всем привет! Я тимлид и Senior Oracle Developer, 12 лет работаю с OeBS и в основном пишу SQL запросы. Хотел бы рассказать, как за это время менялся мой подход в написании SQL запросов.

Вначале было слово, а точнее запрос. Скажем

select name from user where id = 1

Написать такой запрос как-то не так практически невозможно. Он одинаково хорошо работает во всех известных мне базах данных. А знаю я только oracle :З Но подозреваю что и в других реляционных тоже всё будет ок.

Так что же произошло? Проблемы начались, когда таблиц стало две:

select name from user u, rest r where u.id = 1 and u.id = r.user_id

Этот код вызывал у меня больше вопросов. Например, как должны быть соединены эти таблицы? Казалось бы что проще id = user_id, но мне что-то не нравилось. В блоке where мне не хватало четкого разделения между условиями фильтрации и соединениями таблицам. Когда запрос содержал 2 таблицы всё ещё было норм, но когда кол-во таблиц доходило до 5 - всё рассыпалось. Взглянув на запрос, я не мог сразу понять как соединены таблицы и не пропущена ли какая-то связка. И с этим все прекрасно жили, но я не мог. Однажды мне, молодому джуну, на глаза попался ANSI синтаксис.

select name from user inner join rest on u.id = r.user_id where u.id = 1

букв стало немного больше, но я намного лучше стал понимать, как связаны таблицы в моих SQL выражениях. Мир запросов расцвёл для меня новыми красками, и я больше не писал запросы как-то иначе. А ещё распространял эту весть среди других джунов. Это был мой первый шаг в эволюции SQL. Я вырвался от привычных шаблонов легаси кода и сделал что-то своё. Но была одна проблема. Когда используется скажем левостороннее соединение ANSI синтаксис заставляет переносить в связки, и все прочие ограничения для таблицы.

select u.name, r.resp_name from user u left join resp r on u.id = r.user_id  and r.end_date > sysdate where id = 1

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

select resp_q as (  select resp_name, userid   from resp where r.end_date > sysdate) ,main_q as (   select u.name, r.respname   from user u    left join resp_q r on u.id = r.userid   where id = 1) select * from main_q

Кода стало опять большое, но запросы в with позволили мне разбить монолитный запрос и группировать разные кусочки запроса по историям, а потом сплетать их вместе. Я мог рассказать про свой запрос так: Получаем список пользователей. Список ролей. Объединяем их в одну выборку и отсекаем тех кто нам не нравится. С оставшимися идём дальше, взявшись за руки. И за каждый шаг отвечала свой небольшой именованный запрос. Это также помогло мне бороться с моим злейшим врагом WET, т.к. одни и те же истории я мог использовать в разных частях своего запроса, не дублируя код. Ко всему прочему, упростилась отладка. Знай в блок from подставляй разные именованные запросы и отлаживай их по отдельности. А ещё, как выяснилось позже, с помощью with можно оптимизировать запросы, используя hint MATERIALIZE. Он материализует именованный подзапрос и данные при запросе из него берутся из темпового пространства. До этого я использовал обычные темповые таблицы. Это было более грубое решение, т.к. создавались лишние объекты БД + надо было помнить про очистку. Как итог, теперь, если запрос сложнее 10 строк, я почти всегда использую with.

Но чего-то не хватало. По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает. Как итог, я часто отдавал не до конца протестированный код. Мне регулярно приходилось слышать про unit тесты, автотесты и прочее. Но сложно было это применить к БД. Сегодня сумма за период равна 100р, а завтра 120р. И как ты тут напишешь тест? Так и жил Но, уже став тимлидом, мне попалась задача, в которой надо было найти отмененные документы. Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию).

select * from document where xxstorno(id) = 'Y'

У меня было порядка 10 примеров документов. И завершая условие для одного документа, что-то ломалось в другом. А так как тестировал руками и глазами, времени уходило просто море. Я уже думал этому не будет конца. Пока не понял, что вокруг моего запроса можно написать обертку, которая будет за меня проверять все мои кейсы и говорить какие документы прошли проверку, а какие нет. Потратив на обертку несколько минут, я сократил время тестирования с 5-7 минут, до нескольких секунд.

with test_case as (  select 10 id, 'Y' storno from dual   union all   select 5 id, 'N' storno from dual)  , run_test as (    select tc.id, decode(xxstorno(d.id), tc.storno, 'OK', 'Error') result    from test_case tc    left join documentd on d.id = tc.id) select * from run_test

После правки функции, я просто запускал тест-запрос и смотрел сколько документов прошло тестирование, а сколько нет. В процессе тестирования я накидывал туда ещё кейсов, при том что про старые тесты тоже не забывались. И тогда я понял, как же это здорово! Как можно легко тестировать свой запрос, повышать надёжность и при этом не нужно ничего делать руками. Это может показаться элементарным, но до этого мне не встречались подобные конструкции. Обычно я видел конструкции типа and id = 5--6 7 10 135 1345 в которой просто перебором подставлялись разные значения и руками смотрелось что и как оно должно возвращать. С того дня я написал несколько разработок, и к каждой из них я уже готовил свой тестовый скрипт. Данный стиль мне очень понравился и теперь я пытаюсь привить его и своим разработчикам. Чтобы им не пришлось проделать путь в 12 лет, чтобы писать красивые SQL запросы.

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

Источник: habr.com
К списку статей
Опубликовано: 26.01.2021 00:16:12
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Oracle

Sql

Developer

Pl/sql

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru