Отправляет email-рассылки с помощью сервиса Sendsay

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Март 2006   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней ни разу не выходила


Сайт рассылки: http://www.sql-ex.ru
Открыта: 15-09-2004

Автор
Serge I

Статистика

2.594 подписчиков
0 за неделю
←   Все выпуски   →

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 80


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 80 (25 марта 2006 г.)

http://www.sql-ex.ru

Новым посетителям сайта

Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 58-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.

Новости сайта

§ Улучшена навигация по форуму. Новый алгоритм оказался критичен к размерам сообщения и работал некорректно, когда число символов было близко к 8000 - размеру столбца в БД для хранения текста сообщения. В результате пришлось поставить ограничение в 7950 символов на размер сообщения. При публикации длинных сообщений (запросов), превышающих данный размер, разбивайте его на два.

§ Почистил базу участников, удалив случайных посетителей. Использовал уже опробированный критерий:
1. Последнее посещение было более 1 года назад.
2. Число решенных задач на SELECT не превышает 15.
Под сокращение попало около 200 человек.

§ Десятка принимает уже знакомые очертания. На прошедшей неделе 138 задачу решил burakov58 (задач 137, время 17.348). Теперь ему ничего не мешает на какое-то время занять третью позицию в рейтинге, т.к. для этого осталось решить не сложную недавно добавленную 113 задачу.

§ В сотне появились новые участники, которые могут вмешаться в спор за лидерство:
OlegE (104, 1.604) и
Testo (97, 1.007)

§ В одном шаге до финиша остановились:
dimzv (137, 3.278)
Julia_M (137, 68.931)

§ Сохранил шансы на попадание в десятку:
niko (134, 28.265)

§ Продолжили свое восхождение к вершине:
Sp999 (131, 37.884)
xax (121, 63.568)
f.nietzsche (109, 18.205)
Eugeniе (102, 26.380)
arm (97, 10.045)

§ Число подписчиков - 3004

Число участников рейтинга - 5078

Число участников второго этапа - 517

Сертифицировано на сайте - 55

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 LastSolved LastVisit
1 Кувалкин К.С. (Cyrilus) 138 113 20 320 484 5.460 23 Mar 2006 24 Mar 2006
2 Kamaev V.M. (Heromantor) 138 138 20 320 128 9.066 14 Mar 2006 19 Mar 2006
3 Тарасов Д.Б. (Gavrila) 138 113 20 320 208 19.424 22 Mar 2006 24 Mar 2006
4 Голубин Р.С. (Roman S. Golubin) 138 138 20 320 210 20.835 16 Mar 2006 24 Mar 2006
5 Бураков С.Г. (burakov58) 137 138 20 318 416 17.348 21 Mar 2006 23 Mar 2006
6 Валуев Д.И. (Fiolent) 137 138 20 318 925 47.930 17 Mar 2006 24 Mar 2006
7 Войнов П.Е. (pаparome) 137 137 20 316 203 1.769 15 Mar 2006 24 Mar 2006
8 Зверев Д.Л. (dimzv) 137 113 20 316 869 3.278 22 Mar 2006 24 Mar 2006
9 Юлдашев М.Р. (Snowbear) 137 137 20 316 239 3.448 14 Mar 2006 24 Mar 2006
10 Абашин П.И. (Dizil) 137 137 20 316 203 3.776 15 Mar 2006 24 Mar 2006
11 Самохвалов В. (ValdemarES) 137 137 20 316 117 7.788 14 Mar 2006 15 Mar 2006
12 Крижевич С.А. (yaff) 137 137 20 316 259 14.801 16 Mar 2006 22 Mar 2006
13 Иванов А.Н. (Goapsy) 137 137 20 316 128 16.076 18 Mar 2006 18 Mar 2006
14 Зырин В.Е. (Vezyr) 137 137 20 316 63 20.618 17 Mar 2006 21 Mar 2006
15 Страшников А.С. (EffEct) 137 137 20 316 305 59.972 16 Mar 2006 24 Mar 2006
16 >Матвеева Ю.Б. (Julia_M) 137 113 20 316 137 68.931 24 Mar 2006 24 Mar 2006
17 Духин А. (Shark) 135 137 20 311 249 2.785 17 Mar 2006 21 Mar 2006
18 Носков Н.В. (niko2) 134 52 20 309 260 8.265 23 Mar 2006 23 Mar 2006
19 Леденев С.А. (Shurgenz) 134 137 20 309 573 11.574 14 Mar 2006 22 Mar 2006
20 Держальцев В.А. (MadVet) 134 137 20 309 334 18.469 16 Mar 2006 16 Mar 2006

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 mur (lepton) 50 50 88 17 105 591 24 Mar 2006
2 >Yakimenko V. (janush) 56 56 103 1 104 604 24 Mar 2006
3 >Вялухин С.Г. (SERGEY_VS1) 35 50 70 22 92 513 24 Mar 2006
4 Фаст А. (LyXX) 45 45 74 9 83 891 21 Mar 2006
5 Tompkins A. (ASquared) 39 39 66 9 75 1018 23 Mar 2006
6 >Голодов И.Н. (cabutar) 35 35 54 9 63 1254 24 Mar 2006
7 >Иваненко В.Н. (ivanych) 33 33 58 0 58 1393 24 Mar 2006
8 >Ivanov (vanio) 29 29 52 0 52 1567 24 Mar 2006
9 Мец И.В. (MetzIlya) 24 50 46 4 50 537 23 Mar 2006
10 Гонтарев А. (Dzun) 28 28 49 1 50 1613 23 Mar 2006
11 >Игнатьев А.А. (Freinchman) 28 28 49 0 49 1654 24 Mar 2006
12 rawat R. (rohit) 29 29 45 3 48 1706 23 Mar 2006
13 Diyan A. (diyan) 27 27 46 0 46 1742 24 Mar 2006
14 Korotkov V.V. (цепеш) 24 52 43 0 43 789 20 Mar 2006
15 >Бродский Л.В. (Leonid2004) 25 25 42 0 42 1852 24 Mar 2006
16 >Галкина Н. (natalya) 25 25 40 0 40 1928 24 Mar 2006
17 Федченко В.Н. (Bless) 20 58 38 0 38 544 23 Mar 2006
18 >Гриценко Ю.А. (Pal) 26 26 38 0 38 1991 24 Mar 2006
19 >Готов (Готов) 23 23 35 3 38 1996 24 Mar 2006
20 >Урусов А.С. (Xardas) 24 24 37 0 37 2039 24 Mar 2006
21 >Belyavskiy P.V. (bof) 24 24 37 0 37 2058 24 Mar 2006
22 >Елоев О.К. (OlegE) 16 104 35 0 35 78 24 Mar 2006
23 >Лопатин И. (Иван) 14 31 35 0 35 1467 24 Mar 2006
24 Kimask (stkim) 19 30 26 9 35 1726 24 Mar 2006
25 Татаринов А.П. (R00T) 15 71 32 2 34 174 23 Mar 2006
26 Левина (tali) 14 55 31 3 34 605 24 Mar 2006
27 Досанов (Yerlan) 18 40 34 0 34 1295 23 Mar 2006
28 Sudakov Y.N. (Sudakov) 13 56 27 4 31 535 21 Mar 2006
29 kosi (marcin) 22 22 31 0 31 2388 22 Mar 2006
30 Туленков Р.В. (Туленков) 25 25 26 5 31 2416 23 Mar 2006
31 Misakyan (Stakan) 18 18 31 0 31 2436 23 Mar 2006
32 >Ильин А.С. (Sasha_Century) 24 24 31 0 31 2437 24 Mar 2006
33 >Фомичев А.В. (Fomich) 6 47 16 14 30 700 24 Mar 2006

Изучаем SQL

Использование хэш-ключей вместо строковых индексов

Arthur Fuller (оригинал: Intelligent Database Design Using Hash Keys)
Перевод Моисеенко С.И.

Вашему приложению может потребоваться индекс на основе длинной строки символов или, что еще хуже, конкатенации двух строк или строки и одного-двух целых чисел. Для небольшой таблицы вы можете не заметить какого-либо отрицательного влияния такого индекса. Но если предположить, что рассматриваемая таблица содержит 50 миллионов записей? Теперь вы не сможете не заметить воздействия, которое скажется как на требованиях к хранению, так и к производительности поиска.

Однако вам не обязательно так поступать. Есть очень простая альтернатива, использующая то, что еще известно под названием хэш-блоков или хэш-ключей.

Что такое хэширование?

Говоря коротко, хэширование - это целочисленный результат алгоритма (известного как хэш-функция), применяемого к заданной строке. Вы передаете в алгоритм строку, а на выходе получаете целое число. Если Вы используете эффективную хэш-функцию, то вероятность того, что две различных строки дадут одно и то же значение хэш-функции, будет невелика. Такой случай известен под названием коллизии хэширования. Предположим, что Вы применили к этой статье алгоритм хэширования, затем изменили один символ в статье и повторили алгоритм: он возвратил бы другое целое число.

Хэш-ключи в проекте базы данных

Как бы теперь грамотно применить хэш-ключи в наших проектах баз данных? Предположим, что интересующая нас таблица имеет следующие столбцы:

Имя столбца       Тип Данных
Name                     Varchar(50)
GroupName          Varchar(50)

Составной индекс на обоих столбцах потреблял бы 50 + 50 символов на строку. Учитывая, что у нас 50 миллионов строк, это - проблема. Хэш-ключ, построенный на тех же двух столбцах будет значительно меньше (4 байта на строку). Еще лучше, что мы не должны хранить сами хэш-ключи - или более точно, мы должны сохранить их только однажды. Мы создаем вычисляемый столбец, формула которого дает нам хэш-ключ этих двух столбцов. Теперь мы индексируем строку по хэш-ключу и обходимся без индекса на двух упомянутых выше столбцах.

Основной процесс состоит в следующем:
Пользователь (либо человек, либо приложение) запрашивает некоторые значения.
Эти значения теперь преобразовываются в хэш-ключ.
Механизм базы данных выполняет поиск в индексе, построенном на хэш-столбце, возвращая требуемую строку, или небольшой набор соответствующих строк.

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

Алгоритмы хэширования, использующие функцию Checksum

Есть несколько доступных алгоритмов, простейший из которых встроен в SQL Server в форме функции Checksum. Например, следующий запрос демонстрирует получение хэш-ключа для любого заданного значения или комбинации значений:

USE AdventureWorks
SELECT Name, GroupName, Checksum(Name,GroupName) AS HashKey
FROM Adventureworks.HumanResources.Department
ORDER BY HashKey

Этот код приводит к следующему результату (для краткости показаны только 10 строк):

Name                     GroupName                      Hashkey
Tool Design         Research and Development      -2142514043
Production         Manufacturing     -2110292704
Shipping and Receiving         Inventory Management      -1405505115
Purchasing               Inventory Management      -1264922199
Document Control              Quality Assurance      -922796840
Information Services          Executive General and Administration     -904518583
Quality Assurance          Quality Assurance      -846578145
Sales                  Sales and Marketing     -493399545
Production Control      Manufacturing      -216183716
Marketing             Sales and Marketing      -150901473

Имеется множество вариантов того, как создавать хэш-ключ. Вы могли бы применить срабатывание триггера на вставку или использовать хранимую процедуру, чтобы создать хэш-ключ сразу, как только получены необходимые данные, или даже выполнить запрос UPDATE, который создаст хэш-ключи и заполнит хэш-столбец задним числом (чтобы Вы могли применить этот метод к таблицам, которые уже содержат миллионы строк). Как было показано выше, я предпочитаю решение, состоящее в том, чтобы "хранить" хэш-ключи в вычисляемом столбце, который потом индексируется. При этом индекс содержит хэш-ключи, но сама таблица - нет.

Используя этот метод, Вы могли бы решить проблему следующим образом, предполагая, что целью поиска являются значения в Name и GroupName:

CREATE PROCEDURE DemoHash
(
@Name Varchar(50),
@GroupName Varchar(50)
)
AS
-- USE AdventureWorks
DECLARE @id as int
SET @id = Checksum(@Name,@GroupName)
SELECT * FROM Adventureworks.HumanResources.Department
WHERE HashKey = @id
AND Name = @Name AND GroupName = @GroupName

Заключение

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

Полезная информация

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"
В избранное