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

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


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

Выпуск 221 от 20 декабря 2008 г.

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

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

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

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

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


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

§ Сделали, наконец, крупные картинки эскизов сертификатов:
BK
AR

§ На странице движения лидеров теперь выводится и время, которое прошло на текущий момент еще нерешенной, но начатой задачи.

§ К третьему этапу подошел:
45. AlShin (задач 138, время 13.254)

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
8. GreyC (147)

§ Продвинулись в рейтинге:
46. orange (138, 15.979)
77. shadon (127, 28.810)

§ Новые лица в ТОР 100 и вернувшиеся туда:
83. Shark (126, 3.865)
98. Nariman Kurbanoff (124, 93.698)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
114. antihero (121, 14.842)
116. Romul_T (121, 58.431)
142. B o r i s (118, 79.166)

§ На этой неделе сертифицированы:
mar_vi (B08034980) [AR] - г.Липецк, Россия
AlShin (B08040682) [AR] - г.Москва, Россия

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

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

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

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

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

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

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Никотин В.М. (@Nikotin) 150 150 21 364 108 8.371 3.751 40 13 Dec 2008 19 Dec 2008
2 Сальников С.А. ($erges) 150 150 21 364 291 3.487 3.824 40 13 Dec 2008 19 Dec 2008
3 >Креславский О.М. (Arcan) 150 116 21 364 689 58.248 39.373 40 19 Dec 2008 19 Dec 2008
4 Карасёва Н.В. (vlksm) 150 150 21 364 953 78.649 49.585 40 14 Dec 2008 19 Dec 2008
5 Печатнов В.В. (pvv) 146 149 21 352 357 30.849 17.490 36 10 Oct 2008 19 Dec 2008
6 Селезнёв А.С. (Артём С.) 145 149 21 349 322 38.500 29.235 36 25 Sep 2008 21 Nov 2008
7 Муллаханов Р.Х. (rem) 148 150 21 356 460 14.351 19.979 32 13 Dec 2008 19 Dec 2008
8 Сенкевич С.В. (GreyC) 147 147 21 355 319 43.823 13.066 31 18 Dec 2008 19 Dec 2008
9 Мурашкин И.В. (lepton) 142 150 21 342 995 47.797 37.312 30 12 Dec 2008 18 Dec 2008
10 Держальцев В.А. (MadVet) 137 146 21 333 1257 60.783 28.482 28 24 Sep 2008 06 Oct 2008
11 Зотов П.Г. (Ozzy) 141 146 21 340 264 61.111 78.826 28 29 Nov 2008 19 Dec 2008
12 Любченко В.А. (IAS56) 136 146 21 332 615 403.343 373.617 28 11 May 2008 01 Dec 2008
13 Голубин Р.С. (Roman S. Golubin) 140 145 21 335 1122 93.042 58.822 25 13 Sep 2008 06 Dec 2008
14 Nikolaenko A.V. (Shadow77) 142 147 21 339 436 77.451 14.010 23 22 Oct 2008 11 Dec 2008
15 Дроздков А.Н. (anddros) 144 116 21 345 203 4.592 1.153 21 16 Dec 2008 19 Dec 2008
16 Солдатенков Ю.С. (SolYUtor) 138 146 21 331 819 22.615 6.102 20 14 Aug 2008 23 Oct 2008
17 Белогурова К. (Katy_Ekb) 133 143 21 321 552 10.666 4.673 18 27 Nov 2008 09 Dec 2008
18 Егоров А.Б. (ABEgorov) 137 144 21 329 180 12.897 8.815 18 03 Aug 2008 12 Aug 2008
19 Войнов П.Е. (pаparome) 139 146 21 330 1125 3.124 .213 17 22 Sep 2008 11 Dec 2008
20 Тарасов Д.Б. (Gavrila) 142 145 21 338 1201 26.816 3.677 17 09 Dec 2008 19 Dec 2008

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Тотьмянин С.А. (SCAT) 50 50 94 30 124 1045 19 Dec 2008
2 >Папуша Д.А. (SQL-writer) 36 50 77 11 88 1478 19 Dec 2008
3 Мыздриков А.А. (Alex__M) 27 40 58 26 84 1393 19 Dec 2008
4 Гребнев Н.Ю. (temp123) 36 36 69 9 78 2390 16 Dec 2008
5 Черный С. (serij) 38 38 74 0 74 2649 18 Dec 2008
6 >Artamonov I. (artaman) 21 52 40 32 72 936 18 Dec 2008
7 Dileiko V.V. (Gylik) 37 37 71 0 71 2845 14 Dec 2008
8 >Rolling jewel (katy_cruel) 26 26 48 23 71 2854 19 Dec 2008
9 >Конвисар М.В. (ЙожиГ) 31 42 66 1 67 2352 19 Dec 2008
10 Kolosovsky S.V. (ymk) 29 34 60 0 60 3320 17 Dec 2008
11 >Попов А.А. (kross) 20 50 39 17 56 1308 19 Dec 2008
12 Черепова Н.А. (Natawa) 24 24 44 11 55 4101 13 Dec 2008
13 vano 1.2. (__vano__) 24 37 53 0 53 3063 19 Dec 2008
14 Сорокин В. (_Sw_) 11 54 17 30 47 959 16 Dec 2008
15 Островский Д.А. (dmito2003) 24 24 44 0 44 5325 16 Dec 2008
16 >Иванов (Ивашка) 20 47 41 0 41 1992 19 Dec 2008
17 >Bazhnev S. (silverSBN) 17 29 40 0 40 4387 19 Dec 2008
18 >Прутков К. (Козьма) 15 27 35 0 35 4959 19 Dec 2008
19 Batrakov Y. (YYBatrakov) 20 20 32 0 32 6766 17 Dec 2008
20 >Болотов С. (MementoMore) 20 20 32 0 32 6797 19 Dec 2008

Изучаем SQL

Массивы и списки в SQL Server 2005 (начало в вып.217-220)

Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )
Перевод: Моисеенко С.И.

На основании вышесказанного может показаться, что встраиваемый (inline) SQL является наиболее предпочтительным, поскольку нет никаких промежуточных таблиц, и оптимизатор имеет полную информацию. К сожалению, последнее не вполне верно. Чтобы оценить лучший способ доступа к другим таблицам в запросе, оптимизатор должен знать: 1) сколько строк будет генерировать входная строка 2) распределение значений. Но оптимизатор не в состоянии сделать это с рассмотренными в этой статье однострочными функциями, поскольку информация спрятана слишком глубоко в логике этих функций. (Имеется одно исключение: динамический SQL, который имеет собственный набор проблем, делает это менее приемлемым способом.)

Таким образом, на практике оптимизатор будет применять слепые предположения независимо от того, используете ли Вы встраиваемую однооператорную, непрозрачную однооператорную, многооператорную функцию, или табличную переменную. Так что же тогда не имеет значения, какой способ использовать? О, нет. Слепые предположения различаются для различных однооператорных методов, а если функция использует вспомогательную таблицу чисел, то размер этой таблицы повлияет на слепые предположения. (Поскольку оптимизатор имеет информацию об этой таблице, он в состоянии использовать ее.) И слепые предположения для функций CLR и XML отличаются друг от друга и от встроенных функций T-SQL. Предположения относительно многооператорных функций и табличных переменных, видимо, подобны, но отличаются от методов однооператорных функций. Может оказаться, что слепое предположение для одного метода, уводящее оптимизатор от эффективного плана, будет лучше работать с другим методом.

В целом, можно сказать, что встроенные функции T-SQL потенциально дают "более интересные" планы выполнения запросов. Ниже приведена цитата из письма, которое я получил в ответ на мою старую статью для SQL 2000:

После чтения вашей статьи 'Arrays and list in SQL server' я пробовал использовать в своем приложении метод Массива Элементов Фиксированной Длины. Все работало прекрасное, пока я не переместил код из пакета клиента в хранимую процедуру. Когда я посмотрел план выполнения запроса, то увидел, что число строк, выбираемых из таблицы Numbers, превышало 61 миллион! Вместо того, чтобы начать с соединения таблицы Numbers с исходной таблицей для фильтрации 500 строк, входящих в массив, план демонстрировал обработку предложения GROUP BY для всей таблицы (121000 строк), а затем использовал метод вложенных циклов (nested loop ) для поиска соответствий с каждой строкой в таблице Number.

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

А что насчет временных таблиц? Первоначально, когда оптимизатор компилирует хранимую процедуру, он делает слепое предположение о временной таблице. Но если во временную таблицу будет вставлено достаточное количество данных, то это вызовет срабатывание автостатистики, что, в свою очередь, вызовет перекомпиляцию операторов, которые ссылаются на временную таблицу. Эта повторная компиляция является и благом, и проклятием. Благом, потому что она дает оптимизатору второй шанс найти лучший план. Но если оптимизатор приходит в результате к тому же самому плану, который имел место до перекомпиляции, то это будет означать впустую потраченное процессорное время. В SQL 2000, где вся процедура должна была проходить повторную компиляцию, это могло обходиться действительно дорого. SQL 2005 имеет операторную перекомпиляцию, поэтому отрицательный эффект, возможно, не будет таким драматическим.

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

Ниже приведен краткий обзор возможного выбора метода для той или иной стратегии:

   Встраиваемый Непрозрачное Многооператорные  Табличные  Временные
  T-SQL           встраивание  функции           функции    функции
Итерационный
метод    No  No  Yes  Yes  Yes
CLR    No  Yes  No  Yes  Yes
XML    No  Yes  No  Yes  Yes
Таблица
чисел  Yes  No  Yes  Yes  Yes
Фиксированная
длина  Yes  No  Yes  Yes  Yes
Рекурсивный
CTE  Yes  No  Yes  Yes  Yes
Список в
SELECT  No  No  No  Yes  Yes
Динамический
SQL  Yes  No  No  No  No
Действительно
медленно Yes  No   No  No  No

Проблема кэширования встроенного SQL

Рассмотрим такую процедуру:

CREATE PROCEDURE test_sp @str nvarchar(MAX) AS
SELECT t.col1, t.col2
FROM   testtbl t
JOIN   inline_split_me(@str) c ON t.id = c.Value

Как можно догадаться по имени, inline_split_me - однооператорная (встраиваемая) функция. Тони Роджерсон, SQL Server MVP, обнаружил здесь одну проблему: план выполнения для этой процедуры не помещается в кэш, как это должно быть. Это означает, что процедура компилируется при каждом выполнении. Это плохо не только из-за затрат на ненужные компиляции: при многочисленных одновременных вызовах этой процедуры эти вызовы будут сериализованы (упорядочены), поскольку если процесс (пере)компилирует процедуру, никакой другой процесс не может ее выполнять из-за наложенных блокировок до конца компиляции.

Нет никакого повода для такого поведения; и разработчики SQL Server подтвердили мне, что это - просто баг в SQL 2005 и ничто иное.

Эта проблема проявляется только с встраиваемыми функциями T-SQL, но не с многооператорными функциями. И только тогда, когда входная переменная имеет тип данных MAX. (Или связанные типы text, ntext или image.) Если входная переменная является обычной nvarchar (4000), проблемы не возникает.

Решить проблему можно копированием входного параметра в локальную переменную:

CREATE PROCEDURE test_sp @str nvarchar(MAX) AS
DECLARE @copy nvarchar(MAX)
SELECT  @copy = @str
SELECT t.col1, t.col2
FROM   testtbl t
JOIN   inline_split_me(@copy) c ON t.id = c.Value

Попутно оказалось, оказалось, что перебрасывание данных во временную таблицу или табличную переменную здесь не помогает.

Проблемы не возникает со всеми встраиваемыми функциями в этой статье, а лишь с функциями на базе таблицы чисел и рекурсивного CTE, но не для фиксированной длины. Возможно, мне просто повезло, поэтому я призываю вас поработать с SQL Profiler, чтобы убедиться в том, что ваша процедура не подвержена этому багу. Разрешите все события для хранимых процедур. Если все в порядке, Вы должны увидеть событие CacheInsert при первом запуске вашей хранимой процедуры, а при последующих вызовах Вы должны видеть событие CacheHit. Если Вы видите CacheMiss всякий раз и ни разу CacheInsert, значит Вы столкнулись с этим багом.

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

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

§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL.
   Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.

§ Онлайновый выпуск рассылки можно почитать на сайте.

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

§ Хотите поддержать проект? Вот инструкция по применению. :-)

Контакты

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

Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"

В избранное