MCP
Показаны сообщения с ярлыком sql. Показать все сообщения
Показаны сообщения с ярлыком sql. Показать все сообщения

четверг, 7 апреля 2016 г.

Производительность SQL при поиске по ключу в зависимости от типа

Последнее время в проектах для SQL-таблиц в качестве идентификатора мы используем гуиды. Ибо уникально, не нужны идентити, и можно идшники генерить на клиенте (причём сразу для всего дерева объектов), а потом вставлять в базу (при этом, хоть асинхронно).

Но вот тут у меня закралась мысль, а насколько эффективно SQL в дальнейшем работает с данными типами? И не будут ли голые инты быстрее во всяких селектах и джойнах, да так, что перекроют с лихвой все недостатки при вставке (ведь в основном все селектят, это обычно важнее, чем вставка).

Тестировать можно долго и по-разному, но для простоты, я ограничился следующими условиями:

  • Таблицы с Id и Value (чтобы совсем грустные не были
  • Id типов int, bigint и guid (т.е. 4, 8 и 16 байт, меньшие типы несерьёзно тестировать)
  • Id - кластерный индекс и первичный ключ
  • Заполнение по Identity и по рандому (чтобы проверить, влияет ли более разреженный индекс на результаты). Для гуидов идентити эмулировалось вручную, генерацией "правильных" данных 
  • Заранее вставил 1000000 записей в каждую таблицу
  • Селекчу много раз определённую запись (глупо, но SQL ведь особых чудес ловкости отваги проявить тут не сможет, впрочем, попробовал разные записи, результат похож)
В качестве подходящей незагруженной железяки (чтобы не влияли на результаты) оказался сервер с SSD, достаточным количеством памяти, но с SQL 2008. Не очень свежо, но не думаю, что что-то менялось на подобных запросах. Впрочем, позднее перетестирую, и если результаты изменятся — обновлю пост.

В общем, замерил результаты и.... они оказались практически идентичными. Конечно, разница есть в пределах 3%, но это несущественно. Впрочем, смотрите картинку сами:
Вам может показаться, что разница существенная и в пользу инта, давайте немного поправим масштаб картинки:


Так уже понятнее становится, что разница настолько мала, что хотя бы в данной ситуации типом можно пренебречь и выбирать тот, который больше нравится.

Но дальше я решил разнообразить тесты и сделать точно такие же таблицы, но без ключей (не повторять самостоятельно, опасно для жизни!). Результаты оказались слегка обескураживающими
Инт тут не просто в пролёте, он в почти в 3 раза медленнее гуида! А бигинт самый быстрый (что, в принципе не так уж и странно, но не в случае такого провала с интом).
Вначале я думал, что табличке с интами не повезло и она нарвалась на длинный фулскан, пробовал селектить разные записи — результат идентичный. В чём дело, я не понимаю. Возможно проблемы SQL сервера, а возможно так и задумано, тут я не специалист. В общем, надо доисследовать, но ситуация очень неординарная. Бигинты круче интов в несколько раз...

Ну и напоследок размер таблиц. Понятно, что это всё сильно depends и от данных и от наполненности индекса, но общее представление можно поиметь:


Тут всё просто и логично, снос кластерного индекса экономит 4-5 мегабайт на миллионе записей, бигинт больше инта где-то на 4 мегабайта, гуид больше бигинта на 8-9 мегабайт в зависимости от наличия/отсутствия индекса.

Update: Обновил SQL Server с 2008 до 2014. Результаты: Поиск по индексу стал где-то на 40% быстрее (смысл обновляться оказывается имеется!), Поиск по таблицам без первичного ключа: с интами ничего не изменилось, гуиды стали быстрее на 30%, т.. практически сравнялись с бигнитами. Т.е. проблемы те же самые на месте, но гуиды стали ещё более привлекательнее. Картинка с данными:

PS: OLTP-таблицы дают офигенный прирост скорости для таблиц без индекса, но OLTP для таблиц с индексом даёт те же результаты в моём случае. В общем, OLTP, это предмет отдельного поста, просто, раз обновился, решил глянуть.

среда, 5 августа 2009 г.

Как заполнить поле в базе рандомными значениями

Небольшая напоминалка для себя на будущее, возможно кому и пригодится.

Для тестов иногда бывает необходимо заполнить поле в базе какими-нибудь левыми значениями. Проблема в том, что обычная функция RAND() при апдейте возвращает одно и тоже значение, ибо оптимизатор её оптимизирует. Собственно, из этого есть выход, заключается он в том, что в RAND() надо передавать рандомный seed в качестве параметра. Вариантов несколько, я использую такой:

UPDATE Table SET Field=RAND(abs(convert(varbinary, newid()) % 65536))*1000 

1000 — это максимальное значение в поле (точнее 999). Т.к. точность не требуется, особо над распределением не заморачивался. Пользуйтесь, если вдруг пригодится.