|
|
-
В этом посте хочу рассказать об одной приятной особенности механизма работы UNION в SQL Server, позволяющей писать масштабируемые запросы, удовлетворяющие порой довольно сложным требования заказчиков. Чтобы лучше понять ситуацию, представьте себе такой кусочек системы (американская доска объявлений): таблица штатов - States, таблица городов - Cities (связана со штатом), таблица объявлений - Ads (связана со штатом и с городом) (скрипты создания таблиц и выборки я привожу в материалах поста). Теперь заказчик хочет, чтобы при просмотре объявлений, если выбран город, отображать в списке сначала объявления города, потом объявления других городов этого штата, потом объявления привязанные к штату, но не привязанные к городу, и наконец, объявления не связанные ни со штатом ни с городом. У этого требования было несколько решений, и каждое из которых приходилось в корне менять, т.к. при росте нагрузки оно становилось узким местом системы. И наиболее удачным решением оказалось использование UNION (а точнее UNION ALL). Запрос (выборка объявлений с пейджированием), в этом случае, выглядит примерно так: SELECT Id, Title FROM
(
SELECT TOP(@RowCount) Id, Title, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS 'row_num'
FROM
(
SELECT TOP(@RowCount) Id, Title FROM Ads
WHERE StateId = @StateId AND CityId = @CityId
AND StateId IS NOT NULL AND CityId IS NOT NULL
UNION ALL
SELECT TOP(@RowCount) Id, Title FROM Ads
WHERE StateId = @StateId AND CityId != @CityId
AND StateId IS NOT NULL AND CityId IS NOT NULL
UNION ALL
SELECT TOP(@RowCount) Id, Title FROM Ads
WHERE StateId = @StateId AND StateId IS NOT NULL
AND CityId IS NULL
UNION ALL
SELECT TOP(@RowCount) Id, Title FROM Ads
WHERE StateId IS NULL AND CityId IS NULL
ORDER BY Id DESC
) s
) s2
WHERE s2.row_num > (@RowCount - @ItemsPerPage)
(Скрипты создания БД, наполнения БД, запросов и плана выполнения я привожу в материалах поста. БД не является копией продашн БД, скорее неким макетом, в котором присутствуют элементы, позволяющие продемонстрировать функционал)
Теперь о том, что же, собственно, хорошего в этом решении. Взгляните на фрагмент сгенерированного плана выполнения:
Прелесть всего этого в том, что в случае, когда выборка попадает в количество строк, указанное в TOP, то все последующие выборки, указанные в UNION ALL не выполняются. Т.е. они не возвращают данных, которые затем подвергаются какой-то обработке (в данном случае – возову функции ROW_NUMBER). Таким образом получаем довольно неплохой шаблон решения подобного рода задач.
Материалы поста:
- UNION, особенности работы
Ссылки по теме:
- UNION (Transact-SQL)
- Правила использования оператора UNION
- Объединение результирующих наборов с помощью оператора UNION
 Денис Резник LPP Soft, .Net Team Lead
Харьков, Украина
|
-
-
"Какая классная штука Management Data Warehouse" – в который раз подумал я, собираясь идти домой в пятницу… Теперь немного предыстории. В пятницу вечером, сразу после встречи UNETA пришлось ехать в офис, т.к. заказчик обрывал трубку и "просил" сделать, чтобы сайт не тормозил. Делать нечего, тем более, что лучше сразу, чем потом на выходных. Еду в офис и первым делом открываю сайт. Таки да, тормозит. На сервере БД такая картина: проц стабильно загружен на 100%, собственно поэтому сайт так медленно работает. Activity Monitor не запускается, профайлером ловить долго. И, в который раз, я порадовался, что в своё время уговорил заказчика, что нам нужно настроить на сервере MDW. В течении 2-х минут я определил виновника. Им оказался запрос на подсчёт данных (этим же днём по требованию заказчика был изменён алгоритм, но при небольшой нагрузке его работы не было заметно, зато при росте нагрузки на сервер, простой COUNT скушал все ресурсы). Ещё в течении 20 мин оптимизировал запрос. "Всё таки класная штука Management Data Warehouse" - в который раз подумал я, собираясь идти домой. Впереди были спокойные выходные… Ссылки по теме: - Data Collection - Management Data Warehouse - Обзор Performance Studio
Денис Резник LPP Soft, .Net Team Lead Харьков, Украина
|
-
-
Довольно часто приходится слышать то, что различие между табличными переменными и временными таблицами заключается в том, что первые всегда хранятся в памяти, а последние в tempdb. Но на самом деле это миф. И временные таблицы, и табличные переменные, создаются в tempdb (так как обязательно должно быть постоянное хранилище данных, на случай, если памяти всё же не хватит). Убедиться в том, что табличные переменные создаются в tempdb так же, как и временные таблицы, можно проделав вот такой опыт: SELECT COUNT(*) FROM tempdb.sys.tables;
GO
DECLARE @table_var table(id int);
SELECT COUNT(*) FROM tempdb.sys.tables;
GO
Результат выполнения 2-го COUNT(*) будет на 1 больше чем первого. Следовательно, в результате объявления табличной переменной в tempdb создалась таблица, и в способе хранения данных они не особо отличаются. Давайте теперь посмотрим на отличия табличных переменных от временных таблиц:
Преимущества:
- Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены
- При использовании в хранимых процедурах табличных переменных приходится прибегать к рекомпиляциям реже, чем при использовании временных таблиц
- Транзакции с использованием табличных переменных продолжаются только во время процесса обновления соответствующих табличных переменных. Поэтому табличные переменные реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации
- Табличной переменной можно присвоить результат выполнения табличной функции, для повторного использования результатов
- Табличную переменную можно передавать как параметр в хранимую процедуру (SQL Server 2008)
Недостатки:
- На табличных переменных нельзя создавать некластерные индексы
- Табличные переменные не содержат статистику
- Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
- Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса
Общие рекомендации Microsoft, относительно использования табличных переменных таковы: "Используйте их везде, где это возможно, кроме тех случаев, когда у вас хранятся значительные объёмы данных, и присутствует повторное использование таблиц". Кроме того, отмечено, что сценарии использования могут быть разными, и в каждом конкретном случае нужно смотреть и пробовать что вам больше подходит.
Ссылки по теме:
- Табличные переменные (MSDN)
- Временные таблицы
- FAQ по использованию табличных переменных
- Блог Kimberly Tripp
- Блог Ken Simmons
- Блог Pinal Dave
Денис Резник
LPP Soft, .Net Team Lead
Харьков, Украина
|
-
-
-
Недавно один из членов моей комманды задал мне вопрос: "Почему генерация случайных чисел в .Net упорно выдаёт не рэндомные значения, а повторяющиеся?". И последовательный вызов вот такого метода, всегда генерирует одну и ту же последовательность чисел: 1: const string AllowableCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
2:
3: public static string GenerateRandomString(int length)
4: {
5: string randomString = String.Empty;
6: Random random = new Random();
7: for (int i = 0; i < length; i++)
8: randomString += AllowableCharacters[random.Next(AllowableCharacters.Length)];
9:
10: return randomString;
11: }
Случай меня заинтересовал, и я полез в рефлектор чтобы понять причину такого поведения. Вот, что я там увидел:
1: public Random() : this(Environment.TickCount) {}
Наверное ни для кого не секрет, что генерация случайных чисел в .Net (и не только в .Net) базируется на использовании некоего начального значения, которое должно быть уникальным. Для получений таких значений часто используется уникальная, в данном контексте, метка времени. Как видно из кода, конструктор по умолчанию, в качестве такого, значения использует Environment.TickCount - время, прошедшее со старта приложения, в миллисекундах. Т.е. получается, что если ваши объекты Random создаются с интервалом меньше миллиcекунды, при помощи конструктора по умолчанию – они будут инициализированы одним и тем же параметром, что в свою очередь влечёт генерацию одинаковых последовательностей "случайных" чисел. На форумах можно встретить несколько вариантов решения проблемы, таких как передачу в конструктор более мелкого промежутка времени (например DateTime.Now.Ticks), задержка между вызовами методов при помощи Thread.Sleep. Но исходя из всего вышесказанного, наилучшим варантом, гарантирующим генерацию случайных чисел, является использование одного экземпляра класса Random:
1: public static string GenerateRealRandomString(int length, Random random)
2: {
3: string randomString = String.Empty;
4: for (int i = 0; i < length; i++)
5: randomString += AllowableCharacters[random.Next(AllowableCharacters.Length)];
6:
7: return randomString;
Где вызов метода, осуществляется таким образом:
1: Random random = new Random();
2: Console.WriteLine(GenerateRealRandomString(10, random));
3: Console.WriteLine(GenerateRealRandomString(10, random));
Материалы поста:
- Насколько случайным бывает Random
Ссылки по теме:
Denis Reznik
LPP Soft, .Net Team Lead
Kharkov, Ukraine
|
-
-
Наверняка это уже не новость, и поисковики пестрят кучей заметок на эту тему, но всё же что это? Да ещё и с таким интригующим названием :) Вот цитата из новостной леты: “На прошлой неделе Инна Фрид (Ina Fried) из CNET News.com заявила в сети об обнаружении в Windows 7 скрытой возможности, которая получила название “режима бога” или GodMode. И хотя в действительности никаких божественных привилегий эта возможность не дает, ее использование может понравиться некоторым пользователям.” Чтобы включить “режим бога” нужно создать на рабочем столе новую папку и назвать её “GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}”. В результате чего значок папки изменится, и по двойному клику откроется окно, содержащее все элементы панели управления нижнего уровня в одном месте: 
В семёрке этот трюк проделывается без проблем. В теории (не пробовал) должно работать на 32-х битных Vista и Server 2008. “Божественного”, как видите, здесь ничего нет, но то, что это возможность скрытая, да и само название “GodMode” подогревает интерес, согласитесь :) Но, на самом деле, эта возможность не совсем скрытая. Элементам панели управления присвоены канонические имена для программного запуска этих элементов, и использовав вот такой гуид в имени папки - {E2E7934B-DCE5-43C4-9576-7FE4F75E7480} можно получить на своём рабочем столе натройки даты и времени. Недокументировано только само каноническое имя - {ED7BA470-8E54-465E-825C-99712043E01C} (поиск по MSDN выдал только ссылки на блоги). А громкое название в имени папки, тоже можно заменить на что-то другое, например “test.{ED7BA470-8E54-465E-825C-99712043E01C}” даст нам тот же “режим бога” только с именем “test” :) Ссылки по теме: - Блог открывателя “режима бога” - Блог SQLDenis - Лента новостей winline.ru - Канонические имена элементов панели управления - Использование папок для доступа к элементам панели управления
|
-
Не знаю как вам, а мне очень нравятся различные codenames, которые Microsoft даёт своим продуктам во время разработки. Над происхождением их никогда особо не задумывался, но, как оказалось, берутся они не с потолка, и в именовании продуктов есть определённый смысл. Вот, например, кодовые имена известных вам семейств продуктов Microsoft: | Windows Client | | Product | Codename | | Windows XP | Whistler | | Windows Vista | Longhorn | | Windows 7 | Blackomb | | Windows XP Starter Edition | Creekside | | Development Tools | | Product | Codename | | Visual Studio 2005 | Whitbey | | Visual Studio 2008 | Orcas | | Visual Studio Team System 2008 | Rosario | | Visual Studio 2010 | Hawaii | Из этих таблиц видно, что кодовые имена продуктов семейства Windows – это названия горнолыжных курортов. А имена семейства Development Tools – названия островов. Интересно, правда? В своём вебкасте Mary Jo Foley (Microsoft Codename Queen) более подробно рассказывает о политике присвоения codenames продуктам Майкрософт: Более подробно об истории этих и многих других codenames, можно узнать из серии постов “Microsoft Codenames” в блоге Mary Jo Foley - http://xtourl.com/22li. Ссылки по теме: - Серия постов, посвящённым Microsoft Codenames - Список различных codenames - Вебкаст Mary Jo Foley “Microsoft code names explained”
|
-
После своего поста, рассказывающего о том, как можно оптимизировать подсчёт строк в таблице, мне несколько раз задавали вопрос о том, как работает COUNT. И теперь, я хочу поделиться этой ценной информацией. Функция подсчёта строк, всегда пробегает по всем записям таблицы, т.е. последовательно их перебирает в памяти (если не используется условие WHERE). С этим связан довольно распространённый миф: “COUNT(имя_поля) лучше использовать чем COUNT(*), т.к. мы явно указываем по какому полю делать перебор, и в этом случае оптимизатор будет использовать индекс”. На самом деле это не так. Если для таблицы созданы индексы, то перебирается листовой уровень индекса с наименьшим размером ключа, таким образом оптимизатор сам выбирает наиболее оптимальный план выполнения, в независимости от того, что вы указываете в условии COUNT. В этом можно убедиться, запустив пару скриптов. Вначале создадим тестовую таблицу, заполним её данными и создадим необходимые для теста индексы (если вставить в таблицу меньшее количество записей (например 100), то использоваться будет IDX_BIG (думаю в этом случае оптимизатору просто не хватает информации)): CREATE TABLE dbo.Test
(Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SmallField tinyint NOT NULL, BigField bigint NOT NULL)
GO
CREATE INDEX IDX_SMALL ON dbo.Test(SmallField)
GO
CREATE INDEX IDX_BIG ON dbo.Test(BigField)
GO
INSERT INTO dbo.Test VALUES(1, 2)
GO 1000
И выполним инструкции COUNT в различном формате:
SELECT COUNT(*) FROM dbo.Test
GO
SELECT COUNT(BigField) FROM dbo.Test
GO
SELECT COUNT(1) FROM dbo.Test
GO
Из планов выполнения видно, что в любом случае используется наименьший индекс IDX_SMALL:
 Если удалить IDX_SMALL, то будет сканироваться IDX_BIG, т.к. в этом случае он станет самым маленьким. Если же удалить и его, будет сканироваться первичный ключ таблицы. В данном примере показан пример полного сканирования таблицы. Если у вас есть конкретное условие WHERE или поля NULL, то в запросе будет использоваться индекс указанного в запросе поля.
PS: Думаю выкладывать скрипты, планы выполнения, и другие материалы используемые в постах, в свой Sky Drive - http://xtourl.com/22et
Ссылки по теме:
- Блог Пола Рэндала
- Блог Аарона Альтона
- Функция COUNT
- Альтернатива COUNT(*)
- Материалы поста
|
-
-
27 ноября состоялась очередная встреча UNETA, на которой я выступал с докладом о “SQL Azure Database”. В докладе был представлен обзор имеющихся возможностей, принципов работы, истории продукта, направлений развития, а также описана стратегия вывода продукта на рынок. Материалы доклада находятся здесь - http://xtourl.com/229d. И, в заключение, чтобы пост не получился таким скучным, хочу привести несколько наиболее актуальных вопросов, заданных в ходе доклада: - Когда планируется выход продукта на рынок? С 1-го января SQL Azure Database будет доступна для промышленного использования в США, странах юго-восточной Азии и северной Европы. У стран СНГ возможность использовать SQL Azure появится позже, но точные даты пока не известны. - Сколько нужно платить за использование? Есть 2 редакции, Web и Business Edition, ограниченные размером базы в 1 и 10 Гб соответственно. Web стоит $9.99 в месяц, Business $99.9 в месяц (хранение данных). Трафик тарифицируется погигабайтно: 10 центов входящий, 15 исходящий. Трафик между приложением, находящимся в Windows Azure и БД в SQL Azure Database не тарифицируется. - Для кого предназначен данный продукт? В таком виде как сейчас SQL Azure Database больше всего подходит для стартапов (за счёт минимальных стартовые денежных вливаний) и для небольших компаний (ограничение на размер базы в 1 и 10 Гб), имеюших географически удалённые филиалы (респределение данных между географически удалёнными серверами). - Чем обусловлено, то, что размер базы ограничен 1 и 10 Гб, для Web и Business Edition соответственно? Ответ с форумов майкрософт был таков, что это обусловлено текущими потребностями рынка и основан на результатах опроса. Но такая ценовая политика это только начало, в дальнейшем она будет двигаться в сторону “Pay as you go”, где пользователи будут платить за те ресурсы, которые они используют. - Есть ли похожие продукты? Да, Amazon уже представил свой продукт общественности, и не за горами предоставление подобных сервисов от Google и других компаний. - Что с защитой данных? Есть ли сейчас какие-нибудь гарантии, того, что мои данные не пропадут? С точки зрения высокой доступности, то уже сейчас есть механизм её поддержания (кроме, непосредственно БД, с которой вы работаете, хранится ещё 2 реплики, одна из которых, в случае падения основной, включается в работу). Важные данные с SQL Azure Database вы можете копировать локально с помощью пакетов SSIS, для предотвращения порчи или потери данных в результате каких-либо действий пользователя. В дальнейшем планируется функционал клонирования БД, позволяющий создвать копию вашей базы по требованию (1 пол. 2010 года) и реализация полноценного бэкапа, с возможностью восстановления данных на конкретный момент времени (2 пол. 2010 года). - Что делать с конфиденциальными данными? Могу ли я доверить их Майкрософт? С точки зрения защиты самих данных от несанкцинированного доступа, есть защита с помощью настраиваемых правил файервола, где вы можете ограничить список IP с которых может быть осуществлён доступ к данным. В то же время, зашиты от “Билла Гейтса” нет, и прозрачного шифрования данных, которое, на мой взгляд, могло бы стать решением этой проблемы, тоже пока нет, но планируется в дальнейшем. Сейчас же вы можете шифровать конфиденциальные данные перед предачей их в облако. Ссылки по теме: - Материалы доклада “SQL Azure Database” - Работа с SQL Azure с помощью SSMS - Сайт SQL Azure - Цены на SQL Azure - SQL Azure developer portal - T-SQL в SQL Azure Database - Блог комманды SQL Azure
|
-
SQL Azure Database – сервис хранения и обработки реляционных данных в облаке или, попросту говоря, “облачная” база данных. Первые версии и демонстрации продукта появились более года назад (тогда он ещё назывался SSDS – SQL Server Data Services), затем продукт был представлен как SQL Data Services на PDC 2008, и, впоследствии, переименован в SQL Azure Database, который в скором времени выйдет в промышленную эксплуатацию. И вот, наконец-то, наряду с возможностью работы с данными с помощью T-SQL команд, с выпуском ноябрьского СTP SQL Server 2008 R2 появилась возможность работать с SQL Server Database с помощью привычного нам инструмента – SQL Server Management Studio, что я конечно же попробовал, и теперь хочу поделиться своим опытом и впечатлениями. Чтобы начать работу с SQL Azure Database нужен аккаунт SQL Azure. Если он у вас уже есть, идём на sql.azure.com, если нет, то можно зарегестрироваться для получения приглашения. После того, как вы вошли в систему с помощью своего Live ID, вы увидите свой сервер. Сервер, в данном случае, не является экземпляром SQL Server, здесь это некая логическая единица. На сервере уже создана одна БД - master: Удалённый доступ к SQL Asure Database по умолчанию отключён. Чтобы включить его нужно перейти на закладку “Firewall Settings” и добавить свой IP в список разрешённых адресов (IP определяется сам, вам только нужно скопировать и вставить его в поле. Мелочь, а приятно :)): 
Новые настройки файервола вступают в действие через пять минут. Есть возможность расслабиться и почитать что-нибудь про SQL Azure :). Итак, приступаем к работе. Для соединения с сервером нужно скопировать имя сервера с портала, выбрать тип аутентификации SQL Server (Windows аутентификация не поддерживается) и ввести имя пользователя и пароль, указанные вами при создании аккаунта SQL Azure. Если вдруг забудете пароль, его можно будет легко поменять прямо на портале. Первое, что бросается в глаза – отсутствие многих пунктов контекстного меню, при нажатии правой кнопкой на различных узлах в Object Explorer: 
Для того, чтобы посмотреть на поддержку T-SQL, я сгенерировал студией скрипты БД Northwind, и попытался накатить их на “облачный” сервер, и поработать с “облачной базой”. Итак, первые впечатления: - CREATE DATABASE не поддерживает разбиение на файловые группы (базу также можно создать прямо с портала) - ALTER DATABASE не поддерживается - Поддерживается создание логинов - Не поддерживается переключение контекста с помощью USE (чтобы начать работать в новой, созданной БД, нужно явно открыть соединение с ней) - Поддерживаются DDL команды создания практически всех объектов БД - Настройки индекса при создании не поддерживаются (FILLFACTOR и т.п.) (если из студии сгенерировать скрипт “облачной” БД, то накатить его в облаке будет нельзя, т.к. он содержит и USE и ALTER DATABASE и неподдерживаемые настройки индексов) - Поддерживаются базовые команды T-SQL - В контекстном меню таблицы нет привычного пункта SELECT TOP 1000 rows - Интеллисенс не работает - Поддерживает типы данных SQL Server 2008 (за исключением HierarchyId, Geometry и Geography) - Доступна работа с планами выполнения - При раскрытии узла репликации в Object Explorer, вылетает “Operation not supported”. - Profiler и Activity Monitor недоступны Более подробно об ограничениях текущей версии и о доступных возможностях T-SQL можно прочитать на странице продукта в MSDN. В целом же впечатление остаётся приятное, и работа с SQL Azure кажется чем-то привычным. Текущая версия SQL Azure Database является “feature complete” и большинство её ограничений связано с самим принципом предоставления облачного сервиса (сокрытие физических аспектов от потребителей). И в заключение хочу сказать, что на PDC 2009 была объявлена дата ввода продукта в промышленную эксплуатацию – 1 января 2010 года (для США, стран юго-восточной Азии и северной европы). У нас это будет немного позже. Ещё интересный момент – первый месяц эксплуатации будет бесплатным. Ссылки по теме: - Сайт SQL Azure - SQL Azure developer portal - T-SQL в SQL Azure Database - Блог комманды SQL Azure - По дороге с облаками - SQL Azure – MSDN - SQL Server 2008 R2 Management Studio Download - Brent Ozar blog - Видео с PDC 2009, содержащее тэг “SQL Azure”
|
|
|