Какой из методов встроенного sql правильный: Руководство по архитектуре обработки запросов — SQL Server
Содержание
Механизм запросов
Механизм запросов — это один из способов доступа к данным, которые поддерживает платформа. Используя этот механизм, разработчик может читать и обрабатывать данные, хранящиеся в информационной базе; изменение данных с помощью запросов невозможно. Это объясняется тем, что запросы специально предназначены для быстрого получения и обработки некоторой выборки из больших массивов данных, которые могут храниться в базе данных.
Табличный способ доступа к данным
Запросы реализуют табличный способ доступа к данным, которые хранятся в базе данных. Это означает, что все данные представляются в виде совокупности связанных между собой таблиц, к которым можно обращаться как по-отдельности, так и к нескольким таблицам во взаимосвязи:
Такой способ работы с данными позволяет получать сложные выборки данных, сгруппированные и отсортированные определенным образом. Для этих выборок могут быть рассчитаны общие и промежуточные итоги, наложены ограничения на количество или состав записей и пр.
Язык запросов
Для того чтобы разработчик имел возможность использовать запросы для реализации собственных алгоритмов, в платформе реализован язык запросов. Этот язык основан на SQL, но при этом содержит значительное количество расширений, ориентированных на отражение специфики финансово-экономических задач и на максимальное сокращение усилий по разработке прикладных решений. Можно перечислить наиболее существенные возможности, реализуемые языком запросов:
Обращение к полям через точку (».»)
Если поля какой-либо таблицы имеют ссылочный тип (хранят ссылки на объекты другой таблицы), разработчик может в тексте запроса ссылаться на них через «.», при этом количество уровней вложенности таких ссылок система не ограничивает.
Обращение к вложенным таблицам (табличным частям документов и элементов справочников)
Система поддерживает обращения к вложенным табличным частям и как к отдельным таблицам, и как к целым полям одной таблицы. Например, при обращении к документу Реализация товаров (содержащему табличную часть Товары с составом отгружаемых товаров), мы можем считать табличную часть как отдельную таблицу:
Но также мы можем считать заголовочную запись документа, в которой значением поля Товары будут все записи вложенной таблицы, подчиненные этому объекту (документу):
Автоматическое упорядочивание
Для выбора наиболее правильного («естественного») порядка вывода информации на экран или в отчет разработчику в большинстве случаев достаточно задать режим автоматического упорядочивания.
Многомерное и многоуровневое формирование итогов
Итоги и подитоги формируются с учетом группировки и иерархии, обход уровней может выполняться в произвольном порядке с подведением подитогов, обеспечивается корректное построение итогов по временным измерениям.
Поддержка виртуальных таблиц
Виртуальные таблицы, предоставляемые системой, позволяют получить практически готовые данные для большинства прикладных решений без необходимости составления сложных запросов. Например, такая виртуальная таблица может предоставить данные по остаткам товаров в разрезе периодов на какой-то момент времени. При этом виртуальные таблицы максимально используют хранимую информацию, например, ранее рассчитанные итоги и т. д.
Стандартные SQL операции
В языке запросов поддерживаются стандартные для SQL операции, такие, как объединение (Union), соединение (Join) и т. д.
Временные таблицы
Язык запросов позволяет использовать в запросах временные таблицы. С их помощью можно повысить производительность запросов, в некоторых случаях снизить количество блокировок и сделать текст запроса более легким для восприятия.
Предположим, нужно получить данные из двух регистров накопления. Данные из одного регистра поместим во временную таблицу:
Запрос, использующий временную таблицу, будет иметь вид:
Пакетные запросы
Для более удобной работы с временными таблицами в языке запросов поддерживается работа с пакетными запросами — таким образом, создание временной таблицы и ее использование помещаются в один запрос. Пакетный запрос представляет собой последовательность запросов, разделенных символом «;». Запросы исполняются один за другим. Результатом выполнения пакетного запроса в зависимости от используемого метода будет являться либо результат, возвращаемый последним запросом пакета, либо массив результатов всех запросов пакета в той последовательности, в которой следуют запросы в пакете.
Конструкторы запроса
Для облегчения труда разработчика технологическая платформа содержит два специальных конструктора. Они служат для того, чтобы помочь разработчику составить правильный текст запроса, используя только визуальные средства. Выбирая мышью нужные поля таблиц, разработчик может составить работоспособный запрос, даже не зная синтаксиса языка запросов.
- Конструктор запроса позволяет составить только текст запроса. Подробнее…
- Конструктор запроса с обработкой результата помимо текста запроса формирует фрагмент программного кода, который исполняет запрос и выводит результаты в табличный документ или диаграмму. Подробнее…
Консоль запросов
Инструмент «Консоль запросов» позволяет разработчикам конфигураций и специалистам по внедрению отлаживать запросы и просматривать результаты их выполнения в режиме «1С:Предприятие 8». Подробнее…
Язык запросов «1С:Предприятия 8» (+диск)
Авторы: Е.Ю. Хрусталева
Фрагментация базы данных: основные методы и случаи использования
4 марта, 2019 12:20 пп
5 970 views
| Комментариев нет
MariaDB, mySQL, VPS | Amber
| Комментировать запись
Любое быстро растущее приложение или веб-сайт в конечном итоге нуждаются в масштабировании, чтобы приспособиться к увеличению трафика. Для приложений и веб-сайтов, управляемых данными, очень важно, чтобы при масштабировании была возможность обеспечить безопасность и целостность данных. Трудно предсказать, насколько популярным станет сайт или приложение и как долго он будет поддерживать эту популярность, поэтому некоторые организации выбирают архитектуру базы данных, которая позволяет им динамически масштабировать их.
В этой статье мы обсудим одну такую архитектуру – фрагментированные базы данных. В последние годы шардингу уделяется много внимания, но многие пользователи не имеют четкого понимания того, что это такое, и сценариев, в которых имеет смысл выполнять фрагментацию. Здесь мы рассмотрим, что такое шардинг, обсудим некоторые из его основных преимуществ и недостатков, а также несколько распространенных методов.
Что такое фрагментация базы данных?
Фрагментация базы данных, или шардинг – это шаблон архитектуры базы данных, связанный с горизонтальным секционированием (это разделение строк одной таблицы на несколько различных таблиц, называемых разделами). Каждый раздел имеет одинаковую схему и столбцы, но разные строки. Соответственно, данные, хранящиеся в каждом из них, уникальны и не зависят от данных, хранящихся в других разделах.
Полезно сравнить горизонтальное секционирование с вертикальным. В таблице с вертикальным секционированием целые столбцы разделяются и помещаются в отдельные таблицы. Данные, содержащиеся в одном вертикальном разделе, не зависят от данных во всех остальных таких разделах, каждый из них содержит как отдельные строки, так и отдельные столбцы.
CUSTOMER ID | FIRST NAME | LAST NAME | FAVORITE COLOR |
1 | TAEKO | OHNUKI | BLUE |
2 | O.V. | WRIGHT | GREEN |
3 | SELDA | BAGCAN | PURPLE |
4 | JIM | PEPPER | AUBERGINE |
VP1 | VP2 | ||||
CUSTOMER ID | FIRST NAME | LAST NAME | CUSTOMER ID | FAVORITE COLOR | |
1 | TAEKO | OHNUKI | 1 | BLUE | |
2 | O. V. | WRIGHT | 2 | GREEN | |
3 | SELDA | BAGCAN | 3 | PURPLE | |
4 | JIM | PEPPER | 4 | AUBERGINE |
HP1 | |||
CUSTOMER ID | FIRST NAME | LAST NAME | FAVORITE COLOR |
1 | TAEKO | OHNUKI | BLUE |
2 | O.V. | WRIGHT | GREEN |
HP2 | |||
3 | SELDA | BAGCAN | PURPLE |
4 | JIM | PEPPER | AUBERGINE |
Фрагментация включает в себя разбиение данных на две или несколько меньших частей, которые называются логическими сегментами. Затем они распределяются по отдельным узлам базы данных, – так называемым физическим сегментам, – которые могут содержать несколько логических сегментов. Данные, хранящиеся во всех сегментах, представляют собой целый набор логических данных.
Сегментированная базы данных – пример архитектуры без общего доступа. Это означает, что все сегменты автономны; они не используют одни и те же данные или вычислительные ресурсы. В некоторых случаях, однако, может иметь смысл копировать определенные таблицы в каждый сегмент, чтобы использовать их для справки. Например, скажем, есть база данных для приложения, которая зависит от фиксированных коэффициентов пересчета для измерений веса. Реплицируя таблицу, содержащую необходимые данные о коэффициенте конверсии, в каждый сегмент, вы можете предоставить каждому сегменту все данные, необходимые для запросов.
Часто фрагментация реализуется на прикладном уровне, то есть приложение включает в себя код, который определяет, какой сегмент передавать на чтение и запись. Однако некоторые системы управления базами данных имеют встроенные возможности фрагментации, что позволяет реализовать ее непосредственно на уровне базы данных.
Теперь давайте рассмотрим некоторые плюсы и минусы этой архитектуры базы данных.
Преимущества фрагментации БД
Основное преимущество шардинга заключается в том, что он может упростить горизонтальное масштабирование (scaling out). Горизонтальное масштабирование – это добавление новых машин к существующему стеку, что позволяет распределить нагрузку и быстрее обрабатывать больший объем трафика. Эта практика часто сравнивается с вертикальным масштабированием (scaling up), которое включает в себя обновление аппаратного обеспечения существующего сервера, обычно путем добавления большего объема ОЗУ или ЦП.
Поддерживать реляционную базу данных, работающую на одной машине, и масштабировать ее вертикально по мере необходимости, обновляя ее вычислительные ресурсы, относительно просто. Однако, в конечном счете любая несегментированная база данных будет ограничена с точки зрения хранения и вычислительной мощности, поэтому возможность горизонтального масштабирования делает вашу установку гораздо более гибкой.
Другой причиной, по которой некоторые выбирают архитектуру фрагментированной БД, является сокращение времени ответа на запрос. При отправке запроса к не фрагментированной базе данных поиск выполняется по каждой строке в таблице, прежде чем будет найден набор искомых результатов. В приложении с большой монолитной базой данных запросы могут обрабатываться очень медленно. В сегментированной таблице при поиске просматривается меньшее количество строк, а результаты возвращаются намного быстрее.
Фрагментация может также сделать приложение более надежным за счет смягчения последствий сбоев. Если ваше приложение или веб-сайт использует монолитную базу данных, сбой может привести к недоступности всего приложения. Однако при использовании фрагментированной базы данных отключение может повлиять только на один сегмент данных. Некоторые части приложения или веб-сайта могут стать недоступными, но общее воздействие все равно будет меньше, чем в случае сбоя всей базы данных.
Недостатки фрагментации БД
Хотя фрагментация базы данных может упростить масштабирование и повысить производительность, она также может накладывать определенные ограничения. В этом разделе мы обсудим некоторые из этих ограничений и ситуации, в которых лучше вообще не использовать шардинг.
Первая проблема, с которой люди сталкиваются при использовании фрагментации – это сложность правильной реализации такой архитектуры. Если фрагментировать данные неправильно, это может привести к потере данных или повреждению таблиц. Но даже если все сделано правильно, фрагментация может оказать существенное влияние на рабочие процессы вашей команды. Вместо того чтобы получать доступ к данным и управлять ими из одной точки, членам команды придется управлять данными в нескольких сегментах, что может помешать работе некоторых групп.
Проблема, с которой пользователи иногда сталкиваются после фрагментации базы данных, заключается в том, что сегменты в конечном итоге сильно разнятся в размерах. Предположим, у вас есть база данных с двумя отдельными сегментами: один для клиентов, чьи фамилии начинаются с букв от A до M, а второй – для тех, чьи фамилии начинаются с букв от N до Z. Однако ваше приложение обслуживает очень много пользователей, чьи фамилии начинаются с буквы G. Соответственно, первый сегмент постепенно накапливает больше данных, чем второй. Это приводит к замедлению работы приложения при обслуживании значительной части ваших пользователей. В этом случае любые преимущества фрагментации базы данных сводятся на нет замедлениями и сбоями. Скорее всего, базу данных необходимо будет восстановить и перенастроить, чтобы обеспечить более равномерное распределение данных.
Другим существенным недостатком является то, что после фрагментации БД может быть очень трудно вернуть ее к предыдущей архитектуре. Резервные копии базы данных, сделанные до ее фрагментации, не содержат данных, записанных после фрагментации. Следовательно, для восстановления исходной архитектуры потребуется объединить новые сегментированные данные со старыми резервными копиями или, наоборот, преобразовать сегментированную БД обратно в единую БД, что потребует больших затрат и времени.
Последний недостаток, который следует учитывать, заключается в том, что фрагментация изначально поддерживается не всеми механизмами БД. Например, PostgreSQL не включает автоматическую фрагментацию как функцию «из коробки», хотя вручную сегментировать базу данных PostgreSQL можно. Существует ряд форков Postgres, которые предлагают автоматическую фрагментацию, но они часто отстают от последнего релиза PostgreSQL и не имеют других важных и новых функций. Некоторые специализированные технологии, такие как MySQL Cluster и подобные продукты типа база данных как сервис (например, MongoDB Atlas), включают функцию автоматической фрагментации, но базовые версии этих СУБД этого не делают. Потому фрагментация часто требует «самодельного» подхода. Следовательно, зачастую найти нужную документацию или советы по устранению неполадок бывает трудно.
Это, конечно, только некоторые общие проблемы, которые необходимо учитывать, прежде чем фрагментировать свои данные. В зависимости от варианта использования у фрагментации может появиться гораздо больше потенциальных недостатков.
Теперь давайте рассмотрим несколько архитектур фрагментированных баз данных.
Виды фрагментированных архитектур
Приняв решение фрагментировать свои данные, вам нужно выяснить, каким именно образом это делать. При выполнении запросов или распределении входящих данных в сегментированные таблицы или БД важно, чтобы они передавались в правильный сегмент. В противном случае это может привести к потере данных или крайне медленной обработке запросов. В этом разделе мы рассмотрим несколько распространенных архитектур фрагментации, каждая из которых использует немного другой процесс для распределения данных между сегментами.
Фрагментация по ключам
Фрагментация по ключам (key based sharding), также известная как фрагментация по хешу (hash based sharding), подразумевает использование значения, взятого из вновь записанных данных (таких как идентификационный номер клиента, IP-адрес клиентского приложения, почтовый индекс и т. д.), и включение его в хэш-функцию, чтобы определить, к какому сегменту данных следует обращаться. Хеш-функция – это функция, которая принимает в качестве ввода часть данных (например, электронную почту клиента) и выводит дискретное значение, известное как хеш. В контексте фрагментации значение хэша является идентификатором сегмента, который используется для определения того, на каком сегменте будут храниться входящие данные. В целом процесс выглядит так:
Ключ | |||||||
Столбец1 | Столбец2 | Столбец3 | |||||
A | |||||||
B | |||||||
C | |||||||
D | |||||||
↓ | ↗ | Сегмент1 | |||||
↓ | ↑ | Столбец1 | Столбец2 | Столбец3 | |||
↓ | Столбец1 | Хеш-значения | ↗ | A | |||
Хеш-функция | → | A | 1 | C | |||
B | 2 | ↘ | |||||
C | 1 | ↘ | Сегмент2 | ||||
D | 2 | Столбец1 | Столбец2 | Столбец3 | |||
B | |||||||
D |
Чтобы обеспечить правильное размещение записей в правильных сегментах, все значения, введенные в хэш-функцию, должны поступать из одного столбца. Этот столбец называется ключом сегмента. Проще говоря, ключи сегментов похожи на первичные ключи тем, что они представлены столбцами, которые используются для создания уникального идентификатора отдельных строк. Вообще ключ сегмента должен быть статическим, то есть он не должен содержать значений, которые могут меняться со временем. В противном случае это увеличит объем работы и может снизить производительность.
Фрагментация по ключам является довольно распространенной архитектурой, но она может усложнить задачу при динамическом добавлении или удалении дополнительных серверов в БД. Когда вы добавляете серверы, каждому из них потребуется соответствующее значение хеш-функции, и многим из существующих записей (если не всем) необходимо будет присвоить новое, правильное значение хеш-функции, а затем перенести на соответствующий сервер. Когда вы попробуете заново сбалансировать данные, ни новые, ни старые хеш-функции не будут действительны. Следовательно, ваш сервер не сможет записывать какие-либо новые данные во время миграции, и ваше приложение может простаивать.
Основная привлекательность этой стратегии в том, что она может использоваться для равномерного распределения данных и предотвратить hotspot. Кроме того, поскольку эта модель распределяет данные алгоритмически, нет необходимости поддерживать карту расположения всех данных, как для других стратегий, таких как фрагментация по интервалам и каталогам.
Фрагментация по интервалам
Фрагментация по интервалам подразумевает сегментирование данных на основе диапазонов заданного значения. Предположим, у вас есть база данных, в которой хранится информация обо всех продуктах в каталоге. Вы можете создать несколько разных сегментов и отделить информацию о каждом продукте в зависимости от ценового диапазона, в который они попадают, например, так:
PRODUCT | PRICE | ||||||
WIDGET | $118 | ||||||
GIZMO | $88 | ||||||
TRINKET | $37 | ||||||
THINGAMAJIG | $18 | ||||||
DOODAD | $60 | ||||||
TCHOTCHKE | $999 | ||||||
↙ | ↓ | ↘ | |||||
($0-$49. 99) | ($50-$99.99) | ($100+) | |||||
PRODUCT | PRICE | PRODUCT | PRICE | PRODUCT | PRICE | ||
TRINKET | $37 | GIZMO | $88 | WIDGET | $118 | ||
THINGAMAJIG | $18 | DOODAD | $60 | TCHOTCHKE | $999 |
Основное преимущество этой модели состоит в том, что она относительно проста в реализации. Каждый сегмент содержит различный набор данных, но все они имеют идентичную схему, а также исходную базу данных. Код приложения просто читает, в какой диапазон попадают данные, и записывает их в соответствующий фрагмент.
С другой стороны, сегментирование по интервалу не защищает данные от неравномерного распределения, что приводит к вышеупомянутым hotspots. Вернемся к нашему примеру: даже если каждый шард содержит одинаковое количество данных, существует вероятность того, что конкретным продуктам будет уделено больше внимания, чем другим. И соответствующие сегменты, в свою очередь, получат непропорциональное количество операций чтения.
Фрагментация по каталогам
Для реализации этой модели необходимо создать и поддерживать справочную таблицу, которая с помощью ключа сегмента отслеживает, какие данные содержаться в том или ином сегменте. Короче говоря, справочная таблица – это таблица, которая содержит статический набор информации о том, где можно найти конкретные данные. Вот упрощенный пример сегментирования по каталогам:
PRE-SHARDED TABLE | ||||||
DELIVERY ZONE | FIRST NAME | LAST NAME | ||||
3 | DARCY | CLAY | ||||
1 | DENISE | LASALLE | ||||
2 | HIROSHI | YOSHIMURA | ||||
4 | KIRSTY | MACCOLL | S1 | |||
↓ | 1 | DENISE | LASALLE | |||
DELIVERY ZONE | SHARD ID | → | → | S2 | ||
1 | S1 | 2 | HIROSHI | YOSHIMURA | ||
2 | S2 | |||||
3 | S3 | S3 | ||||
4 | S4 | 3 | DARCY | CLAY | ||
S4 | ||||||
4 | KIRSTY | MACCOLL |
Здесь столбец Delivery Zone определяется как ключ сегмента. Данные от ключа сегмента записываются в справочную таблицу вместе с тем сегментом, в который должна быть записана каждая соответствующая строка. Это похоже на сегментирование по интервалам, но вместо определения диапазона, в который попадают данные, каждый ключ привязывается к своему определенному сегменту. Сегментирование по каталогам лучше, чем сегментирование по интервалам в тех случаях, когда ключ сегмента имеет низкую мощность связи, хранить диапазон ключей для сегмента не имеет смысла. Обратите внимание, что эта модель также отличается от сегментирования по ключам, поскольку она не обрабатывает ключ сегмента с помощью хеш-функции; она просто проверяет ключ по таблице, чтобы увидеть, куда записать данные.
Основное преимущество фрагментации по каталогам – это гибкость. Архитектура сегментирования по интервалам ограничивает пользователей диапазонами значений, а архитектура по ключам – фиксированной хеш-функцией, которую, как упоминалось ранее, впоследствии может быть чрезвычайно трудно изменить. Сегментирование по каталогам позволяет использовать любую систему или алгоритм для ввода данных в сегменты, и с помощью этого подхода сравнительно легко динамически добавлять фрагменты.
Сегментирование по каталогам является наиболее гибким из рассмотренных здесь методов, но необходимость подключаться к таблице перед каждым запросом или записью может отрицательно повлиять на производительность приложения. Кроме того, справочная таблица может стать единой точкой отказа: если она повреждена или иным образом вышла из строя, это повлияет на способность записывать или извлекать данные.
Когда нужно фрагментировать базу данных?
Вопрос о том, следует ли фрагментировать базу данных, почти всегда является предметом споров. Некоторые считают, что фрагментация баз данных, которые достигли определенного размера, неизбежной; для других это лишняя головная боль, которой следует избегать до тех пор, пока не возникнет критической необходимости.
Поскольку фрагментация – непростая процедура, она обычно выполняется только при работе с очень большими объемами данных. Вот несколько распространенных сценариев, в которых будет полезно сегментировать данные:
- Объем данных приложения увеличивается и скоро превысит емкость хранилища одного узла базы данных.
- Объем операций записи или чтения в БД превосходит возможности отдельного узла или его реплик, что приводит к замедлению отклика или превышению времени ожидания.
- Требуемая приложением пропускная способность сети превышает пропускную способность, доступную для одного узла БД и любых реплик, что приводит к медленной обработке запросов и превышению времени ожидания.
Прежде чем сегментировать данные, вы должны рассмотреть все другие возможности для оптимизации вашей базы данных. Вот некоторые другие средства:
- Настройка удаленной БД. Если вы работаете с монолитным приложением, в котором все его компоненты находятся на одном сервере, вы можете повысить производительность своей базы данных, перенеся ее на отдельную машину. Это не так сложно, как фрагментация, поскольку таблицы базы данных остаются нетронутыми. К тому же, это решение позволяет выполнить вертикальное масштабирование базы данных отдельно от остальной инфраструктуры.
- Кэширование. Если производительность чтения вашего приложения оставляет желать лучшего, кэширование – это одна из стратегий, которая может улучшить ее. Кэширование подразумевает временное хранение данных, которые уже были запрошены в памяти, что позволяет гораздо быстрее получить к ним доступ позже.
- Создание одной или нескольких реплик чтения. Другая стратегия, которая может помочь улучшить производительность чтения, заключается в копировании данных с одного сервера базы данных (ведущего сервера) на один или несколько вторичных (ведомых) серверов. При этом каждая новая запись передается на ведущий сервер, затем копируется на ведомые серверы, а операции чтения производятся исключительно с ведомых серверов. Такое распределение операций чтения и записи защитит любую машину от чрезмерной нагрузки, предотвратит замедления и сбои. Обратите внимание, что создание реплик чтения требует больше вычислительных ресурсов и, следовательно, стоит больше денег.
- Переход на больший сервер. В большинстве случаев масштабирование ресурсов сервера БД требует меньше усилий, чем фрагментация. Как и в случае создания реплик чтения, более мощный сервер с большим количеством ресурсов, вероятно, будет стоить больше денег. Соответственно, вам следует менять сервер только в том случае, если это решение действительно окажется лучшим вариантом для вас.
Заключение
Сегментирование баз данных может быть отличным решением для тех, кто хочет масштабировать свою БД горизонтально. Тем не менее, эта процедура усложняет работу и создает больше потенциальных точек сбоя для вашего приложения. Фрагментация может быть необходима в отдельных ситуациях, но время и ресурсы, требуемые для создания и поддержки сегментированной архитектуры, могут перевесить преимущества этого метода.
Теперь вы имеете более четкое представление о плюсах и минусах фрагментации и можете принять более обоснованное решение о том, подходит ли вашему приложению подобная архитектура данных.
Tags: MongoDB, MySQL, PostgreSQL, SQL
Встроенные функции SQL Server с табличным значением
В этой серии статей мы найдем основы и распространенные сценарии использования встроенных функций с табличным значением, а также закрепим ваши знания на практических примерах.
- Примечание: Дополнительные сведения о функциях с табличным значением из нескольких операторов см. в статье о функциях с табличным значением из нескольких операторов в SQL Server
.
Сначала кратко поищем ответ на вопрос «Зачем использовать функции в SQL Server?» вопрос.
В процессе разработки базы данных SQL Server функции позволяют нам заключать коды в один исполняемый объект базы данных. Другими словами, функции позволяют применить идею инкапсуляции к кодам T-SQL. Таким образом, написанную функцию можно использовать многократно. Таким образом, мы не тратим время на написание одного и того же кода снова и снова, и в результате мы можем уменьшить повторение кода. Кроме того, использование функций SQL Server помогает уменьшить беспорядок в коде.
Описание
Простое определение табличной функции ( TVF ) можно сделать таким образом; определяемая пользователем функция, которая возвращает табличный тип данных, а также может принимать параметры. TVF можно использовать после предложения FROM в операторах SELECT , чтобы мы могли использовать их так же, как таблицу в запросах. Первое, что приходит нам на ум, это то, что , в чем основное отличие между видом ( Представления — это виртуальные объекты базы данных, которые извлекают данные из одной или нескольких таблиц) и TVF ? Представления не допускают параметризованного использования. Это существенное различие между представлениями и TVF . В следующих разделах мы подкрепим эти теоретические сведения практическими примерами от простого к сложному. TVF можно разделить на два типа. Это встроенный и мультиоператорный табличные функции . В этой статье мы уделим особое внимание встроенному.
Вы можете обратиться к этой статье, Встроенные функции SQL Server и пользовательские скалярные функции, чтобы получить знания о встроенных функциях и пользовательских скалярных функциях в SQL Server.
Примечание: Все примеры в этой статье будут использоваться с образцом базы данных Adventureworks и запросами, отформатированными в программе форматирования запросов SQL .
Создание встроенной табличной функции (iTVF)
iTVF не включает в свой синтаксис блок BEGIN / END , а оператор SELECT является выходом функций этого типа, и это лучшая деталь iTVF .
Следующий оператор T-SQL создает очень простой iTVF , и результатом этой функции будет таблица Product .
1 2 3 4 5 6 7 8 9 9 0003 10 11 | СОЗДАТЬ ФУНКЦИЮ [dbo].[udfGetProductList] (@SafetyStockLevel SMALLINT ) ТАБЛИЦА ВОЗВРАТА AS ВОЗВРАТ 9000 3 (ВЫБРАТЬ Product.ProductID, Product.Name, Product.Number ИЗ Производство.Продукт ГДЕ SafetyStockLevel >= @SafetyStockLevel) |
Теперь мы рассмотрим код построчно.
Функция CREATE udfGetProductList (@SafetyStockLevel SMALLINT) |
Приведенная выше часть кода определяет имя функции, имя параметров и типы данных функции. В частности, для нашей функции мы указываем только один параметр с именем @SafetyStockLevel и его тип данных SMALLINT .
ТАБЛИЦА ВОЗВРАТА |
Приведенная выше часть кода указывает, что функция вернет таблицу.
1 2 3 4 5 6 | ВОЗВРАТ (ВЫБРАТЬ Product.ProductID, Product.Name, Product.ProductNumber FROM Production.Product WHERE SafetyStockLevel >= @SafetyStockLevel) |
Приведенная выше часть кода возвращает такие данные, как ProductId, Name и ProductNumber из таблицы Product , для которых значение в столбце SafetyStockLevel равно или больше значения, переданного в параметре функции.
Мы можем узнать функцию udfGetProductList под Папка программируемости в SQL Server Management Studio.
Как вы можете видеть на изображении выше, SSMS также показывает информацию о параметрах iTVF .
Выполнение встроенной функции с табличным значением
С помощью следующего запроса мы можем выполнить TVF. Еще раз отметим, что набор результатов функции будет изменен в соответствии с параметром @SafetyStockLevel .
ВЫБЕРИТЕ * ИЗ dbo.udfGetProductList(100) |
В приведенном выше случае мы передали @SafetyStockLevel как 100, и функция udfGetProductList вернула набор результатов в соответствии с этим параметром. В приведенном ниже примере мы добавим предложение WHERE в запрос, чтобы мы могли применить его для фильтрации вывода функции.
ВЫБЕРИТЕ * ИЗ dbo.udfGetProductList( 100 ) ГДЕ Имя КАК ‘Chainring%’ |
В следующем примере мы будем использовать предложение JOIN с функцией udfGetProductList .
1 2 3 4 5 6 | ВЫБЕРИТЕ PUdfList.ProductNumber, PUdfList.Name, PCost.StandardCost FROM dbo.udfGetProductList( 100 ) AS PUdfList INNER JOIN Production.ProductC ostHistory AS PCost ON PUdfList.ProductId = PCost.ProductID ГДЕ PUdfList .Id продукта = 717 |
В приведенном выше случае мы объединили таблицу ProductCostHistory и udfGetProductList и добавили Столбец StandartCost в набор результатов из таблицы ProductCostHistory .
Использование параметра по умолчанию
Мы узнали, что встроенных табличных функций принимают параметры, и эти параметры должны быть переданы функциям для их выполнения. Однако мы можем объявить значения параметров по умолчанию для iTVF . Если мы хотим выполнить функцию со значением по умолчанию, мы должны установить значение по умолчанию, и мы можем установить это значение для функции с помощью ключевое слово ПО УМОЛЧАНИЮ . В следующем примере мы изменим функцию udfGetProductList и объявим новый параметр со значением по умолчанию. Таким образом, нам не нужно придавать какое-либо значение параметру. Только мы будем передавать ключевое слово DEFAULT вместо значения параметра.
1 2 3 4 5 6 7 8 9 9 0003 10 11 12 | ALTER FUNCTION [dbo].[udfGetProductList] (@SafetyStockLevel SMALLINT, @MFlag BIT=0 ) ТАБЛИЦА ВОЗВРАТА AS 9000 2 ВОЗВРАТ (ВЫБРАТЬ Product.ProductID, Product.Name, Product.ProductNumber FROM Production.Product WHERE SafetyStockLevel >= @SafetyStockLevel AND MakeFlag=@MFlag ) |
В приведенном выше сценарии использования мы добавили новый параметр в функцию udfGetProductList с именем @MFlag , и значение этого параметра по умолчанию указано как 0.
Теперь давайте узнаем, как выполнить функцию udfGetProductList с параметром по умолчанию. Следующий запрос показывает этот метод использования:
ВЫБЕРИТЕ * ИЗ dbo.udfGetProductList(100, ПО УМОЛЧАНИЮ) |
Как передать несколько параметров во встроенную функцию с табличным значением
В некоторых случаях нам нужно передать несколько значений параметров в iTVFs . Предположим, что команда разработчиков хочет передать в разработанную функцию несколько значений одного параметра. Чтобы реализовать подобный сценарий использования, мы должны создать определяемый пользователем табличный тип, потому что благодаря этим типам мы получаем возможность объявлять табличные параметры. Табличные параметры позволяют отправлять в функции несколько значений.
Создание пользовательского типа таблицы:
СОЗДАТЬ ТИП ProductNumberList КАК ТАБЛИЦУ
(
ProductNum nvarchar(25)
)
Добавление табличного значения в функцию udfGetProductList с оператором READONLY :
1
2
3
4
5
6
7
8
9
10
900 02 11
12
13
14
ALTER FUNCTION [dbo]. [udfGetProductList](
@SafetyStockLevel SMALLINT, @MFlag BIT= 0, @ProductList ProductNumberListList ТОЛЬКО ДЛЯ ЧТЕНИЯ)
ТАБЛИЦА ВОЗВРАТОВ
AS
RETURN
(ВЫБРАТЬ Product.ProductID, Product.Name , Product.ProductNumber
FROM Production.Product
WHERE SafetyStockLevel >= @SafetyStockLevel
AND MakeFlag = @MFlag
AND Product.ProductNumber IN
(
SELECT ProductNum
FROM @ProductList
))
Объявите переменную как параметр с табличным значением и заполните ее несколькими значениями параметров. Выполнить функцию.
ОБЪЯВИТЬ @TempProductList AS ProductNumberList
ВСТАВИТЬ В @TempProductList
ЗНАЧЕНИЯ (‘EC-R098’), (‘EC-T209’)
SELECT * FROM [dbo].[udfGetProductList](100,1, @TempProductList)
Заключение
В этой статье мы рассмотрели, почему мы должны использовать функции в SQL Server, а затем изучили сценарии использования встроенные табличные функции (iTVF) . Эти типы функций делают процесс разработки нашей базы данных более простым и модульным, а также помогают избежать повторного написания одного и того же кода.
- Автор
- Последние сообщения
Esat Erkec
Esat Erkec — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server.
Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.
Просмотреть все сообщения от Esat Erkec
Последние сообщения от Esat Erkec (посмотреть все)
sql server — встроенная табличная функция против встроенного sql
Задавать вопрос
спросил
Изменено
10 лет, 5 месяцев назад
Просмотрено
2к раз
Я наблюдаю странное поведение при попытке настроить встроенную функцию с табличным значением. В частности, если я возьму внутренности функции и с теми же параметрами, с которыми я бы вызвал функцию, я увижу прирост производительности примерно в 3 раза. Что-то вроде этого (значительно упрощенное):
создать функцию dbo.FooToDate(@ToDate date) таблица возвратов как возвращаться ( выберите f.a, f.b, b.c от dbo.[Foo] как f левое соединение dbo.Bar как b на f.fId = b.fId где f.ToDate >= @ToDate ) идти объявить дату @ToDate = '2012-12-21' --запускается примерно через 4 секунды выберите f.a, f.b, b.c от dbo.[Foo] как f левое соединение dbo.Bar как b на f.fId = b.fId где f.ToDate >= @ToDate --запускается примерно через 12 секунд выберите a, b, c из dbo.FooToDate(@ToDate)
Когда я смотрю на планы запросов для моей реальной ситуации, общая форма совсем другая. Мое понимание встроенных TVF состояло в том, что оптимизатор мог развернуть функцию, поэтому такое поведение для меня несколько странно. Есть ли простое (или не очень простое) объяснение?
- sql-сервер
- sql-сервер-2008-r2
9
В SQL Server 2000 и более ранних версиях мы все привыкли использовать чередующиеся формы логически эквивалентных запросов в качестве средства настройки запросов.
С тех пор оптимизатор настолько улучшился, что мы удивляемся, если два разных, но логически эквивалентных запроса приводят к разным планам выполнения.
Однако пространство поиска всех возможных планов выполнения для данного запроса имеет значительный размер даже для запросов средней сложности, так что оптимизатор не может просмотреть их все. Оптимизатор использует сочетание эвристики, правил и информации о данных, чтобы определить, что искать дальше после того, как он нашел первый подходящий план. Если запрос достаточно сложен, даже тривиальное изменение, подобное этому, может направить оптимизатор на совершенно разные пути, что в конечном итоге приведет к таким большим различиям во времени выполнения. (Посмотрите мой пост о подсказках присоединения для более подробного обсуждения этого: http://sqlity.net/en/1443/a-join-a-day-join-hints/
Часто мы можем исправить подобную ситуацию, убедившись, что информация, на которой оптимизатор основывает свои решения, является максимально точной.