(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

MS SQL: генерация псевдослучайных данных с использованием newID(). Возможности и подводные камни

Источник: habrahabr
gleb_l

Известно, что встроенная функция newID() широко используется разработчиками не только по прямому назначению - то есть для генерации уникальных первичных ключей, но и в качестве средства для генерации массивов псевдослучайных данных.
В составе встроенных функций, newID() фактически единственная, которая не только non-deterministic, но можно сказать и "super-non-deterministic", т.к. в отличие от всех остальных, она способна выдавать новое значение для каждой новой строки, а не одно и то же для всего батча - что делает ее чрезвычайно полезной для подобной массовой генерации. Кроме newID() этим свойством обладает еще newSequentialID(), однако ее использование где-либо, кроме как в задании дефолтного значения колонок типа uniqueidentifier, запрещено.
За примерами далеко ходить не надо - ниже код:

SELECT TOP 100 ABS(CHECKSUM(NEWID())) % 1000 FROM sysobjects A CROSS JOIN sysobjects B
или вот этот (если кажется, что checksum - трудоемкая операция):

SELECT TOP 100 ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 FROM sysobjects A CROSS JOIN sysobjects B
Сгенерирует нам таблицу из 100 случайных целых чисел в диапазоне от 0 до 999.

Для плавающих чисел можно использовать свойство функции rand() инициализировать генератор целым числом:

SELECT TOP 100 RAND(CHECKSUM(NEWID())) FROM sysobjects A CROSS JOIN sysobjects B
В данном случае rand() используется по сути просто как преобразователь диапазона int32 в диапазон [0..1). Статистическая проверка качества распределения этим методом на количестве записей порядка миллиона показывает, что оно не уступает стандартному использованию rand(), инициализированному один раз, и далее используемому в цикле. Поэтому - можете смело использовать.

Еще один интересный вариант - генерация нормально-распределенных данных. Здесь будем использовать метод Бокса-Мюллера:

SELECT TOP 1000 COS(2 * PI() * RAND(BINARY_CHECKSUM(NEWID()))) * SQRT(-2 * LOG(RAND(BINARY_CHECKSUM(NEWID())))) FROM sysobjects A CROSS JOIN sysobjects B CROSS JOIN sysobjects C
Желающие могут проверить, что сгенерированное распределение очень близко к нормальному, построив график.

Все это хорошо работает и позволяет очень быстро сгенерировать хоть десяток миллионов записей, не используя решения "в лоб" типа циклов, курсоров, или даже вставки записей по одной в базу из слоя приложения. Нужно только убедиться, что таблицы, которые вы используете как источник строк, имеют достаточную емкость, и либо увеличить количество CROSS JOIN'ов, либо использовать табличные переменные с нужным количеством строк в качестве источника.

Однако, тема не только об этом. В подавляющем большинстве случаев сгенерированные строки материализуются, то есть вставляются в постоянную или временную таблицу, либо в табличную переменную. Если это так, то дальше можно не читать - материализованные данные будут работать отлично. Однако, встречаются случаи, когда вышеуказанные стейтменты используются в подзапросах. И вот здесь появляются труднообъяснимые на первый взгляд особенности поведения SQL engine. Рассмотрим их на примерах, а затем попытаемся проанализировать, почему так происходит, и как с этим бороться:

Для начала просто напишем statement с newID() в subquery и запустим его несколько раз в цикле:

declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ) SUBQ set @c = @c + 1 end
Код работает ожидаемо - выдает 5 резалтсетов, в каждом строго одна запись с числом в диапазоне от 0 до 4. Скриншота результатов я не привожу - когда и так все в порядке, смысла в них мало.

Теперь интереснее. Пробуем поджойнить результат из SUBQ на какую-нибудь другую таблицу. Ее можно создать, а можно поджойнить subquery на subquery - результат от этого не изменится. Пишем:

declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ) SUBQ INNER JOIN ( SELECT 0 AS VAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NUM ON SUBQ.RNDIDX = NUM.VAL set @c = @c + 1 end
Смоторим на скриншот результата выполнения - и медленно сползаем под стул - количество строк в каждом резалтсете не равно строго 1. Где-то пусто (это еще можно хоть как-то объяснить - не сработал INNER JOIN из-за выхода RNDIDX из диапазона [0..4] (что само по себе невероятно!)), а где-то - больше одной (!) записи.

image

Теперь делаем невинное изменение - меняем INNER на LEFT:

declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ) SUBQ LEFT JOIN ( SELECT 0 AS VAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NUM ON SUBQ.RNDIDX = NUM.VAL set @c = @c + 1 end
Выполняем - все стало работать правильно (!) - проверерьте плз сами, скриншота для правильной работы я не делал. Заметьте, что поскольку для любого значения RNDIDX из диапазона [0..4], которое способно выдать сабквери SUBQ, всегда есть значение VAL из сабквери NUM, то с точки зрения логики результат LEFT и INNER JOIN должен быть одинаков. Однако по факту это не так!

Еще один тест - возвращаем INNER, но добавляем TOP / ORDER BY в первый сабквери. Зачем - об этом позже, давайте просто попробуем:

declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT TOP 1 ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ORDER BY RNDIDX ) SUBQ INNER JOIN ( SELECT 0 AS VAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NUM ON SUBQ.RNDIDX = NUM.VAL set @c = @c + 1 end
Все опять работает правильно! Мистика!

Погуглив, выясняем, что с подобным поведением периодически сталкиваются SQL-разработчики со всего мира - примеры здесь, или здесь

Люди предполагают, что материализация subquery помогает. Действительно, если переписать пример, выбрав сначала записи в явном виде во временную таблицу, а затем только поджойнив, все работает нормально. Почему же на нормальную работу влияет замена INNER на LEFT, или добавление TOP / ORDER BY там, где это не нужно? Все потому же - в одном случае присутствует материализация результатов subquery, в другом - нет. Нагляднее разницу может показать анализ плана более развернутого случая, например вот этого:

DECLARE @B TABLE (VAL INT) INSERT INTO @B VALUES (0), (1), (2), (3), (4) SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM @B ) SUBQ INNER JOIN @B B ON SUBQ.RNDIDX = B.VAL Смотрим естественно, только выборку (query 2), заполнение таблицы @B нам ни к чему: image

Мы видим, что запрос сращивает два потока строк до вычисления значения колонки, зависящей от newID(). Это может происходить потому, что SQL engine считает, что значение, возвращаемое newID(), хоть и non-deterministic, но не изменяется в течение всего батча. Однако, это не так - и скорее всего поэтому запрос работает неправильно. Теперь меняем INNER на LEFT, и смотрим план:

image

Ага, LEFT JOIN заставил SQL engine выполнить Compute Scalar перед объединением потоков, поэтому наш запрос стал работать правильно.

И наконец, проверим версию с добавлением TOP / ORDER BY:

image

Собственно, диагноз ясен. MS SQL не учитывает особенности newID(), и соответственно, неправильно строит планы, полагаясь на константное значение, возвращаемое функцией в скоупе батча. На эту особенность есть воркэраунд - заставлять SQL engine любыми способами материализовать выборку, перед тем как ее использовать в зависимых запросах. Каким способом вы будете материализовать - дело ваше, однако лучше всего, наверное, использовать табличные переменные, особенно если размер подвыборки невелик. Иначе результат, мягко говоря, не 100% гарантирован; кроме того, нет никакой гарантии, что однажды вы сами, или кто-нибудь другой не отревьюит код, выкинув "ненужные" TOP / ORDER BY или мудро заменив LEFT на INNER.

Собственно, все. Удачного SQL-программирования!

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Microsoft »
Обсудить материал в конференции Дизайн, графика, обработка изображений »
Написать редактору 
 Рекомендовать » Дата публикации: 05.12.2013 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Microsoft Windows Professional 10, Электронный ключ
Microsoft Visual Studio Professional w/MSDN AllLng License/Software Assurance Pack OLP 1 License No Level Qualified
Microsoft Office 365 Бизнес премиум. Подписка на 1 рабочее место на 1 год
Microsoft System Center Standard Core Sngl License/Software Assurance Pack OLP 2Licenses NoLevel CoreLic Qualified
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Новости мира 3D-ускорителей
Краткие описания программ и ссылки на них
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Windows 10 загружен процессор (2)
Добрый день! На рабочем ПК Windows 10, компьютер тормозит, в диспетчере задач выдает что диск...
 
Отличается ли ДрифтКазино от беттинга? (8)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
ErWin to Access Relation Error (2)
Всем привет! ErWin при попытке генерации в Ассеss выдаёт: ERwinDatabase.Relations.Append...
 
Помощь по MS Access (331)
Доброе время суток. Случайно оказался на этом сайте, искал статьи по OLAP. Вижу, что...
 
Смена типа уровня модели (1)
Здравствуйте. При запуске программы выбрал уровень "Логический" вместо "Логический и...
 
 
 



    
rambler's top100 Rambler's Top100