| ← Август 2008 → | ||||||
|
1
|
3
|
|||||
|---|---|---|---|---|---|---|
|
4
|
5
|
6
|
7
|
8
|
10
|
|
|
11
|
12
|
13
|
14
|
15
|
17
|
|
|
18
|
19
|
20
|
21
|
22
|
24
|
|
|
25
|
26
|
27
|
28
|
29
|
31
|
|
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 205
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 205 (30 августа 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 54-х задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Выполнил компенсацию времени, связанную с простоем с 26-08-2008 на 27-08-2008. § Почти исправил баг форума, связанный с появлением пробельных строк в скрипте под Мозиллой/Оперой. § На форуме появились топики 777 и 778. Задач с такими номерами нет. Это - ветки для обсуждения планов развития сайта с лидерами рейтинговой системы (777) и проблем оптимизации запросов (778). Доступ к первому топику имеют те, кто перешел на третий этап, а ко второму - преодолевшие первый сертификационный порог (наличие сертификата не обязательно, но желательно :-)). § Еще один вариант решения задачи об объединении данных из двух столбцов в один от sql chuvak опубликован в виде комментария к статье. § Усилил проверку задачи 56 (сообщение Warri). § По просьбе $erges переписал рейтинг задач второго этапа. Раньше нельзя было посмотреть начавших решать новую задачу до тех пор, пока кто-нибудь помимо автора не решит ее. § К следующей рассылке планирую выставить несколько новых задач. Обновление коснется всех этапов. § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Одна задача до 3 этапа осталась: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § Число подписчиков - 3819 Число участников рейтинга - 17169 Число участников второго этапа - 1513 Число участников третьего этапа - 38 Сертифицировано на сайте - 313 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-204)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод Карасева Н.В. Возвращаясь к главному, теперь, когда мы уже увидели, насколько аналитические функции упрощают код ранжирующих вычислений, давайте оценим влияние этих функций производительность. Используем простое вычисление номера строки как основу для этого сравнения. Данные в таблице 3-7 - результаты запроса, который возвращает заказы и присваивает им номера в порядке возрастания qty с использованием ROW_NUMBER. --------------------------------------------------------------------- -- Производительность для номера строки -- ROW_NUMBER SQL Server 2005 --------------------------------------------------------------------- DROP INDEX idx_UC_orderdate_orderid ON dbo.Orders CREATE UNIQUE CLUSTERED INDEX idx_UC_qty_orderid ON dbo.Orders(qty,orderid); SELECT orderid, qty, ROW_NUMBER () OVER (ORDER BY qty) AS rownum FROM dbo.Orders ORDER BY qty; GO --------------------------------------------------------------------- Orderid qty rownum 30001 10 1 10001 10 2 10006 10 3 40005 10 4 30003 15 5 30004 20 6 20002 20 7 20001 20 8 10005 30 9 30007 30 10 40001 40 11 Таблица 3-7. Номера строк, упорядоченных по qty  Рисунок 3-1. План выполнения для вычислений номера строки при помощи функции ROW_NUMBER, реализованной в SQL Server 2005 Когда мы смотрим план исполнения на рисунке 3-1, мы замечаем, что листовой уровень кластерного индекса сканируется упорядоченно. Оптимизатору нужны данные отсортированные сначала по столбцам группы (partition), а затем - по столбцам сортировки. Так как у нас имеется индекс, мы замечаем, что нашему плану не нужен оператор сортировки. Оператор Sequence Project вычисляет ранговые значения. Для каждой строки этот оператор полагается на 2 флага, обеспечиваемых предыдущими операторами, один флаг соответствует проверке, не является ли текущая запись первой в группе (partition), и если это так, то он сбросит ранговое значение. Другой флаг проверяет отличие сортируемого значения данной строки от значения в предыдущей строке, и если это так, то оператор инкрементирует ранговое значение на основании этой функции. План весьма эффективн и сканирует данные только один раз, и если данные ещё не упорядочены по индексу, то сортирует их. Теперь давайте сравним его с вариантом запроса для SQL Server 2000. Для простоты в примере вычисляется номер строки по единственному уникальному столбцу - orderid: --------------------------------------------------------------------- -- Производительность для номера строки -- ROW_NUMBER SQL Server 2000 --------------------------------------------------------------------- SELECT orderid, (SELECT COUNT (*) FROM dbo.Orders AS O2 WHERE O2.orderid <= O1.orderid) AS rownum FROM dbo.Orders AS O1 ORDER BY orderid; GO ---------------------------------------------------------------------  Рисунок 3-2. План выполнения для вычисления номера записи в SQL Sqerver 2000 Этот метод гораздо медленнее чем функция ROW_NUMBER. Это видно по плану выполнения на рисунке 3-2. Сначала выполняется полное сканирование по индексу на orderid , и для каждой возвращаемой строки оператор вложенных циклов (nested loop) генерирует номера строк путом их подсчета. Каждое вычисление номера строки вызывает оператор поиска (seek) в индексе по orderid, с последующим частичным сканированием с головы связанного списка листового уровня до последней точки, где orderid из внутреннего запроса меньше или равен значению orderid внешнего запроса. Обычно одним из главных факторов, влияющих на производительность запросов манипуляции данными, является ввод-вывод. Как было показано ранее, для вычисления номеров n строк данные будут сканироваться 1+2+3+….+n = (n+ n2)/2 раз. Проблемы не возникнут сразу, но для таблицы в 100.000 строк полное число сканированных строк составит порядка 5 миллиардов! При отсутствии индекса для того, чтобы вычислить номер каждой строки, нужно просмотреть всю таблицу целиком. Полное число просканированных строк в этом случае будет порядка n2. Падение производительности растет экспоненциально. Для той же таблицы в 100.000 строк без индекса запрос завершится после сканирования 10 миллиардов записей. Теперь давайте посмотрим на решение, основанное на курсорах. Вообще говоря, работы с курсорами следует избегать, поскольку это связано с большими накладными расходами, сказывающимися на производительности. Однако в данном случае, если размер разбиения не слишком маленький, основанное на курсорах решение выполняется лучше предыдущего, поскольку данные просматриваются всего один раз. Это означает, что по мере того, как таблица растет, решение, основанное на курсорах имеет линейное ухудшение производительности, в отличие от эспоненциального ухудшения (n2) для предыдущего решения. ---------------------------------------------------------------------
-- Вычисление номера строки
-- Решение с помощью курсора SQL Server 2000
---------------------------------------------------------------------
DECLARE @OrdersRN TABLE(RowNum INT, Orderid INT, qty INT);
DECLARE @RowNum AS INT, @OrderID INT, @qty INT;
DECLARE rncursor CURSOR FAST_FORWARD FOR
SELECT orderid, qty
FROM dbo.Orders ORDER BY qty;
OPEN rncursor;
SET @RowNum = 0;
FETCH NEXT FROM rncursor INTO @orderid, @qty;
WHILE @@fetch_status = 0
BEGIN
SET @RowNum = @RowNum + 1;
INSERT INTO @OrdersRN(RowNum, orderid, qty)
VALUES(@RowNum, @OrderID, @qty);
FETCH NEXT FROM rncursor INTO @OrderID, @qty;
END
CLOSE rncursor;
DEALLOCATE rncursor;
SELECT orderid, qty, RowNum FROM @OrdersRN;
GO
---------------------------------------------------------------------
Наконец, давайте посмотрим на вычисление номера записи с помощью функции IDENTITY. ---------------------------------------------------------------------
-- Производительность: Вычисление номера записи
-- Использование Identity SQL Server 2000
---------------------------------------------------------------------
-- SELECT INTO without ORDER BY
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
DROP TABLE #Orders;
GO
SELECT IDENTITY(int, 1, 1) AS RowNum,
orderid + 0 AS orderid, qty
INTO #Orders
FROM dbo.Orders;
GO
-- CREATE TABEL w/IDENTITY, INSERT SELECT … ORDER BY
DECLARE @OrdersRN TABLE( OrderID INT, qty INT,RowNum INT IDENTITY);
INSERT INTO @OrdersRN(OrderID, qty)
SELECT OrderID, qty
FROM dbo.Orders
ORDER BY qty;
SELECT * FROM @OrdersRN;
GO
Использование функции IDENTITY в операторе SELECT INTO будет самым быстрым способом вычисления номера записи в SQL Server до версии 2005, потому что данные сканируются только один раз и отсутствуют накладные расходы, связанные с использованием курсоров. В добавок SELECT INTO минимально журнализируемая операция, если не используется полная модель восстановления (FULL recovery model). Однако этот способ можно использовать только если порядок назначения номера строки не важен, как в нашем примере. Если номера строк опираются на заданный порядок, SELECT INTO использовать не следует. Сначала нужно создать таблицу, и только потом её заполнять. Подводя итог, можно смело утверждать, что для вычисления ранговых значений функция ROW_NUMBER с предложением OVER гораздо быстрее любого метода имеющегося в арсенале SQL Server 2000. Оно не только быстрее, но исключительно понятно и просто. (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
