| ← Февраль 2009 → | ||||||
|
1
|
||||||
|---|---|---|---|---|---|---|
|
2
|
3
|
4
|
5
|
6
|
8
|
|
|
9
|
10
|
11
|
12
|
13
|
15
|
|
|
16
|
17
|
18
|
19
|
20
|
22
|
|
|
23
|
24
|
25
|
26
|
27
|
||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 231
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 231 от 28 февраля 2009 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 50-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Вчера во время подготовки рассылки исчез из сети наш хостер - netter.ru. Причины неизвестны, т.к. связи с ними нет. § По многочисленным просьбам сделал пояснение к основному рейтингу TOP 100. Собираюсь сделать то же самое и для других рейтингов. § Fiolent добавил подсказку для задачи 75. Также написаны подсказки для задач 40 и 68, и дополнены подсказки для задач 41 и 125. § Дважды произошла смена лидера. § Продвинулись в рейтинге: § Новые лица в ТОР 100 и вернувшиеся туда: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3825 Число участников рейтинга - 19359 Число участников второго этапа - 1731 Число участников третьего этапа - 46 Сертифицировано на сайте - 354 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLМассивы и списки в SQL Server 2005 (начало в вып.217-229)Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )Перевод: Моисеенко С.И. Очевидной проблемой этой функции является надежность. Разделенный запятыми список, который содержит более миллиона символов - очень длинная строка, но рано или поздно она все же упрется в предел. И в таком случае эта функция возвратит неправильные результаты при отсутствии какого-либо сообщения об ошибке. Другая проблема - производительность. Как я уже отметил, charindex медленнее работает на nvarchar (MAX), чем на nvarchar (4000). И что совсем плохо, там выполняется сравнение строк посимвольно. В результате получается, что эта встроенная функция вдвое медленнее многооператорной табличной функции с разбиениями, которую мы рассмотрим в следующем разделе. Хотя вся история производительности inline_split_me является намного более сложной и весьма запутанной. Видите ли, когда я выполнял свои тесты производительности, то заметил, что я получал параллельный план при соединении с моей тестовой таблицей. Для коротких списков параллелизм, скорее, увеличивал нагрузку. Но на самых длинных тестовых строках с 10 000 элементов inline_split_me неожиданно выполнялась быстрее, чем CLR на машине с 4 центральными процессорами. (Полную информацию о тестах вы найдете в приложении.) Получите вы параллельный план или нет, зависит от большого количества условий, но размер входного параметра входа не является одним из них. Независимо от входной строки оптимизатор предположит, что первое условие в WHERE уберет 30 % строк в таблице Numbers, то есть 300 000 в нашем случае, а второе условие для подстроки уменьшит предполагаемое число строк приблизительно до 9 500. Видимо, это в сочетании с моей тестовой таблице и привело к параллельному плану. Отсюда следует, что если входной параметр роли не играет, то значение имеет размер Numbers. Еще имеет значение подобные мелкие детали: Number <= convert(int, len(@param)) Для типов данных MAX функции len и datalength возвращают bigint. Поскольку Numbers.Number есть int, этот столбец должен быть неявно преобразован без использования convert на len. Оптимизатор тогда решил бы это с функцией RangeSeek подобно тому, что имело место, когда varchar преобразуется к nvarchar. Само преобразование не является дорогостоящим в этом случае, но по некоторым причинам, это не дает оптимизатору выбрать параллельный план./p> Каков практический вывод из всего этого? В большинстве случаев Вы должны избегать использования inline_split_me. Она может подойти только для длинного входного параметра, когда Вы, вероятно, извлечете пользу в результате параллельного выполнения запроса. И если Вы рассчитываете на многочисленные процессы, вызывающие вашу хранимую процедуру, которая использует inline_split_me, вряд ли Вы захотите, чтобы все они пытались использовать все центральные процессоры одновременно. Таким образом, если Вы рассматриваете возможность использования inline_split_me в целях оптимизации, необходимо протестировать ее с вашим запросом и посмотреть, какую производительность Вы получите. Есть еще одна проблема с производительностью inline_split_me. Процедуры, которые используют эти функции, вероятно, будут подвержены ошибкам, которые я обсуждал в разделе "Проблемы кэширования однострочных функций T-SQL". Есть способ сделать эту функцию неизменно быстрой: если Вы знаете, что ваша входная строка всегда будет вписываться в nvarchar (4000), то можете использовать этот тип данных для входного параметра. Это также решило бы проблему кэширования. Но, конечно, функция стала бы не очень надежной, и именно поэтому я не выполнял никаких тестов с nvarchar (4000) для входного параметра. Многооператорная функция с разбиениямиКак и в случае с итерационным методом, разбиение входной строки на куски по nvarchar (4000) улучшает производительность. Вот многооператорная функция, которая делает это: CREATE FUNCTION duo_chunk_split_me(@list nvarchar(MAX),
@delim nchar(1) = N',')
RETURNS @t TABLE (str nvarchar(4000) NOT NULL,
nstr nvarchar(4000) NOT NULL) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int
SELECT @textpos = 1, @maxlen = 4000 - 2
WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @stoppos = @maxlen -
charindex(@delim COLLATE Slovenian_BIN2,
reverse(@slice))
INSERT @slices (slice)
VALUES (@delim + left(@slice, @stoppos) + @delim)
SELECT @textpos = @textpos - 1 + @stoppos + 2
-- On the other side of the comma.
END
INSERT @slices (slice)
VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)
;WITH stringget (str) AS (
SELECT ltrim(rtrim(substring(s.slice, N.Number + 1,
charindex(@delim COLLATE Slovenian_BIN2,
s.slice, N.Number + 1) -
N.Number - 1)))
FROM Numbers N
JOIN @slices s
ON N.Number <= len(s.slice) - 1
AND substring(s.slice, N.Number, 1) = @delim COLLATE Slovenian_BIN2
)
INSERT @t (str, nstr)
SELECT str, str
FROM stringget
RETURN
END
Мы сначала разбиваем текст на куски и помещаем их в табличную переменную @slices. Когда мы создавали куски для итерационного метода, то не беспокоились, если элемент списка расщеплялся по двум кускам. Но для этого метода, мы должны принять меры предосторожности, чтобы избежать такой ситуации, и мы должны проверять, что последний символ в куске - разделитель. Сначала мы получаем предварительный кусок максимальной длины, которую мы можем обработать. Затем находим последний разделитель в этом куске с помощью функции charindex, которая применяется к результату функции reverse, - небольшой трюк. Когда мы вставляем кусок в табличную переменную, то проверяем, что разделитель имеется и до, и после, так что нам не придется заниматься этим позже. Следует отметить, что если текст на входе укладывается в рамки обычного nvarchar, мы не входим в цикл, а просто вставляем текст непосредственно в таблицу @slices. Как только @slices заполнена, мы применяем логику встроенной функции, хотя и несколько отличную, поскольку, как мы знаем, все строки в @slices начинаются и заканчиваются разделителем. Теперь числа, отфильтрованные через JOIN, есть положения разделителей, и элементы начинаются на одну позицию вперед. Отметим, что нам не нужны итерации по @slices; мы можем выполнить непосредственное соединение с Numbers. То, что начинается с WITH, есть Общее Табличное Выражение. Здесь мы лишь используем его как макрос, т.е. не повторяя сложное выражение, в котором мы извлекаем строку. Далее еще будут рассматриваться общие табличные выражения. Как и в случае с iter_charlist_to_table, эта функция возвращает таблицу с двумя столбцами типов varchar и nvarchar. Мои тесты показали, что стоимость будет на 10-15 % выше для возвращаемой таблицы, если она имеет только один столбец nvarchar. Эта функция в большинстве случаев значительно быстрее, чем inline_split_me, вплоть до 2 раз. Как я обсуждал в предыдущем разделе, inline_split_me может оказаться очень быстрой при параллельном плане и длинным входным параметром. Прекрасно, что при работе с могооператорной функцией с разбиениями Вы получаете согласующуюся производительность. По сравнению с CLR duo_chunk_split_me в моих тестах был на 25-50 % медленнее только на распаковке списка в таблицу. Отметим, что эта функция не имеет проблем с надежностью как у встроенной функции. Посредством разбиения мы защищаемся от больших чисел, лишь бы у нас было 4000 чисел в таблице. Хорошо, но если Мы получаем больше кусков, чем имеется чисел в таблице, то у нас проблема. Но миллион чисел в таблице будет поддерживать входную длину около четырех миллиардов символов, что намного превышает размер для nvarchar (MAX). (Продолжение следует...) Полезная информация§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. :-) КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
