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


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

http://www.sql-ex.ru

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

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

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

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

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

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

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

§ Готовлю новые задачи для всех этапов. Планирую их выставить до следующей рассылки. Так что скучать не придется :-).

§ Последнюю задачу решила vlksm, перешедшая на третью позицию в рейтинге ( баллы 3, время .803). Ждем остальных лидеров.

§ Новые люди в сотне:
Fencer (задач 109, время 71.744)
Палкин (104, 10.009)

§ Сохранили шансы попасть в ТОР 10:
loki (123, 9.962)
SergeyTikh (120, 6.723)

§ Продолжили свое восхождение к вершине:
imsh (130, 55.583)
alex_v (127, 22.128)
Lord Nick (121, 27.080)
Gosha (108, 116.398)

§ На этой неделе превысили первый знаковый порог - 100 сертифицированных на сайте специалистов. Поздравляю нас всех с этим событием! Появились новые авторы, задачи я которых готовлю к публикации. В результате замены слабых задач требования будут меняться не только количественно, но и качественно.
Надеюсь, что это повысит доверие к нашему сертификату у работодателей, которые уже появляются на сайте. В ближайшее время подготовлю страничку для них с информацией о специалистах, которые ищут работу или не против ее поменять. Чтобы попасть на эту страницу, достаточно выбрать "да" в графе "Интересуют предложения о работе" в резюме специалиста. Сортировка по умолчанию будет выполнена по рейтинговой позиции.
На этой неделе сертифиционированы:
magnitt (A06012401) [BK] (г.Москва, Россия)
Flagman_er (A06011157) [BK] (г.Москва, Россия)
Johan (B06011832) [AR] (г.Красноярск, Россия)

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

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

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

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

Лучшие результаты (ТОР 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 22 Sep 2006
2 Васьков Е.В. (Johan) 139 139 20 326 57 9.626 .116 3 14 Sep 2006 22 Sep 2006
3 Карасёва Н.В. (vlksm) 139 139 20 326 137 25.724 .803 3 20 Sep 2006 21 Sep 2006
4 Мурашкин И.В. (lepton) 139 139 20 326 175 13.419 3.284 3 14 Sep 2006 22 Sep 2006
5 Войнов П.Е. (pаparome) 138 47 20 323 348 2.680 07 Aug 2006 22 Sep 2006
6 Slobodcicov A.N. (Testo) 138 92 20 323 337 7.180 25 Aug 2006 22 Sep 2006
7 Иванов А.Н. (Goapsy) 138 47 20 323 270 18.919 07 Aug 2006 07 Aug 2006
8 Голубин Р.С. (Roman S. Golubin) 138 47 20 323 353 21.138 06 Aug 2006 22 Sep 2006
9 Валуев Д.И. (Fiolent) 138 47 20 323 1085 54.545 24 Aug 2006 22 Sep 2006
10 Кувалкин К.С. (Cyrilus) 137 47 20 321 623 9.768 09 Aug 2006 22 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 22 Sep 2006
14 Держальцев В.А. (MadVet) 137 92 20 319 506 25.778 04 Sep 2006 20 Sep 2006
15 Тарасов Д.Б. (Gavrila) 136 47 20 318 346 19.382 07 Aug 2006 22 Sep 2006
16 Страшников А.С. (EffEct) 136 87 20 317 483 61.850 10 Sep 2006 21 Sep 2006
17 Матвеева Ю.Б. (Julia_M) 136 87 20 317 310 81.870 13 Sep 2006 22 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 >Сизов (Perfect Stranger) 68 68 127 32 159 275 22 Sep 2006
2 Шишов Д.В. (SHDV) 59 59 109 32 141 370 21 Sep 2006
3 Железняков В.С. (Ennor Tiegael) 59 59 109 32 141 371 22 Sep 2006
4 Suchkov (andrews) 55 55 99 0 99 939 20 Sep 2006
5 Павлюк Д. (О...!) 32 53 65 29 94 538 21 Sep 2006
6 >Левыкин Д.А. (TODD) 44 44 74 17 91 1064 22 Sep 2006
7 >Этин А.А. (Diomed) 44 44 75 9 84 1185 22 Sep 2006
8 Cвиридов (nafta) 45 45 74 2 76 1028 21 Sep 2006
9 >Терехов К.В. (KT) 40 40 73 0 73 1440 22 Sep 2006
10 >Терехов К.В. (void) 40 40 73 0 73 1441 22 Sep 2006
11 Breusov (Mazoku) 41 41 66 3 69 1556 22 Sep 2006
12 >Селиверстов А.Г. (Алескандр) 34 40 67 0 67 1447 22 Sep 2006
13 nameless (AntonBSE) 24 65 46 15 61 299 20 Sep 2006
14 Тарноруцкий А.В. (th0mas) 29 29 43 17 60 1820 19 Sep 2006
15 >Boiko (MIB) 38 38 59 0 59 1860 22 Sep 2006
16 Басурин (rarog) 35 35 57 0 57 1940 19 Sep 2006
17 Павловская А. (Pavlina) 32 32 57 0 57 1943 22 Sep 2006
18 >hart (bret) 37 37 57 0 57 1946 22 Sep 2006
19 Ниязбаева С.В. (Светлана) 31 31 56 0 56 1980 22 Sep 2006
20 >Салимов Д.М. (Damirishe) 30 30 55 0 55 2009 22 Sep 2006
21 >Tsimashevich V. (Alonzo) 32 32 54 0 54 2058 22 Sep 2006
22 Кривоносов О.Ю. (educated fool) 25 32 51 0 51 1904 20 Sep 2006
23 >Бичев А.В. (ArcadyVL) 24 54 49 0 49 854 22 Sep 2006
24 Meshkov A. (Avatar) 28 28 49 0 49 2250 19 Sep 2006
25 >Ильиных Е.А. (ЖеняИ) 17 31 41 6 47 1693 22 Sep 2006
26 >Минаев Д.С. (Horoshiy) 27 27 46 1 47 2352 22 Sep 2006
27 Алексеенко А.А. (sunflower) 21 44 41 0 41 1450 22 Sep 2006
28 >zeke Z.Z. (zeke) 17 30 41 0 41 2026 22 Sep 2006

Изучаем SQL

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

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

UNION против UNION ALL

В любом случае, когда возможно используйте UNION ALL вместо UNION. Различие между ними заключается в том, что UNION имеет "побочный эффект" в виде устранения всех дубликатов и сортировку результатов, в то время как UNION ALL этого не делает. Для выбора неповторяющихся данных требуется создание временной рабочей таблицы, в которой хранятся все строки и происходит сортировка перед выводом результата. (Вывод плана выполнения для запроса SELECT DISTINCT покажет, что имеет место агрегация потока, потребляющая 30 % всех ресурсов, используемых для выполнения запроса.) В некоторых случаях, это именно то, что вам необходимо - и тогда UNION ваш друг. Но если в результирующем множестве не предполагается наличие дублированных строк, то лучше использовать UNION ALL. Он просто осуществляет выборку из одной таблицы или соединения, а затем из другой, добавляя результаты к концу первого результирующего набора. UNION ALL не требует никакой временной рабочей таблицы и никакой сортировки (если нет других условий, требующих сортировки). В большинстве случаев это намного более эффективно. Существует еще одна потенциальная проблема - опасность переполнения базы данных tempdb огромной вспомогательной рабочей таблицей. Это может произойти в том случае, если запрос с UNION должен выдать большой результирующий набор.

Пример

Следующие запросы выбирают ID для всех магазинов в таблице sales, которая представляет собой оригинал таблицы в базе данных pubs, и все ID для магазинов из таблицы big_sales - версии таблицы sales, в которую мы добавили приблизительно 70 000 строк. Различие между двумя решениями заключается только в использовании UNION вместо UNION ALL. Однако добавление ключевого слова ALL существенно влияет на план выполнения запроса. Первое решение требует агрегации потоков и сортировку результатов, прежде чем они будут возвращены клиенту. Второй запрос намного более эффективен, особенно для больших таблиц. В этом примере оба запроса возвращают одинаковые результирующие наборы, хотя и в разном порядке. В наших тестах мы использовали использовали две временные таблицы во время выполнения. Поэтому ваши результаты могут отличаться.

Решение с UNION Решение с UNION ALL

SELECT stor_id FROM big_sales
UNION
SELECT stor_id FROM sales

SELECT stor_id FROM big_sales
UNION ALL
SELECT stor_id FROM sales

|--Merge Join(Union)
     |--Stream Aggregate(GROUP BY:
     ([big_sales].[stor_id]))
     | |--Clustered Index Scan
         (OBJECT:([pubs].[dbo].
         [big_sales].
         [UPKCL_big_sales]),
         ORDERED FORWARD)
     |--Stream Aggregate(GROUP BY:
         ([sales].[stor_id]))
         |--Clustered Index Scan
         (OBJECT:([pubs].[dbo].
         [sales].[UPKCL_sales]),
         ORDERED FORWARD) 		|--Concatenation
     |--Index Scan
    (OBJECT:([pubs].[dbo].
     [big_sales].[ndx_sales_ttlID]))
     |--Index Scan
    (OBJECT:([pubs].[dbo].
     [sales].[titleidind]))
Table 'sales'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'big_sales'. Scan count 1, logical reads 463, physical reads 0, read-ahead reads 0. 		Table 'sales'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'big_sales'. Scan count 1, logical reads 224, physical reads 0, read-ahead reads 0.

Хотя результирующие наборы в этом примере аналогичны, можно увидеть, что предложение UNION ALL потребовало почти вдвое меньше ресурсов по сравнению с теми, которые потребовались для операции UNION. Так что если существует уверенность в том, что в ожидаемом результирующем наборе нет дубликатов, используйте предложение UNION ALL.

Функции и выражения, подавляющие использование индексов

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

Примеры

Необходимо помогать SQL Server удалять любые выражения, применяемые к числовым столбцам, которые формируют индекс. Следующие запросы выбирают строку из таблицы jobs по уникальному ключу, который имеет уникальный кластеризованный индекс. Если применять выражение к столбцу, то индекс использоваться не будет. Но если только изменить условие 'job_id - 2 = 0' на 'job_id=2' оптимизатор выполнит операцию поиска (seek) по кластеризованному индексу.

Запрос, с неиспользующимся индексом Оптимизированный запрос, использующий индекс

SELECT *
FROM jobs
WHERE (job_id-2) = 0

SELECT *
FROM jobs
WHERE job_id = 2

|--Clustered Index Scan(OBJECT:
     ([pubs].[dbo].[jobs].
     [PK__jobs__117F9D94]),
     WHERE:(Convert([jobs].[job_id])-2=0))
|--Clustered Index Seek(OBJECT:
([pubs].[dbo].[jobs].
[PK__jobs__117F9D94]),

SEEK:([jobs].[job_id]=Convert([@1])) ORDERED FORWARD)
Заметьте, что seek значительно лучше, чем scan в предыдущем запросе.

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

Запрос, с неиспользующимся индексом Оптимизированный запрос, использующий индекс

DECLARE @job_id VARCHAR(5)
SELECT @job_id = '2'
SELECT *
FROM jobs
WHERE CONVERT( VARCHAR(5), job_id ) = @job_id

DECLARE @job_id VARCHAR(5)
SELECT @job_id = '2'
SELECT *
FROM jobs
WHERE job_id = CONVERT(SMALLINT, @job_id )

SELECT *
FROM authors
WHERE au_fname + ' ' + au_lname = 'Johnson White'

SELECT *
FROM authors
WHERE au_fname = 'Johnson' AND au_lname = 'White'

SELECT *
FROM authors
WHERE SUBSTRING( au_lname, 1, 2 ) = 'Wh'

SELECT *
FROM authors
WHERE au_lname LIKE 'Wh%'

CREATE INDEX employee_hire_date
ON employee ( hire_date ) GO

-- Получаем всех сотрудников, нанятых
-- в первом квартале 1990 года:

SELECT *
FROM employee
WHERE DATEPART( year, hire_date ) = 1990
AND DATEPART( quarter, hire_date ) = 1

CREATE INDEX employee_hire_date
ON employee ( hire_date ) GO

-- Получаем всех сотрудников, нанятых
-- в первом квартале 1990 года:

SELECT *
FROM employee
WHERE hire_date >= '1/1/1990'
AND hire_date < '4/1/1990'

-- Предположим, что дата найма
-- может содержать время, отличное от 12AM
-- Кто был нанят 2/21/1990?

SELECT *
FROM employee
WHERE CONVERT( CHAR(10), hire_date, 101 ) = '2/21/1990'

-- Предположим, что дата найма
-- может содержать время, отличное от 12AM
-- Кто был нанят 2/21/1990?

SELECT *
FROM employee
WHERE hire_date >= '2/21/1990' AND hire_date < '2/22/1990'

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

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

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

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

Контакты

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