Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 234
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 234 от 21 марта 2009 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 50-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Ростовчан приглашаем посетить семинар, посвященный SQL Server 2008 Express. § Новая задача от anddros выставлена на третьем этапе под номером 154 (сложность 4 балла). § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3830 Число участников рейтинга - 19592 Число участников второго этапа - 1747 Число участников третьего этапа - 47 Сертифицировано на сайте - 359 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLМассивы и списки в SQL Server 2005 (начало в вып.217-229, 231-233)Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )Перевод: Моисеенко С.И. Передача чисел в двоичном видеМетод элементов списка фиксированной длины представляет собой еще один метод для передачи списка чисел, если представить его в виде бинарной строки: списка чисел, если представить его в виде бинарной строки:
CREATE FUNCTION fixbinary_single(@str varbinary(MAX))
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,
n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4))
FROM Numbers n
WHERE n.Number <= datalength(@str) / 4 )
(Должен ли я добавить, что эта идея также принадлежала Стиву Кассу?) При вызове из T-SQL она выглядит менее привлекательной: CREATE PROCEDURE get_product_names_binary @ids varbinary(2000) AS SELECT P.ProductID, P.ProductName FROM Northwind..Products P JOIN fixbinary_single(@ids) b ON P.ProductID = b.n go EXEC get_product_names_binary 0x00000090000000C0000001B00000025 В моих тестах производительность fixbinary_single и fixstring_single была фактически идентичной. Однако, Алекс Кузнецов (Alex Kuznetsov), который прислал мне подобное предложение, указал, что таким способом Вы экономите на пропускной способности сети - аспект, который мной не тестировался. Вместо передачи 10 байтов на число, поскольку нам требуется строка, которая соответствовала бы всем положительным целым числам, бинарные строки требуют только четыре байта на число. Это может оказаться существенным выигрышем для длинных списков. Чтобы использовать эту возможность, Вы должны в коде клиента преобразовать ваши целые числа в массив байтов. Алекс любезно предоставил мне функцию C#, которая делает это для типа данных bigint. static byte[] UlongsToBytes(ulong[] ulongs) {
int ifrom = ulongs.GetLowerBound(0);
int ito = ulongs.GetUpperBound(0);
int l = (ito - ifrom + 1)*8;
byte[] ret = new byte[l];
int retind = 0;
for(int i=ifrom; i<=ito; i++)
{
ulong v = ulongs[i];
ret[retind++] = (byte) (v >> 0x38);
ret[retind++] = (byte) (v >> 0x30);
ret[retind++] = (byte) (v >> 40);
ret[retind++] = (byte) (v >> 0x20);
ret[retind++] = (byte) (v >> 0x18);
ret[retind++] = (byte) (v >> 0x10);
ret[retind++] = (byte) (v >> 8);
ret[retind++] = (byte) v;
}
return ret;
}
Заметим, что fixbinary_single не использует параметр @itemlen, который здесь оказывается лишним. Для обработки чисел других целочисленных типов потребуется отдельная функция. Конечно, fixbinary_multi легко написать, но я оставлю это в качестве упражнения для читателя. Фиксированная длина и CLRПоскольку спецификой этого метода является формат входного параметра, а не сам алгоритм, Вы могли бы использовать для форматирования функцию CLR. Я написал табличнозначную функцию CLR, которая использует на входе элементы фиксированной длины. Было отмечено незначительное различие в производительности по сравнению с другими функциям CLR. Производительность в случае очень длинного входного параметраВ начале этого раздела я говорил, что метод фиксированной длины является самым быстрым методом, за исключением случая очень длинного входа. Мои тесты на одной машине показали, что метод на основе фиксированной длины отстал от нескольких других методов, когда на вход подавался список из 10 000 строк. Это озадачивало меня в течение долгого времени, пока я как-то не запустил некоторые специальные тесты. Я пришел к заключению, что SQL Server имеет особенность в обработке nvarchar (MAX), которая проявляется в замедлении обработки, наступающей выше определенного предела. Этот предел - приблизительно 500 000 байтов на машинах x64, и 750 000 байтов на 32-разрядных машинах. (Я не имею доступа к какой-нибудь машине с архитектурой IA64, поэтому не могу оценить предельное значение для этого случая). Когда длина входа превышает этот предел, время выполнения функций фиксированной длины увеличивается примерно вдвое. Я наблюдал эту проблему только в моих тестах с фиксированной длиной, поскольку только для фиксированной длины генерировались такие длинные входные строки. Однако и другие встраиваемые функции T-SQL, подобные inline_split_me, естественно, также подвержены этому явлению. С другой стороны, на процедуры, применяющие разбиение входной строки на куски, это оказывает влияние в меньшей степени, так как они выполняют меньше действий со строками большой длины. Проблема не возникает вообще, когда используются не SQL-методы: CLR обрабатывает длинные строки в .Net Framework, а XML также имеет собственный внутренний обработчик. Я более подробно обсуждаю этот предел в приложении, посвященном производительности. Альтернативный метод заполнить таблицу NumbersЗапрос, который я приводил в качестве примера заполнения таблицы Numbers, достаточно прост для понимания, но не очень эффективен. Поскольку вам предстоит это сделать всего один раз, производительность не является большой проблемой. Тем не менее, приведу блестяще быстрый запрос, который я заимствовал из книги Ицика Бена-Гaн (Itzik Ben-Gan) Inside SQL Server 2005: T-SQL Programming (SQL Server 2005 изнутри: Программирование в T-SQL): CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS RETURN WITH L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) SELECT n FROM Nums WHERE n <= @n; GO INSERT Numbers(Number) SELECT fn_nums(1000000) Если, как предполагает Ицик, создание таблиц не находится в пределах ваших полномочий, Вы можете использовать функцию непосредственно в вашем запросе. Результаты тестов показали, что чем больше вам нужно чисел, тем больше затраты, связанные с этой функцией. Для функции типа chunk_split_me, которая никогда не требует более 8000 чисел, вообще не было замечено никакого существенного различия. Для fixstring_single, которая для 10 000 30-символьных элементов требует 300 000 чисел, падение производительности составило 25 %, что представляется удивительно низким. Есть риск при использовании этой встраиваемой функции с методом, который является также встраиваемым: когда я запускал тест с fixstring_single на SQL 2008, выполняя соединение с некластерным индексом моей тестовой таблицы, оптимизатор полностью потерялся и привел к плану, который выполнялся несколько минут всего лишь с 20 элементами в списке. На 2005 SQL я получил нормальный и быстрый план, но следует иметь в виду, что для вашего вопроса, это может оказаться и не так. Замечание. В приложение не включены никакие данные об использовании этой функции вместо таблицы Numbers, поскольку я добавил эту функцию в статью уже спустя некоторое время после начальной публикации, и у меня уже нет доступа ко всем испытательным машинам, на который изначально выполнялись тесты. (Продолжение следует...) Полезная информация§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. :-) КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
