Отправляет 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


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


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

Автор
Serge I

Статистика

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

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


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

http://www.sql-ex.ru

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

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

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

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

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

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

§ Обнаружилась прореха в проверке задачи 87. Соответствующие проверочные данные добавлены.
Проверьте свои решения.

§ Colan обратил внимание, что число участвующих в рейтинге не совпадает показаниями на кнопке-счетчике. Причина заключалась в том, что старый код учитывал только тех, кто решал задачи на SELECT.
Теперь везде должно считаться одинаково - SELECT + DML.

§ Разделяя точку зрения KERBEROS о необъективности рейтинга по оптимизации при использовании хинтов типа OPTION (FAST 1) и WITH(FASTFIRSTROW), я исключил содержащие их решения из рейтинга. Собственно, для исключения нужно пересчитать результаты для подозрительно быстрых решений на странице performance.php.
Мотивировка такая. В BOL буквально написано:

FAST N
Указание оптимизировать запрос для наискорейшего получения первых N строк (неотрицательное целое). После возвращения первых N строк продолжается выполнение запроса для получения полного результирующего набора.

Фактически, этот хинт заставляет оптимизатор использовать некластеризованный индекс, соответствующий столбцам, перечисленным в предложении ORDER BY (если такой найдется).
И все бы ничего, если бы оценка плана не строилась исходя из получения только первой (в случае FAST 1) строки, а не всего набора.
Выполните, например, следующий скрипт:

set showplan_all on
go
select model from product
go
select model from product option(fast 1)
go
set showplan_all off

Поскольку на первичном ключе (model) построен некластеризованный индекс, то он и будет использоваться в обоих случаях. Т.е. планы двух запросов оказались идентичны, однако в первом случае я получил оценку стоимости 3.7674598E-2, а во втором - 6.4082001E-3.
Мне очень не хочется, чтобы вся оптимизация решений на сайте сводилась бы к дописыванию OPTION (FAST 1) в каждом запросе-решении.

 

§ 141 задача сдалась. Тестирование выполнили KERBEROS и Roman S. Golubin, которые дали задаче высокую оценку. В итоге 1 и 3 место в рейтинге, соответственно.
В результате тестирования убрали лишнее условие в формулировке.

§ Отметился на третьем этапе Cyrilus, решивший 139 задачу. Пока 8 место с результатом, оставляющим шансы на самое высокое место.
С результатом 24.992 подошел к третьему этапу =Maxim=, решивший на этой неделе "культовую" 138 задачу.

§ Продолжили свое восхождение к вершине:
Johnny (задач 116, время 170.961)
Goga_3040 (128, 53.831)
Дайнин (111, 80.588)
Fencer (111, 116.817)
snikol (107, 73.944)

§ Вошли в сотню:
Tunin (109, 17.032)
Dark_Elf (106, 18.068)
maar (105, 34.666)

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 >Агапов В. (KERBEROS) 141 141 20 333 79 6.173 1.262 11 10 Nov 2006 10 Nov 2006
2 Карасёва Н.В. (vlksm) 141 141 20 333 176 27.838 2.710 11 29 Oct 2006 10 Nov 2006
3 Голубин Р.С. (Roman S. Golubin) 141 141 20 333 447 54.977 33.802 11 08 Nov 2006 10 Nov 2006
4 Мурашкин И.В. (lepton) 140 140 20 329 199 13.712 3.581 7 08 Oct 2006 10 Nov 2006
5 Васьков Е.В. (Johan) 140 140 20 329 85 12.705 11.402 7 12 Oct 2006 10 Nov 2006
6 Войнов П.Е. (pаparome) 139 140 20 326 407 2.687 .000 4 05 Oct 2006 10 Nov 2006
7 Юлдашев М.Р. (Snowbear) 139 139 20 325 441 4.106 .000 3 02 Oct 2006 07 Nov 2006
8 Кувалкин К.С. (Cyrilus) 139 139 20 325 712 11.063 1.171 3 06 Nov 2006 10 Nov 2006
9 Держальцев В.А. (MadVet) 139 139 20 325 540 34.249 3.085 3 08 Oct 2006 19 Oct 2006
10 Тарасов Д.Б. (Gavrila) 138 87 20 322 432 19.721 .000 0 01 Nov 2006 10 Nov 2006
11 Утёнков М.Н. (=Maxim=) 138 138 20 322 190 24.992 .000 0 09 Nov 2006 10 Nov 2006
12 Валуев Д.И. (Fiolent) 138 138 20 322 1125 54.696 .000 0 03 Oct 2006 10 Nov 2006
13 Slobodcicov A.N. (Testo) 137 138 20 320 337 7.171 .000 0 25 Aug 2006 10 Nov 2006
14 Иванов А.Н. (Goapsy) 137 138 20 320 270 18.911 .000 0 07 Aug 2006 09 Oct 2006
15 Зверев Д.Л. (dimzv) 137 137 20 318 1070 3.663 .000 0 09 Oct 2006 09 Oct 2006
16 Абашин П.И. (Dizil) 137 107 20 318 420 3.961 .000 0 18 Oct 2006 18 Oct 2006
17 Палий С.А. (PS_Sergey) 137 137 20 318 166 10.215 .000 0 16 Oct 2006 07 Nov 2006
18 Крижевич С.А. (yaff) 137 107 20 318 481 14.998 .000 0 24 Oct 2006 25 Oct 2006
19 Мальцев А.В. (Палкин) 137 137 20 318 56 15.229 .000 0 12 Oct 2006 05 Nov 2006
20 Вязовецков А.С. (alex_v) 137 137 20 318 141 22.788 .000 0 20 Oct 2006 20 Oct 2006

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Вертоградский А.В. (vav) 60 60 110 32 142 408 09 Nov 2006
2 >Ульянов В.В. (EvilM@niac) 48 48 83 19 102 938 10 Nov 2006
3 >Sleptsov S. (Sergey Sleptsov) 50 50 90 0 90 1180 10 Nov 2006
4 Фесик А.М. (AlexF) 46 46 83 0 83 1310 09 Nov 2006
5 >Хамето С.Н. (Rabbit) 26 56 61 20 81 605 10 Nov 2006
6 Egorova I. (Lunka) 44 44 71 9 80 1370 09 Nov 2006
7 >Балакина Н.А. (nadush) 42 42 73 0 73 1578 10 Nov 2006
8 Князев И.Н. (gareg) 18 41 35 23 58 1149 09 Nov 2006
9 >Serbin A. (gauleiter) 30 30 55 0 55 2208 10 Nov 2006
10 Касимов Р.Г. (Ренат) 8 71 22 32 54 281 10 Nov 2006
11 >Меркулов С.Н. (Sergei(RUS)) 31 31 54 0 54 2260 10 Nov 2006
12 >Михалев С.С. (Gert) 30 30 53 0 53 2284 10 Nov 2006
13 >Точилин (TC) 23 89 52 0 52 171 10 Nov 2006
14 Рыжиков С.А. (DeadLock5) 26 31 51 0 51 2173 08 Nov 2006
15 >Ломтев С.А. (Ахилл) 24 50 47 0 47 1183 10 Nov 2006
16 >Нечаев С.В. (Cepera) 27 27 47 0 47 2585 10 Nov 2006
17 Исаев В.Н. (Hunter-86) 27 27 47 0 47 2589 05 Nov 2006
18 Anneline A. (Anni337) 27 27 47 0 47 2591 08 Nov 2006
19 >Bear A. (Bear) 19 36 44 0 44 1805 10 Nov 2006
20 sichinava (zaza) 21 29 43 1 44 2296 10 Nov 2006
21 Распопин Е.В. (Mocart) 26 26 43 0 43 2760 08 Nov 2006
22 >Зубарева А.В. (Tonya) 13 54 32 10 42 686 10 Nov 2006
23 Козырев Д.О. (Raven) 22 27 41 0 41 2642 09 Nov 2006
24 H. Y. (SoftS) 25 25 40 0 40 2888 10 Nov 2006
25 >Gates B. (Экспериментатор) 25 25 40 0 40 2889 10 Nov 2006

Изучаем SQL

Кластеризованные Индексы в SQL Server: Вы должны это знать

Geert Vanhove (оригинал: Clustered Indexes in SQL Server: Things You Need to Know)
Перевод Моисеенко С.И.

Эта статья раскрывает некоторые аспекты использования кластеризованных индексов в SQLServer. Мало того, что я попытаюсь убедить Вас в абсолютной необходимости использования кластеризованных индексов, я также дам Вам некоторые советы относительно того, как использовать их в неочевидных случаях.

Зачем нужны кластеризованные индексы!

Как Вы знаете, механизм SQL Server обрабатывает таблицу без кластеризованного индекса, также называемую кучей, не так, как кластеризованную таблицу. В отличие от таблицы-кучи, кластеризованная таблица содержит свои страницы с данными в связанном списке и упорядоченными в соответствии с последовательностью ее индексной структуры. Таблица-куча использует IAM и PFS страницы для поиска страницы, имеющей достаточно свободного места для размещения новой строки, если не находит места на текущей странице (более подробную информацию можно найти в BOL).

Чтобы проиллюстрировать это различие в поведении, я создал небольшой скрипт, который Вы можете сами выполнить.

-- Init

Use tempdb

SET NOCOUNT ON

IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL DROP TABLE dbo.TestTable
CREATE TABLE dbo.TestTable ( GUID_col_indexed uniqueidentifier, CHAR_col char(10), GUID_col uniqueidentifier)
CREATE CLUSTERED INDEX IX1 ON dbo.TestTable ( GUID_col_indexed )
GO
-- Step 1: Load TestTable with 100.000 records
declare @t int
set @t=1
while @t <= 100000
begin
insert TestTable values (newid(), char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65)+ char(rand()*26 + 65), newid())
select @t=@t + 1
end
go
-- Step 2: Select 100 random values
select top 100 * from TestTable order by GUID_col
go
-- Step 3: Delete 1000 random values
delete TestTable where GUID_col in (select top 1000 GUID_col from TestTable order by GUID_col desc)
go
-- Step 4: Insert 1000 more values
declare @t int
set @t=1
while @t <= 1000
begin
insert TestTable values (newid(), char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65)+ char(rand()*26 + 65), newid())
select @t=@t + 1
end

go

Этот скрипт содержит 4 шага:
1. Загрузка 100 000 записей в таблицу, использующая сгенерированные случайным образом значения для всех полей.
2. Выбираются 100 случайных записей.
3. Удаляется 1 000 случайных записей.
4. Загружается еще 1 000 записей.

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

Вы должны запустить этот скрипт дважды, используя сначала в точности приведенный текст (создание кластеризованного индекса), а затем, убрав из него ключевое слово [CLUSTERED]. Я надеюсь, что Вы получите результаты, сопоставимые с теми, которые я получил после нескольких тестовых испытаний:

Процессор Чтения Продолжительность
Куча Кластер
Куча Кластер
Куча Кластер
- Шаг 1: Загрузка в таблицу 100.000 записей
5094 4063
374627 281336
5640 4926
- Шаг 2: Выборка 100 случайных строк
78 78
671 942
97 84
- Шаг 3: Удаление 1000 случайных строк
641 719
9551 7698
811 834
- Шаг 4: Вставка еще 1000 строк
218 47
5717 3076
210 45

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

Кластеризованные индексы на столбцах uniqueidentifier: отказаться! Или нет?

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

Но в некоторых системах VLDB (очень большие базы данных) с высокой транзакционной активностью это может быть решением, когда пропускная способность IO является потенциально узким местом. Предположим, что Вы имеете большую (> 100 Гбайт) таблицу базы данных, использующую файловую группу, которая распределена более чем на 10 физических дисках с кластеризованным индексом на столбце [date-inserted] (дата вставки). Когда имеет место много вставок в сочетании с большим количеством обновлений недавно добавленных записей, вся производительность будет сосредоточена на наборе физически группированных страниц данных, создавая точку перегрузки. Массивы незафиксированных страниц, которые процесс контрольной точки хочет сбросить на диск, могут стать слишком большими для обработки одним диском. Вот здесь кластеризованный индекс на столбце uniqueidentifier как раз может помочь. Вместо того чтобы сбрасывать все грязные (незафиксированные) страницы на один диск, ввод-вывод будет размазан по всем физическим дискам.

Обратите особое внимание на фрагментацию, используя это решение. Из-за вставок, происходящих случайным образом, разбиения страниц становятся более вероятными, увеличивая фрагментацию. Когда объемы ваших данных становятся слишком велики для ежедневной перестройки, выполняйте общую перестройку индексов каждую неделю. Я лично предпочитаю оператор CREATE INDEX … WITH DROP_EXISTING для перестройки индексов.

Заключение

1. Всегда используйте кластеризованный индекс на таблице базы данных OLTP.
2. Если Вы рассматриваете возможность использования кластеризованного индекса на столбце uniqueidentifier:
    a. Протестируйте ваше решение в тесте при полной нагрузке.
    b. Регулярно перестраивайте индексы.

08/12/2005

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

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

§ Поступила в продажу книга SQL. Задачи и решения, посвященная анализу ошибок, допускаемых при решении задач первого этапа. На сайте издательства Питер можно сделать заказ и познакомиться с содержанием.

Контакты

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