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


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

http://www.sql-ex.ru

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

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

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

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

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

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

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

§ Финишный спурт совершил Johan, который не только завершил второй этап, но и по инерции решил первую и пока единственную задачу третьего этапа, показав второе после автора время Johan (баллы 3, время 0.116). Для участников третьего этапа, которых я, за неимением лучшего предложения, буду называть McL, указываются показатели третьего этапа.
Третье место у автора одной из будущих задач третьего этапа lepton(3, 3.284).

§ Новый человек в сотне - Roman1979 (задач 109, время 12.698).

§ Оценка шансов на попадание в ТОР 10 после введения третьего этапа стала довольно условной, т.к. третий этап начинается с нуля и после решения всех задач второго этапа у каждого появляется такой шанс (что может стать дополнительным стимулом), но тем не менее.
Сохранили шансы попасть в ТОР 10:
PS_Sergey (124, 8.135)
Родион1976 (109, 0.994)
StrayCat (104, 2.932)

§ Продолжили свое восхождение к вершине:
EffEct (136, 61.850)
Julia_M (136, 81.870)
imsh (128, 55.583)
Weed (122, 84.964)
Johnny (116, 161.247)
silich (115, 53.530)
Евпатий (115, 73.825)
Goga_3040 (120, 36.097)
snikol (106, 45.515)

§ На этой неделе сертифицированы:
Ocean (A06009763 [BK] и B06009763 [AR]) (г.Челябинск, Россия)
PolygalovIvan (A06012205 [BK]) (г.Барнаул, Россия)
Очень лысый (A06009918) [BK] (г.Домодедово, МО, Россия)
alex_v (B06010925 [AR]) (г.Москва, Россия)
Testo (A06006426 [BK] и B06006426 [AR]) (Chisinau, Moldova)
PS_Sergey (B06010240 [AR]) (г.Таганрог, Россия)
Lotar (A06012855 [BK]) (г.Кострома, Россия)

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Юлдашев М.Р. (Snowbear) 139 139 20 326 418 4.055 .000 3 09 Sep 2006 15 Sep 2006
2 Васьков Е.В. (Johan) 139 139 20 326 57 9.626 .116 3 14 Sep 2006 15 Sep 2006
3 Мурашкин И.В. (lepton) 139 139 20 326 175 13.419 3.284 3 14 Sep 2006 15 Sep 2006
4 Войнов П.Е. (pаparome) 138 47 20 323 348 2.680 07 Aug 2006 15 Sep 2006
5 Slobodcicov A.N. (Testo) 138 92 20 323 337 7.180 25 Aug 2006 15 Sep 2006
6 Иванов А.Н. (Goapsy) 138 47 20 323 270 18.919 07 Aug 2006 07 Aug 2006
7 Голубин Р.С. (Roman S. Golubin) 138 47 20 323 353 21.138 06 Aug 2006 15 Sep 2006
8 Карасёва Н.В. (vlksm) 138 47 20 323 92 24.922 06 Aug 2006 13 Sep 2006
9 Валуев Д.И. (Fiolent) 138 47 20 323 1085 54.545 24 Aug 2006 15 Sep 2006
10 Кувалкин К.С. (Cyrilus) 137 47 20 321 623 9.768 09 Aug 2006 15 Sep 2006
11 Абашин П.И. (Dizil) 137 47 20 319 348 3.903 07 Aug 2006 31 Aug 2006
12 Самохвалов В. (ValdemarES) 137 47 20 319 273 7.850 17 Aug 2006 11 Sep 2006
13 Солдатенков Ю.С. (SolYUtor) 137 137 20 319 111 12.909 06 Sep 2006 15 Sep 2006
14 Держальцев В.А. (MadVet) 137 92 20 319 506 25.778 04 Sep 2006 14 Sep 2006
15 Тарасов Д.Б. (Gavrila) 136 47 20 318 346 19.382 07 Aug 2006 15 Sep 2006
16 Страшников А.С. (EffEct) 136 87 20 317 483 61.850 10 Sep 2006 12 Sep 2006
17 Матвеева Ю.Б. (Julia_M) 136 87 20 317 310 81.870 13 Sep 2006 13 Sep 2006
18 Kamaev V.M. (Heromantor) 135 138 20 316 128 9.044 14 Mar 2006 25 Mar 2006
19 Бураков С.Г. (burakov58) 135 138 20 316 419 17.381 24 Mar 2006 07 Apr 2006
20 frenkental (a2010) 135 137 20 315 110 15.332 19 Jul 2006 26 Jul 2006

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Юрковец Н. (yna) 47 47 82 19 101 872 15 Sep 2006
2 Яковлев Е. (Hazer) 40 40 73 26 99 926 11 Sep 2006
3 >obuhova (chu) 49 50 92 0 92 1029 15 Sep 2006
4 >Устинкин С.В. (TheChups) 48 48 82 0 82 1202 15 Sep 2006
5 Надаев Э.А. (Nerd) 25 58 50 31 81 379 15 Sep 2006
6 Романов О.В. (ORomanov) 36 36 65 6 71 1492 14 Sep 2006
7 >dell D.I. (dellsystems) 31 31 54 0 54 2021 15 Sep 2006
8 >Бичев А.В. (ArcadyVL) 30 30 50 3 53 2042 15 Sep 2006
9 >Сапронова О.А. (JKsenja) 29 29 52 0 52 2088 15 Sep 2006
10 >Колоткин А.А. (AlexeiAres) 21 41 42 8 50 1294 15 Sep 2006
11 >Dzygar A.A. (Dzygar) 25 25 41 9 50 2156 15 Sep 2006
12 Никифоров А.П. ([PSTU]Arni-training) 28 28 49 0 49 2201 15 Sep 2006
13 Ядыкин М.Е. (Mighty Mamont) 28 28 49 0 49 2214 13 Sep 2006
14 >Любченко В.А. (IAS56) 21 43 41 6 47 1271 15 Sep 2006
15 >Чернега П.П. (pasha) 20 39 45 0 45 1664 15 Sep 2006
16 >raju R.R. (phoenix_5050) 23 23 43 0 43 2465 15 Sep 2006
17 >Gad J.C. (Josephine) 20 37 42 0 42 1243 15 Sep 2006
18 Самойлов К. (aka_CoBra) 4 59 10 31 41 361 15 Sep 2006
19 nameless (AntonBSE) 25 41 41 0 41 1054 09 Sep 2006
20 >maha I.S. (indra11) 25 25 33 8 41 2515 15 Sep 2006
21 kattamudi R.K. (ramakrishna) 29 29 40 0 40 2589 15 Sep 2006

Изучаем SQL

Настройка операторов SQL на Microsoft SQL Server 2000 (продолжение, начало в вып.99)

Kevin Kline, Claudia Fernandez, Quest Software, Inc. (оригинал: Tuning SQL Statements on Microsoft SQL Server 2000)
Перевод Живенко Н.

Методы настройки запросов

В данной статье на примерах и планах выполнения показаны полезные технические приемы для улучшения запросов в Microsoft SQL Server 2000. Существует множество небольших подсказок и технических приемов, применимых для небольшого класса задач программирования. Их знание расширит ваши возможности в оптимизации производительности. Для отображения выходных данных всех примеров в этом разделе используется SHOWPLAN_ALL, т.к. его вывод более компактен и при этом показываются вся критически важная информация. Большинство примеров основано либо на базе данных PUBS, либо на системных таблицах. Мы значительно увеличили размер таблиц, используемых в базе данных PUBS, добавив десятки тысяч записей во многие из них.

Оптимизация подзапросов

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

Пример

В следующих запросах необходимо получить названия магазинов и количество книг, проданных в каждом магазине. Оба запроса возвращают один и тот же результирующий набор, но первый из них использует подзапрос, а второй - внешнее соединение. Сравним планы выполнения запросов, произведенные Microsoft SQL Server.

Решение с помощью подзапроса Решение с помощью соединения
SELECT st.stor_name AS 'Store',
(SELECT SUM(bs.qty)
FROM big_sales AS bs
WHERE bs.stor_id = st.stor_id), 0)
AS 'Books Sold'
FROM stores AS st
WHERE st.stor_id IN
(SELECT DISTINCT stor_id
FROM big_sales) 		SELECT st.stor_name AS 'Store',
SUM(bs.qty) AS 'Books Sold'
FROM stores AS st
JOIN big_sales AS bs
ON bs.stor_id = st.stor_id

WHERE st.stor_id IN
(SELECT DISTINCT stor_id
FROM big_sales)
GROUP BY st.stor_name
SQL Server parse and compile time:
CPU time = 28 ms,
elapsed time = 28 ms.
SQL Server Execution Times:
CPU time = 145 ms,
elapsed time = 145 ms.
Table 'big_sales'. Scan count 14, logical reads 1884, physical reads 0, read-ahead reads 0.
Table 'stores'. Scan count 12, logical reads 24, physical reads 0, read-ahead reads 0. 		SQL Server parse and compile time:
CPU time = 50 ms,
elapsed time = 54 ms.
SQL Server Execution Times:
CPU time = 109 ms,
elapsed time = 109 ms.
Table 'big_sales'. Scan count 14, logical reads 966, physical reads 0, read-ahead reads 0.
Table 'stores'. Scan count 12, logical reads 24, physical reads 0, read-ahead reads 0.

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

Заметим, что результирующие множества совпадают в обоих случаях, хотя порядок сортировки различен, поскольку запрос с соединением (с его предложением GROUP BY) подразумевает неявное использование ORDER BY:

Store Books Sold
-------------------------------------- -----------
Barnum's 154125
Bookbeat 518080
Doc-U-Mat: Quality Laundry and Books 581130

Eric the Read Books 76931
Fricative Bookshop 259060
News & Brews 161090
(6 row(s) affected)
Store Books Sold
-------------------------------------- -----------
Eric the Read Books 76931
Barnum's 154125
News & Brews 161090
Doc-U-Mat: Quality Laundry and Books 581130
Fricative Bookshop 259060
Bookbeat 518080
(6 row(s) affected)

Изучение плана выполнения запроса для метода с подзапросом показывает:

|--Compute Scalar(DEFINE:([Expr1006]=isnull([Expr1004], 0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([st].[stor_id]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([big_sales].[stor_id]))
| |--Stream Aggregate(GROUP BY:([big_sales].[stor_id]))
| | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales].
[UPKCL_big_sales]), ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].[UPK_storeid]
AS [st]),
SEEK:([st].[stor_id]=[big_sales].[stor_id]) ORDERED FORWARD)
|--Stream Aggregate(DEFINE:([Expr1004]=SUM([bs].[qty])))
|--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales].
[UPKCL_big_sales] AS [bs]),
SEEK:([bs].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

Тогда как с запросом, использующим соединение, имеем:

|--Stream Aggregate(GROUP BY:([st].[stor_name])
DEFINE:([Expr1004]=SUM([partialagg1005])))
|--Sort(ORDER BY:([st].[stor_name] ASC))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([st].[stor_id]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([bs].[stor_id]))
| |--Stream Aggregate(GROUP BY:([bs].[stor_id])
DEFINE:([partialagg1005]=SUM([bs].[qty])))
| | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales].
[UPKCL_big_sales] AS [bs]), ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].
[UPK_storeid] AS [st]),
SEEK:([st].[stor_id]=[bs].[stor_id]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales].
[UPKCL_big_sales]),
SEEK:([big_sales].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

Вариант решения с помощью соединения является более эффективным. Он не требует дополнительного агрегирующего потока, который производит суммирование по столбцу big_sales.qty, необходимое для обработки подзапроса.

(Продолжение следует...)

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

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

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

Контакты

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