Комментарии 16
INSERT IGNORE
#или
REPLACE
#или
INSERT ... ON DUPLICATE KEY UPDATE
replace ужасен по дизайну, т.к. технически при этом удаляется строка, а потом вставляется — тут и лишняя работа с диском и увеличение фрагментации и до кучи пересчет индексов (что особенно «приятно» при большой БД)
insert… on duplicate — тоже так себе, т.к. он каждый раз пробует делать инсерт и в случае ошибки делает апдейт, вот тут был драматический пост на тему проблем вытекающих из этого habr.com/en/post/156489
Автору зачет:)
Чаще всего разработчикам все равно, они берут какой нибудь фреймворк, который за них все это делает. Для них база данных черный ящик. Если в интерфейсе есть что то upserta, они просто его используют. Вся оптимизация начинается уже в продакшн. Имхо надо merge на уровне базы данных или фреймворка реализовывать нормально.
В данном случае достаточно научиться правильно писать merge и все.
Замерял на 10 млрд строк данных: merge по скорости всегда выигрывал, чем update с проверкой или insert с проверкой, или delete с проверкой. Но конечно просто вставка без проверок быстрее merge.
И не забываем, что merge (слияние) работает только между таблицами, у которых установлено взаимно-однозначное соответствие, т е одной строке первой таблицы может соответствовать не более одной строки из другой таблицы.
Merge бояться не стоит-на нем основана репликация слиянием, его стоит понимать и правильно использовать.
В данном случае достаточно научиться правильно писать merge и все.Всё-таки список проблем в Merge, который приведён по первой ссылке в этом посте, говорит, что этого недостаточно и лучше бы всё-таки его избегать.
на нем основана репликация слияниемизвестная своей беспроблемностью)?
Я читал все эти проблемы.
Но добавлю, что около 8 лет пользуюсь merge в том числе в системах реального времени (движение целей), а также репликацией слияния. И все работает.
Перед тем как критиковать встроенную репликацию, попробуйте написать свою. И после этого сделайте вывод какой вариант более безпроблемный.
Да, я вместе с коллегами делал собственную репликацию как на уровне самой СУБД, так и вообще как отдельный сервис на .NET и немного на C++. Также видел и другие решения разработчиков. После этого могу смело утверждать, что встроенные репликации в СУБД-это в большинстве своем оптимальные решения для большинства случаев задач, где требуется репликация.
По merge не забывайте, что все проблемы обнаружены в основном в 2008 версии скуля, тогда как merge я начал применять в 2012 версии, а затем и в 2014, и в 2016.
Сейчас успешно применяю merge в 2016, 2017 и в 2019 версиях.
Да, мне тоже нравится MERGE, он прикольный и удобный, но в критически-важные участки я его без веских причин не потащу — предсказуемость важнее.
Вы писали, что MERGE работает быстрее UPDATE — может быть есть конкретные примеры?
Перед тем как критиковать встроенную репликацию, попробуйте написать свою.ИМХО, странное предложение. Да репликация сложный механизм, но в ней есть проблемы и приводить её в пример в контексте «у меня с Merge всё работает и у репликации с Merge нет проблем» тоже странно, потому что репликация может привести ко множеству проблем безотносительно того — могу ли я, или кто-то другой, сделать лучше.
Я не говорил, что у встроенной репликации нет проблем, их просто нужно уметь обходить-правильно настроить разрешение конфликтов, к примеру.
По merge у меня свой опыт, когда он был применен в 2012 версии в системе реального времени (это критичная система, т к отслеживает движение целей как морских, так и воздушных).
И да, человек может критиковать любого эксперта, если их опыт пересекается. Если не пересекается, то нужно перепроверить.
В данном случае часто встречаю специалистов, которые боятся использовать merge и репликацию слиянием только потому, что либо остались с 2008 версией, либо не умеют с ними работать.
И судя по статье да, Аарон не договаривает, упрощая подачу материала для восприятия. Ведь проще просто аналоги привести и призвать не использовать более сложные подходы, чем объяснять как ими пользоваться для более оптимальной работы в дальнейшем. Ну это как миллионеру призвать больше зарабатывать простым смертным, чем брать ипотеку. Примерно с того же ракурса.
По примеру замера скорости-возьмите хотя бы 1 млн строк и проверьте: merge и update/delete с условием, на insert от 10 млн строк (правда обычно разбивают порциями, а не сразу миллионы вставляют, но это ради замеров).
Замеры делал почти 8 лет назад, затем 5 лет назад, а между этим и после-только замеры скорости конкретных запросов и хранимок, т е специально по merge уже не делал.
The MERGE plan may fail at execution time depending on the order in which rows are processed, and the distribution of data in the database. Worse, a previously solid MERGE query may suddenly start to fail unpredictably if a filtered unique index is added to the merge target table at any point.
Bug reproduces on:
SQL Server 2014 (SP3-CU2) build 12.0.6214.1
SQL Server 2012 (SP4-GDR) build 11.0.7462.6
Да, там же описаны воркаэраунды, но, считается ли это предсказуемым и допустимым поведением для mission-critical систем? Как по мне — нет.
Я не говорил, что у встроенной репликации нет проблемНу да, вы запретили мне их упоминать, пока я не напишу свою без этих проблем.
Я Вас удивлю, но с фильтрованными индексами в принципе проблемы есть в скуле и не только в merge.
Это вопрос к реализации и поддержки фильтрованных индексов самим скулем, а не к merge.
Я не использую фильтрованные индексы, предпочитая либо встроенное секционирование, либо разделение таблиц.
Данное описание ошибки говорит о том, что автор материала не в курсе проблем с фильтрованными индексами в скуле в принципе, т е поверил msdn, а на опыте шишек не набил.
Я не могу Вам ничего запретить, просто призвал критично относиться к любому мнению-даже авторитетному и все перепроверять.
При использовании merge проблема проявляется, при использовании update+insert — нет, но это проблема не merge? Окей. Но тогда в первом вашем сообщении хотелось бы видеть, что кроме умения готовить merge, нужно ещё отказаться от filtered indexes. Может ещё что-то использовать не стоит?
Что касается "автора материала не в курсе" — ну, даже не знаю что тут сказать.
Как раз таки фильтруемые индексы проявляются при фильтрациях и не важно где-в update, delete или merge.
Конкретный кейс у меня был, когда есть фильтруемый индекс, который по факту не просто оптимизатором не рассматривался, а даже через подсказки нельзя было его прилепить-вылетала ошибка, что не может построить план с этим индексом.
При этом похожие проблемы были и в других базах и таблицах.
Происходило тогда, когда таблица очень большая (миллиарды строк), а отфильтрованных данных очень мало (порядка несколько тысяч и менее).
Как только не пытались с коллегами исправить, в итоге выработали рекомендацию не использовать фильтруемые индексы, где предполагается или может быть так, что отфильтрованных данных будет многократно меньше всех данных в таблице, т е почти никогда не использовать такие индексы.
Да, и авторитеты тоже люди, а значит им не чуждо такие когнитивные особенности как искажение восприятия и подачи материалы. И все на свете знать невозможно.
Мы либо соглашаемся, либо перепроверяет. И у каждого свой опыт.
Если опыты пересекаются, то будет критика. Если опыт не пересекается, то либо можно доверять, либо перепроверить. Все перепроверять сразу тоже не получится, т к это слишком затратно по времени. Потому стоит перепроверять только то, что планируется использовать.
Как раз таки фильтруемые индексы проявляются при фильтрациях и не важно где-в update, delete или merge.ну я же специально привёл пример — где при использовании merge проблема есть, а при использовании update + insert — проблемы нет.
Я изначально «прицепился» к этой вашей фразе:
В данном случае достаточно научиться правильно писать merge и все.очень уж она категорично звучит и вот мы выяснили, что, как минимум, кроме правильного написания merge нужно ещё не использовать filtered index'ы.
Мне позиция «авторитетов» гораздо более близка. Они говорят менее квалифицированным людям — у merge есть проблемы, они могут выстрелить, лучше их не использовать. Вы пишете в комментариях — я использовал и использую, всё ок, не надо бояться. При этом не оговариваете условия использования.
Для использования MERGE у вас должна быть либо веская причина (возможно, вам нужна какая-то маловразумительная MERGE-функциональность)
Использование merge — это единственный более-менее вменяемый способ вставить данные из нескольких таблиц, связанных ключами master-detail в другие несколько связанных таблиц, но уже с ключами на основе identity, или автогенерируемых newsequeninalid().
Merge + output, разумеется.
Пожалуйста, прекратите использовать антипаттерн UPSERT (SQL Server)