| ← Сентябрь 2008 → | ||||||
|
1
|
2
|
3
|
4
|
5
|
7
|
|
|---|---|---|---|---|---|---|
|
8
|
9
|
10
|
11
|
12
|
14
|
|
|
15
|
16
|
17
|
18
|
19
|
21
|
|
|
22
|
23
|
24
|
25
|
26
|
28
|
|
|
29
|
30
|
|||||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 209
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 209 (27 сентября 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 51-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Реанимировали работу быстрых клавиш на страницах с упражнениями: § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Одна задача до 3 этапа осталась: § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3845 Число участников рейтинга - 17938 Число участников второго этапа - 1554 Число участников третьего этапа - 38 Сертифицировано на сайте - 320 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-208)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод: Карасева Н.В. Максимум параллельных сессийЗадача максимума параллельных сессий является ещё одним примером вычислений на упорядоченных данных. Вы записываете данные пользовательских сессий для разного рода приложений в таблицу Sessions. Выполните следующий запрос, для того чтобы создать и заполнить таблицу Sessions. USE tempdb;
GO
IF OBJECT_ID('dbo.Sessions') IS NOT NULL
DROP TABLE dbo.Sessions;
GO
CREATE TABLE dbo.Sessions
(
keycol INT NOT NULL IDENTITY PRIMARY KEY,
app VARCHAR(10) NOT NULL,
usr VARCHAR(10) NOT NULL,
host VARCHAR(10) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
CHECK(endtime > starttime)
);
INSERT INTO dbo.Sessions
VALUES('app1', 'user1', 'host1', '20030212 08:30', '20030212 10:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user2', 'host1', '20030212 08:30', '20030212 08:45');
INSERT INTO dbo.Sessions
VALUES('app1', 'user3', 'host2', '20030212 09:00', '20030212 09:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user4', 'host2', '20030212 09:15', '20030212 10:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user5', 'host3', '20030212 09:15', '20030212 09:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user6', 'host3', '20030212 10:30', '20030212 14:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user7', 'host4', '20030212 10:45', '20030212 11:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user8', 'host4', '20030212 11:00', '20030212 12:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user8', 'host1', '20030212 08:30', '20030212 08:45');
INSERT INTO dbo.Sessions
VALUES('app2', 'user7', 'host1', '20030212 09:00', '20030212 09:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user6', 'host2', '20030212 11:45', '20030212 12:00');
INSERT INTO dbo.Sessions
VALUES('app2', 'user5', 'host2', '20030212 12:30', '20030212 14:00');
INSERT INTO dbo.Sessions
VALUES('app2', 'user4', 'host3', '20030212 12:45', '20030212 13:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user3', 'host3', '20030212 13:00', '20030212 14:00');
INSERT INTO dbo.Sessions
VALUES('app2', 'user2', 'host4', '20030212 14:00', '20030212 16:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user1', 'host4', '20030212 15:30', '20030212 17:00');
CREATE INDEX idx_app_st_et ON dbo.Sessions(app, starttime, endtime);
Для каждого приложения требуется вычислить максимальное количество сессий, открытых в один и тот же момент времени. Такие расчеты необходимы для определения стоимости типа лицензии на обслуживание, зависящей от максимального числа параллельных сессий. Попытаемся решить задачу основываясь на теоретико-множественном подходе; затем оптимизируем решение; и после этого оценим его потенциальную производительность. Ниже я буду рассматривать решение в курсорах и приведу сравнительную таблицу для решения в рамках теоретико-множественного подхода и решения посредством курсора. Один из путей решения состоит в создании вспомогательной таблицы, содержащей все возможные моменты времени из рассматриваемого временного интервала. Затем выполняется подзапрос для вычисления количества активных сессий для каждого такого момента времени, потом создается производная таблица/CTE из таблицы с результатами, и, наконец, выполняется группировка строк производной таблицы по приложениям с подсчетом максимуммального числа параллельных сессий для каждого приложения. Такое решение весьма неэффективно. Даже если вы создадите оптимальный индекс для него - один на (app, starttime, endtime) - полное число строк, которое сканирует запрос только на листовом уровне индекса, огромно. Оно равно числу строк во вспомогательной таблице, умноженному на среднее количество активных сессий в каждый момент времени. Для того чтобы почувствовать, насколько громоздка эта задача, представьте, что вам нужно выполнить расчет для месяца рабочей деятельности. Тогда число записей во вспомогательной таблице будет: 31(дни) x 24(часы) x 60(минуты) x 60(секунды) x 300 (интервалы в пределах секунды). Теперь умножьте результат этого вычисления на среднее число активных сессий в каждый момент времени (скажем, 20 сессий), и вы получите 16.070.400.000. Конечно, возможности для оптимизации есть. Будут периоды, в течение которых число одновременных сессий не меняется, поэтому для них подсчеты не нужны. Количество меняется, когда стартует новая сессия (число сессий увеличивается на 1) или завершается существующая сессия (число уменьшается на 1). Более того, поскольку в момент начала сессии число сессий увеличивается, а при завершении - уменьшается, именно моменты начала сессий будут кандидатами на момент, в который число сессий достигнет искомого максимума. Наконец, если 2 сессии открываются в одно и то же время, то нет нужды подсчитывать количества для каждой из них. Таким образом, вы можете использовать предложение DISTINCT в запросе, который вернет вам начальные моменты для каждого приложения, хотя при уровне точности 3 1/3 миллисекунды (ms), число дубликатов было бы очень мало- пока вы не имеете дело с очень большим количеством данных. Короче говоря, вы можете просто использовать в качестве вспомогательной таблицы производную таблицу или СТЕ, которая возвращает все различные времена начала сессий для каждого приложения. С этого момента все, что вам нужно сделать, - следовать логике, приведенной ранее. Вот оптимизированное решение, основанное на теории множеств: SELECT app, MAX(concurrent) AS mx
FROM (SELECT app,
(SELECT COUNT(*)
FROM dbo.Sessions AS S2
WHERE S1.app = S2.app
AND S1.ts >= S2.starttime
AND S1.ts < S2.endtime) AS concurrent
FROM (SELECT DISTINCT app, starttime AS ts
FROM dbo.Sessions) AS S1) AS C
GROUP BY app;
App mx
app1 4
app2 3
Заметьте, что вместо использования предиката BETWEEN для определения активности сессии в момент времени (ts), я использовал ts >= starttime и ts < endtime. Если сессия заканчивается в момент ts, то я не рассматриваю ее как активную. План выполнения этого запроса показан на рис.4-1. 
Рисунок 4-1. Вычисление максимума одновременных сессий. План для запроса, основанного на теоретико-множественном подходе Во-первых, сканируется индекс, который я создал (app, starttime, endtime), и убираются дубликаты (оператором Stream Aggregate). Если таблица не очень большая, вы можете предположить, что число возвращаемых строк будет очень близко к числу строк в таблице. Для каждого app, starttime (обозначенного как ts) возвращенных после удаления дубликатов, начинает выполняться оператор Nested Loops, в котором вычисляется количество активных сессий (поиском по индексу с последующим частичным сканирования для подсчета активных сессий). Число страниц, прочитанных при каждой итерации Nested Loops, будет равно числу уровней в индексе плюс число страниц занимаемых активными сессиями. Для достижения свой цели я сосредоточусь на числе строк, просмотренных на листовом уровне (коррелирующий подзапрос - прим. перев.), потому что это число изменяется в зависимости от числа активных сессий. Конечно, для получения адекватных оценок производительности, вы должны учесть количество страниц (логических чтений) наряду с многими другими факторами. Если у вас N записей в таблице, то в предположении, что большая часть из них имеет уникальные значения app, starttime, и в любой момент времени у вас O перекрывающихся сессий, вы получите в итоге N х O строк, просканированных на листовом уровне, не считая сканирования страниц операциями поиска (seek) в процессе перехода к листовому уровню. Теперь посмотрим, как масштабируется это решение с ростом таблицы. Как правило, такие отчеты нужны периодически - например, раз в месяц, за последний месяц. При наличии рекомендованного индекса, производительность решения не должна меняться пока не увеличится месячный траффик - то есть составлять N x O (где N - число записей за последний месяц). Но предположим, что вы предвидите увеличение траффика в f раз? Если траффик вырастает в f раз, то как число строк, так и число активных сессий в заданный момент времени, вырастут в f раз; в итоге число строк, просмотренных на уровне листьев, будет равно (N x f)x (O x f) = N x O x f2. Вы видите, что с ростом траффика производительность падает не линейно; она ухудшается более радикально. (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
