Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 43 (9 июля 2005 г.)
Новым посетителям сайта
Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 59-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается
один самый худший показатель среди всех решенных им упражнений.
Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.
Новости сайта
§ Сертифицированный на сайте специалист появился в Молдавии.
§ Замеченный (Cyrilus) недостаток проверки задачи 112 устранен добавлением необходимых данных.
§ Незначительная стилистическая правка формулировки задачи 80 предложена молдавским сибиряком.
§ Ни один сайт не может нормально развиваться без постоянных посетителей - людей, которые своим активным участием определяют направление его развития. В результате появилась страница "Аксакалы сайта", которая заменила за ненадобностью страницу рейтинга первого этапа. Пока на эту страницу попадают участники рейтинговой системы, которые преодолели как минимум первый сертификационный барьер (65 упражнений на SELECT) и провели на сайте
более года (от момента регистрации до времени последнего решенного задания). Если у вас есть предложение других (или дополнительных) критериев, пишите.
§ Число подписчиков - 2210
Число участников рейтинга - 3548
Число участников второго этапа - 340
Лучшие результаты (ТОР 20)
No
Person
Number of Sel_ex
Last_Sel
Number of DML_ex
Scores
Days
Days_2
LastSolved
LastVisit
1
Кувалкин К.С. (Cyrilus)
137
112
20
312
224
5.129
06 Jul 2005
08 Jul 2005
2
Гонтовой В.А. (noname)
137
112
20
312
105
9.808
29 Jun 2005
02 Jul 2005
3
Леденев С.А. (Shurgenz)
137
112
20
312
313
9.900
27 Jun 2005
07 Jul 2005
4
Валуев Д.И. (Fiolent)
137
112
20
312
662
26.627
27 Jun 2005
01 Jul 2005
5
Галиаскаров Э.Г. (Galogen)
137
112
20
312
221
61.437
01 Jul 2005
01 Jul 2005
6
>Бураков С.Г. (burakov58)
136
136
20
308
160
11.525
08 Jul 2005
08 Jul 2005
7
Мельникова И.А. (Iris_m)
135
137
20
308
380
89.865
27 May 2005
14 Jun 2005
8
Зверев Д.Л. (dimzv)
135
136
20
306
580
2.471
06 Jun 2005
15 Jun 2005
9
Колосов А.С. (KAS)
134
137
20
306
25
3.398
11 Mar 2005
14 Jun 2005
10
Сныткин В.Л. (Ded I)
134
136
20
304
252
7.456
12 May 2005
08 Jul 2005
11
Рахманов И.Е. (bloom)
134
136
20
304
148
14.171
11 May 2005
15 Jun 2005
12
Hakobyan H.H. (hamlet)
134
136
20
304
220
37.869
07 May 2005
03 Jun 2005
13
Шипунов И. (IAS)
134
136
20
304
334
82.080
13 May 2005
26 May 2005
14
Иткин И.Л. (joseph_itkin)
132
136
20
299
375
2.849
07 Mar 2005
13 Apr 2005
15
Spirin (spirin)
131
136
19
296
158
13.461
21 Jan 2005
24 Jan 2005
16
Михайлов В.Г. (mslava)
132
136
17
293
648
10.504
25 Mar 2005
25 Mar 2005
17
Gershovich (VIG)
127
127
20
292
847
13.051
06 Jul 2005
08 Jul 2005
18
Пятница О.А. (Robin)
125
128
20
287
754
74.630
19 Mar 2005
03 Jul 2005
19
Митронин А.А. (mitronin)
123
124
20
283
701
27.541
31 May 2005
01 Jun 2005
20
Булаев В.В. (Kvix)
119
121
20
274
266
24.671
18 May 2005
18 May 2005
Лучшие результаты за неделю
No
surname
n_sel
sel_all
sel_scores
dml_scores
scores
rating
last_visit
1
Кочнов Д.А. (dcochnov)
59
59
107
32
139
269
05 Jul 2005
2
Пантюхин М.Ю. (maxifly)
34
58
68
30
98
323
08 Jul 2005
3
>Basarab A.N. (AleXqwq)
54
54
93
0
93
432
07 Jul 2005
4
>Berezdetsky I.T. (Ivan TB)
50
50
88
0
88
468
08 Jul 2005
5
Новиков (Nop)
45
45
72
15
87
628
07 Jul 2005
6
>Носков Н. (niko2)
51
51
86
0
86
488
08 Jul 2005
7
Скударь А.С. (ASkudar)
24
64
43
32
75
189
06 Jul 2005
8
>Круглов Н.И. (Working)
43
43
68
5
73
668
08 Jul 2005
9
>Томшинский В.А. (Seva)
37
37
66
1
67
686
08 Jul 2005
10
Lastname M.U. (xxx)
43
43
66
0
66
687
07 Jul 2005
11
>Baldin O.B. (Weed)
21
59
50
14
64
272
08 Jul 2005
12
Busel (Ellith)
38
38
64
0
64
713
07 Jul 2005
13
Rookie (rookiejunior)
31
31
50
9
59
926
07 Jul 2005
14
Bahety K. (keshav_b)
33
33
52
3
55
896
04 Jul 2005
15
Subbotin (sarges)
24
24
37
17
54
1165
07 Jul 2005
16
>Кочетов А.В. (Use_0k!!!)
32
32
53
0
53
886
08 Jul 2005
17
Ponomarenko A.G. (Ponomarenko)
22
35
47
0
47
767
06 Jul 2005
18
>d D.D. (parteigenosse)
27
27
46
0
46
1015
06 Jul 2005
19
>Воловик (Andrys)
25
25
39
5
44
1145
08 Jul 2005
20
Kascheyev A. (Alex.K)
24
24
37
7
44
1195
08 Jul 2005
21
Шепелев И.Н. (waspworths)
24
47
40
3
43
599
07 Jul 2005
22
>Силантьев С.В. (silich)
15
74
33
9
42
128
08 Jul 2005
23
Холин К.В. (f.nietzsche)
22
59
41
0
41
274
07 Jul 2005
24
>Абдурахманов А.А. (Amantay A)
25
25
40
0
40
1120
08 Jul 2005
25
>Lomiashvili K. (LKakha)
25
25
40
0
40
1128
08 Jul 2005
26
Басов В. (vasua)
24
24
37
3
40
1178
05 Jul 2005
27
Фолифоров М.А. (Max_GT)
24
24
37
3
40
1186
07 Jul 2005
Изучаем SQL
Общий план оптимизации и настройки запросов (окончание, начало в вып.42)
Если вы находите сегменты кода, которые кажутся более длительными, чем должны бы быть, используйте следующий список, чтобы пробовать определить что не так в этом сегменте. Вы должны также использовать этот список, чтобы оптимизировать весь ваш код вообще, даже если этот код, возможно, не выглядит проблемным.
Есть ли в коде какие-нибудь курсоры? Курсоры - главная проблема производительности и обычно требуют обработки. Простая временная таблица с полем IDENTITY может обычно использоваться вместо необходимости применения курсора.
Можете ли вы уменьшить использование временных таблиц или типа данных TABLE в запросе? Хотя эти объекты находят свои области применения, некоторые разработчики имеют привычку использовать их даже тогда, когда они на самом деле не нужны. Спросите себя, нельзя ли использовать JOIN вместо этого, а может другое предложение WHERE устранит необходимость в таблице. Если вы не можете обойтись без таблицы, посмотрите, можете ли вы уменьшить ее размер с помощью фильтров или использовать базовую таблицу, созданную
в базе данных, чтобы вам не приходилось терять производительность на создании таблицы всякий раз, когда она потребуется.
Имеются ли какие-нибудь операторы, которые изменяют структуру таблиц? Так же, как создание временной таблицы в запросе, модификация таблиц в запросах, также вызывает потерю производительности. Посмотрите, что можно сделать с этими операторами. Возможно, создание общей таблицы вас вполне устроит.
Не возвращает ли запрос больше данных, чем это необходимо. Перегрузка сети может вызвать незначительные проблемы с хранимыми процедурами. Пробуйте не возвращать больше данных или столбцов, чем это необходимо для приложения. Также старайтесь использовать SET NOCOUNT ON, чтобы уменьшить обычно ненужный подсчет строк, который возвращается всеми запросами.
Используете ли вы хранимую системную процедуру sp_executesql вместо команды EXECUTE, чтобы выполнить строку? Системная хранимая процедура sp_executesql имеет некоторое преимущество в производительности перед командой EXECUTE. Найдите возможность заменить EXECUTE на sp_executesql.
Создаете ли вы транзакции в пределах запроса? Если вы создаете транзакцию в коде, удостоверьтесь, что вы можете оценить стоимость этой транзакции. Зная стоимость транзакции, вы сможете оценить, будет ли она обычно завершаться за 1 секунду, 1 минуту или 1 час. Можно принимать меры для упрощения транзакции, если оценка ее стоимости превышает несколько секунд. В противном случае вы рискуете получить очень медленные запросы, которые, помимо этого, могут вызвать проблемы блокировки базы данных в течение времени,
необходимого для завершения транзакции.
Старайтесь создавать процедуры как можно меньшего размера, устраняя ненужный код. Спросите себя, будет ли оператор IF когда-либо выполнять все свои переходы, - если нет, удалите их. Еще лучше создать другие хранимые процедуры, которые обрабатывают функцию каждого перехода в операторе IF. Также посмотрите, имеются ли части кода, которые используются много раз; в этом случае их можно убрать и создать из них отдельные хранимые процедуры. Наконец, разберитесь с использованием оператора CASE. Операторы CASE
могут иногда устранить большое количество кода, если вы знаете, как использовать их должным образом.
Используйте новый синтаксис ANSI JOIN вместо соединения в старом стиле. Новый синтаксис соединения имеет некоторое преимущество в производительности по сравнению со старым способом использования предложения WHERE для соединения. Новый синтаксис обладает также лучшей читаемостью и должен стать нормой в вашем кодировании на Transact-SQL.
Иногда приходится слышать рекомендации о замене динамической части кода на параметризованные запросы. Это зависит от конкретной ситуации, т.к. я обнаружил, что в некоторых случаях, динамический запрос выполняет лучше, даже если не используется кэшируемый план. Чтобы сделать правильный выбор, вам нужно провести собственное исследование на основе конкретного запроса и базы данных.
Ищите объекты, которые вызываются без уточнения имени владельца (owner). Каждый раз, когда запрос выполняется без уточнения имени владельца объектов, на которые имеются ссылки в запрос, оптимизатор должен удерживать блокировки компиляции на системных объектах, пока не определит, являются ли эти объекты теми же самыми, что и находящиеся в кэшируемом плане. Указание имени владельца решит эту проблему и поможет с проблемами производительности и блокировки.
Обращайте внимание на типы данных, используемых в предложении WHERE операторе соединения. Любые сравнения данных или объединения должны быть выполнены с теми же самыми типами данных. Если типы данных различны, то незначительная потеря производительности возникает в связи с преобразованием типов данных, которое должно быть произведено до выполнения сравнения.
Перекомпиляция запросов
Даже если вы оптимизировали запросы, анализируя план выполнения и код, вы все еще можете иметь проблему, которую огромное большинство разработчиков даже не рассматривает. Эта проблема - стоимость работы, выполняемая всякий раз, когда для запроса должен быть создан новый план или выполнена повторная компиляция. Обычно повторной компиляции легко избежать, что дает значительный прирост производительности многих приложений. Следует иметь в виду, когда вы получаете список перекомпилирующихся хранимых процедур,
что есть законные причины для того, чтобы перекомпилировать хранимые процедуры. Поэтому вы должны побеспокоиться в первую очередь о тех из них, для которых компиляция выполняется при каждом их исполнении. Другими вы можете заняться позже, решая, требуется ли для них перекомпиляция или нет.
Ищите смесь операторов DDL и DML. Не перемешивайте создание объектов и использование этих объектов в вашем коде. Для этого переместите создание всех объектов в верхнюю часть вашего запроса.
Ищите команды ANSI SET. Изменение состояния SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL приведет к перекомпиляции запроса при каждом его выполнении. Убедитесь в том, что вам действительно необходимо изменять эти настройки прежде, чем вы сделаете это внутри запроса.
Если вам необходимо использовать курсор в коде, удостоверьтесь, что в курсоре нет ссылок на временные объекты. Ссылка на временный объект в курсоре приведет к повторной компиляции запроса при каждом его выполнении.
Итак, вы изучили план выполнения, проанализировали код и выявили повторную компиляцию; что теперь? Лишь только то, что все выглядит прекрасно, еще не означает, что запрос будет удовлетворительно выполняться в каждом отдельном сценарии. Вы должны придумать различные тесты для вашего запроса. Как он выполняется на наборах данных различного объема? Как он выполняется с различными наборами параметров? Что будет, если вы запустите запрос многократно, используя многочисленные соединения в Query Analyzer? Что бы
вы ни думали о своем конкретном случае, требуется выполнять трассировку и анализ. Только не говорите: "Я настроил этот запрос, и он готов", - если вы запустили его только один раз. Проявите твердость и убедите ваших клиентов и менеджеров, что вам нужно время, чтобы полностью проверить новый или недавно переработанный запрос. Это более важно, чем быстрая установка заплаток, чтобы только выполнить работу в срок.
Как было сказано, это только общий план. Добавьте в него любые новые пункты, которые пришли вам в голову. Расширьте мои этапы своими наработками, накопленными за годы, и решениями общих проблем, которые вы обнаружили в своей базе данных. Как бы вы ни решили оптимизировать ваши запросы, запишите это, чтобы у вас было нечто "формальное", что можно передать другим в вашей группе или просто использовать, чтобы дать ответы на вопрос интервью, который захватил меня врасплох, если вам это когда нибудь понадобится.
2003 г.
Полезная информация
Конкурс
§ Мы выставили наш сайт на конкурс Интернить 2005. Победитель определяется числом поданых голосов. Просьба проголосовать. (рекомендуемая оценка 3 :-)).
Контакты
По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.