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

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

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Июль 2006   →
 1
01.07.2006
00:45:51
22:12:44
 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
31


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


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

Автор
Serge I

Статистика

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

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


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

http://www.sql-ex.ru

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

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

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

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

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

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

§ Проблемы на хосте еще не решены. Следующее обещание - вторник.
Я выпущу специальный выпуск, когда сайт станет полностью функционален.

§ Привел в соответствие с 17 (DML) формулировку задачи 18 (DML). Теперь и там, и там "округленное до целого", а не "с точность до".

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 LastSolved LastVisit
1 Юлдашев М.Р. (Snowbear) 138 138 20 320 338 3.989 21 Jun 2006 23 Jun 2006
2 Кувалкин К.С. (Cyrilus) 138 138 20 320 484 5.460 23 Mar 2006 23 Jun 2006
3 Slobodcicov A.N. (Testo) 138 138 20 320 252 6.716 01 Jun 2006 23 Jun 2006
4 Kamaev V.M. (Heromantor) 138 138 20 320 128 9.066 14 Mar 2006 25 Mar 2006
5 Мурашкин И.В. (lepton) 138 138 20 320 81 9.919 12 Jun 2006 22 Jun 2006
6 Бураков С.Г. (burakov58) 138 138 20 320 419 17.395 24 Mar 2006 07 Apr 2006
7 Иванов А.Н. (Goapsy) 138 138 20 320 211 18.283 09 Jun 2006 09 Jun 2006
8 Тарасов Д.Б. (Gavrila) 138 138 20 320 208 19.424 22 Mar 2006 02 Jun 2006
9 Голубин Р.С. (Roman S. Golubin) 138 138 20 320 210 20.835 16 Mar 2006 22 Jun 2006
10 Валуев Д.И. (Fiolent) 138 138 20 320 971 48.000 02 May 2006 23 Jun 2006
11 Войнов П.Е. (pаparome) 137 137 20 316 203 1.769 15 Mar 2006 15 Jun 2006
12 Зверев Д.Л. (dimzv) 137 137 20 316 869 3.278 22 Mar 2006 04 Apr 2006
13 Абашин П.И. (Dizil) 137 137 20 316 203 3.776 15 Mar 2006 22 May 2006
14 Самохвалов В. (ValdemarES) 137 137 20 316 117 7.788 14 Mar 2006 13 Jun 2006
15 Носков Н.В. (niko2) 137 137 20 316 274 8.472 06 Apr 2006 16 May 2006
16 Крижевич С.А. (yaff) 137 137 20 316 259 14.801 16 Mar 2006 05 May 2006
17 Держальцев В.А. (MadVet) 137 137 20 316 357 18.639 08 Apr 2006 15 Apr 2006
18 Зырин В.Е. (Vezyr) 137 137 20 316 63 20.618 17 Mar 2006 20 Jun 2006
19 Страшников А.С. (EffEct) 137 137 20 316 305 59.972 16 Mar 2006 02 May 2006
20 Матвеева Ю.Б. (Julia_M) 137 137 20 316 137 68.931 24 Mar 2006 24 May 2006

Изучаем SQL

Замена курсоров и циклов While

Clinton Herring (оригинал: Replacing Cursors and While Loops )
Перевод Моисеенко С.И.

Резюме

Получите большой прирост производительности, удаляя курсоры из вашего кода. Clinton Herring приводит пару примеров того, как он заменив курсоры в своем коде, снизил продолжительность обработки с 80 до 12 минут!

Введение

КУРСОРЫ и циклы WHILE. Кажется, мы не можем жить с ними и не можем жить без них, или все-таки можем?! Недавно на работе я должен был посмотреть некий рабочий код, который был написан не мной. Я тестировал производительность на новой машине (8 гигабайт RAM и 4 процессора с гипертредингом), и мы хотели сравнить ее производительность с одной из наших рабочих машин (4 гигабайта RAM и 4 более старых процессора). После обычной процедуры - создание резервных копий, восстановление, переиндексация и DBCC для 17-гигабайтной базы данных - мы решаем импортировать важное задание (job) и выполнить его. Хорошая новость заключалась в том, что, как и ожидалось, задание выполнилось значительно быстрее: 2 часа 35 минут свелись к 1 часу 19 минутам. Однако я немного оторопел, увидев, что код содержал внутренние курсоры, вложенные во внешний курсор. Для меня это БОЛЬШОЙ КРАСНЫЙ флаг, который говорит о том, что этот код выполняется не так, как следует.

Описание курсоров

Внутренние курсоры были попыткой соединить данные таблицы со стороны "многие" в связи "один ко многим" с данными в одностолбцовой таблице Output. Подход использовал цикл по всем первичным записям, задаваемым ключевым значением (1.7 миллионов записей), а затем цикл по вторичной таблице для каждой из этих первичных записей, изменяя и конкатенируя извлеченные данные для каждой из них в 3 различных переменных. Затем обновлялась таблица Output на основе совпадения с ID первичной записи, после чего процесс продолжался до тех пор, пока не были обновлены все выходные записи. Подход хорошо звучит, но является медленным - очень, очень медленным. Здесь много тормозов: объявление, открытие и перемещение по курсору, закрытие и освобождение ресурсов курсора. Я знал, что могу сделать лучше. Код курсора приведен ниже (измененный, чтобы скрыть всякую конкретную информацию о метаданных компании, где я работаю). Представлен только один внутренний курсор.

(Код объявления и начала внешнего курсора)

-- код внутреннего курсора … код только для обработки одного столбца
-- (предварительно объявляются переменные)
DECLARE temp_cursor CURSOR FOR
SELECT column_data
FROM DB.dbo.many_tbl
WHERE id = @tmp_id -- ключевые данные
ORDER BY column_data

OPEN temp_cursor

FETCH NEXT FROM temp_cursor

INTO @tmp_data

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @tmp_values = @tmp_values + convert(varchar(20), tmp_data) + ','

FETCH NEXT FROM temp_cursor
INTO @tmp_data
END

CLOSE temp_cursor
DEALLOCATE temp_cursor

UPDATE DB.dbo.OutPut_tbl
SET column_out = @tmp_values
WHERE id = @tmp_id

(Fetch next in outer Cursor)
(Close & Deallocate outer Cursor)

(Fetch next во внешнем курсоре)
(Закрытие и освобождение ресурсов курсора)

Замена внутренних курсоров

Еще в 2002 году я представил на SQL Server Central "Простую обработку таблицы" (Easy Table Pivot), и знал, что могу, по крайней мере, избавиться от внутреннего курсора (курсоров). Вот код, который непосредственно заменил вышеупомянутый внутренний курсор.

(Код объявления и начала внешнего курсора)

-- Непосредственное занесение вычисляемого столбца в переменную
-- (переменные предварительно объявлены),
SELECT @tmp_values = @tmp_values + convert(varchar(20), column_data) + ','
FROM DB.dbo.many_tbl
WHERE id = @tmp_id
ORDER BY column_data

UPDATE DB.dbo.OutPut_tbl
SET column_out = @tmp_values
WHERE id = @tmp_id

(Fetch next во внешнем курсоре),
(Закрытие и освобождение ресурсов внешнего курсора)

Замена внешнего курсора

Этот код проще и быстрее, чем курсор, и эта часть полного задания дала снижение времени выполнения с 52 минут до приблизительно 11 минут. Может ли быть что-нибудь лучше! Возможно, в SQL 7.0 Вы не сможете добиться большего, по крайней мере, я так думаю (весь вышеупомянутый код уже находился в хранимой процедуре), но что сказать о SQL2000? Анализируя вышеприведенный код, оператор select представляется подходящим кандидатом для скалярной функции. Я был уверен, что эта часть задания должна выполняться лучше, если задать в цикле единственное обновление с вызовом функции. Между прочим, я заменил курсор циклом WHILE, чтобы устранить потери времени на открытие, закрытие и освобождение ресурсов курсора. Так или иначе, вот та функция, которую я создал на основе того же самого оператора select.

CREATE function dbo.ufn_data_pivot(@id as int)
Returns varchar(20)
AS
BEGIN
DECLARE @value varchar(20)
SET @value = ''
SELECT @value = @value + convert(varchar(20), column_data) + ','
FROM DB.dbo.many_tbl
WHERE id = @id
ORDER BY column_data

Return @value
END

Запрос на обновление в основном коде, тогда будет, например, таким

UPDATE DB.dbo.OutPut_tbl
SET column_out = dbo.ufn_data_pivot(key_column)

Новые результаты

И как теперь выполняется этот код? Он буквально захлопнул двери перед старыми встроенными курсорами. Эта часть кода сократилась по времени выполнения до 2-х с небольшим минут! Примите во внимание, что у меня было три функций, вычисляющих значения для трех различных выходных столбцов, и ключевой набор данных составлял приблизительно 1.7 миллионов записей! ВОТ ТАК-ТО!

После внесения некоторых других изменений в других частях кода задания, все задание вместо 1 часа 20 минут на новой машине стало выполняться за немногим более 12 минут! Я (и еще кое-кто) были счастливы.

Заключение

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

07/04/2006

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

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

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

Контакты

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