среда, 3 декабря 2014 г.

MSSQL – простые индикаторы нехватки индексов

Как обычно в нашей профессии, есть несколько способов решить задачу :)

Расскажу о трёх наиболее простых подходах (и одном бонусном), не углубляясь в детали. Если интересны как раз глубокие детали, рекомендую почитать документацию (начать стоит с Designing Indexes, а прочитав о проектировании продолжить Performance (Database Engine)).

Кстати, если тема плана выполнения для вас нова – неплохим стартом будет мой давний пост «SQL – execution plan».

DISCLAIMER: в этой статье я даю простые советы, которые чаще всего работают, однако в MSSQL практически невозможно дать рекомендации на все случаи жизни – данные бывают разные, запросы бывают разные…

1. Мысленный эксперимент

Достаточно полезный способ, особенно на этапе проектирования. Хорошим подспорьем для тех, кому индексы в новинку, будет вышеупомянутый раздел про дизайн индексов.

Что касается простейших советов, они следующие:

  1. Если данных не мизерное количество – вам нужны индексы.
  2. Если есть внешние ключи – настоящие или по смыслу (некоторые их принципиально не любят делать) – они хорошие кандидаты для индексирования.
  3. Чаще всего стоит сделать кластерный индекс и сделать его не слишком большим (потому что другие индексы будут ссылаться на строки по кластерному индексу). Кстати, кластерный индекс отлично подходит для выборки по диапазону (например, по диапазону дат).
  4. Помните, что индексы тоже хранятся на диске. Простейший вывод – большое количество индексов снизит скорость вставки (иногда и обновления) данных. Обычно до создания 5-7 индексов проблем не возникает.
  5. Статистика очень важна для правильного использования индексов. Хорошая новость – чаще всего MSSQL сам достаточно неплохо за ней следит, плохая новость – это не всегда срабатывает.
  6. Для наиболее критичных запросов, возвращающих небольшое количество столбцов, имеет смысл использовать покрытие индексом (это уже чуть более продвинутая техника, но ничего сложного в ней нет).

Поскольку я обещал не углубляться в детали – идём дальше.

вторник, 21 января 2014 г.

Практическое применение Integration Services

Изложение не претендует на полноту, сейчас я хочу показать лишь некоторые возможности SSIS, чтобы вы могли приблизительность оценить целесообразность его использования. А уже в случае такого выбора - изучать предмет более досконально.

1. Базы данных для примера

В примерах используется база данных AdventureWorks в качестве источника и простая база, которую легко можно сделать самостоятельно в качестве приёмника данных. Важно понимать, что примеры приводятся искусственные, в реальной жизни всё может быть несколько сложнее.

Итак, перед началом работы нам понадобится экземпляр MS SQL Server 2012 с установленной на нём БД AdventureWorks и созданной тестовой БД, назовём её TestSSIS. Для обучения проще держать MS SQL на том же компьютере, на котором будем работать с проектом Integration Services.

Распространённые технологии импорта данных

Под распространёнными технологиями подразумеваются те, которые часто используются для импорта данных. Разумеется, для некоторых задач чаще могут использоваться технологии импорта, которые не вошли в список ниже.

1. BULK INSERT/bcp

bcp - это утилита командной строки для импорта/экспорта данных, входящая в состав Microsoft SQL Server 2012 tools (для более ранних версий она тоже присутствует). BULK INSERT - команда T-SQL, позволяющая импортировать данные тем же способом что и bcp, но без использования командной строки.

Основное назначение bcp - быстрое копирование данных между аналогичными по структуре таблицами. В принципе, с помощью файла формата (указанного в качестве опции) можно копировать данные между таблицами с разной структурой. Нередко используется копирование с помощью bcp в промежуточные таблицы, из которых средствами T-SQL данные преобразуются в итоговые таблицы.

В простом случае, сначала данные экспортируют из таблицы в файл, а потом из файла импортируют в другую таблицу (в т.ч. в другой БД или на другом сервере). В принципе, файл можно подготовить и другими средствами. Например, можно использовать csv-файлы, но с ограничениями.

четверг, 16 января 2014 г.

Импорт данных с точки зрения бизнеса

Эта статья открывает небольшой цикл статей об импорте данных. Перед тем, как говорить об импорте данных, имеет смысл понять, какие бизнес-задачи решает импорт. Обычно функциональность импорта решает одну из трех задач, обсуждаемых ниже.

1. Первоначальный/разовый импорт

Без первоначального импорта данных редко обходится внедрение любого нового решения, которое приходит на смену старому.

Часто бывает, что старое решение использует разнородные (или как модно говорить - гетерогенные) источники данных. Это могут быть несколько СУБД, файлы Excel или даже просто текстовые файлы. Но даже в случае, когда источник данных - единственная БД того же производителя, задача может быть не очень тривиальной, учитывая различия в структуре данных.

Обычно для первоначального импорта в MS SQL Server используются Integration Services, BULK INSERT (или утилита командной строки bcp) или написанный специально для этой задачи программный код. Подробнее об этих технологиях будет рассказано далее.

2. Интеграция с помощью БД

Как вы наверняка знаете, часто требуется интеграция между несколькими программными продуктами (часто даже входящими в одно решение от единственного производителя).

суббота, 16 ноября 2013 г.

SQL-скрипты для поиска данных

Иногда мы точно знаем что и где искать, иногда только приблизительно знаем что и лишь догадываемся где. Хочу поделиться несколькими скриптами под MS SQL Server для последнего случая.

Поиск объектов во всех базах данных сервера

У вас бывало, что нужно найти таблицу чтобы посмотреть данные или структуру, а вы не помните точно в какой БД она находится? У меня, периодически, такое случается. Пример - найти таблицы из проекта пятилетней давности, чтобы посмотреть как тогда решал текущую задачу.

К счастью, есть процедура sp_MSforeachdb, которая позволяет написать такой скрипт довольно компактно:

DECLARE @name AS SYSNAME,
@strSQL AS VARCHAR(MAX)
SET @name = 'Hierarchy'

SET @strSQL = '
IF EXISTS(SELECT * FROM ?..sysobjects WHERE name LIKE '
'%'+@name+'%'')
BEGIN
SELECT '
'?'' db, name FROM ?..sysobjects WHERE name LIKE ''%'+@name+'%''
END
'

CREATE TABLE #result(
db SYSNAME,
name SYSNAME,
PRIMARY KEY(db,name))

INSERT #result EXEC sp_MSforeachdb @strSQL
SELECT * FROM #result

DROP TABLE #result

Здесь всё достаточно просто:

  1. Задаём часть названия, по которому будем искать (в данном случае - Hierarchy).
  2. Собираем динамический SQL, который ищет по sysobjects (для некоторых старых версий MS SQL можно вместо VARCHAR(MAX) использовать VARCHAR(2000), например).
    При этом символ "?" заменится на название БД.
  3. С помощью конструкции INSERT ... EXEC сохраняем все результаты во временную таблицу, чтобы отобразить, в итоге, единый результат.

Поиск подстроки в текстах хранимых процедур

Как вы знаете, тексты хранимых процедур, триггеров и представлений можно найти в таблице syscomments.

Чтобы наш предыдущий запрос позволил нам найти упоминание в syscomments некоторой подстроки, его можно переписать следующим образом:

SET @strSQL = '
SELECT DISTINCT '
'?'' as db, o.name FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s.[text] LIKE '
'%'+@name+'%'''

Обратите внимание на DISTINCT - может пригодиться, потому что иногда для одного объекта бывает несколько записей в syscomments.

Поиск подстроки во всех строковых полях всех таблиц

Такой скрипт здорово помогает спать спокойно после "чистки" базы перед отправкой клиенту. Естественно, этой областью его применение не ограничивается.

Для этого случая помощник в виде процедуры от MS не нашёлся, поэтому пришлось использовать курсоры:

declare @pattern as nvarchar(MAX)
set @pattern = N'%Test%'
set nocount on
declare @sql as nvarchar(MAX)
declare @table as sysname
declare tables cursor local static read_only for select name from sys.tables t where t.is_ms_shipped = 0
open tables
fetch next from tables into @table
create table #results(name sysname not null, value nvarchar(MAX) not null)
while @@FETCH_STATUS = 0
begin
set @sql = ''
select @sql = @sql + '
insert into #results select '
'' + @table + '.' + name + ''' as name, [' + name + '] from [' + @table + '] where [' + name + '] like ''' + @pattern + ''''
from sys.columns c where c.object_id = OBJECT_ID(@table) and c.system_type_id in
(select system_type_id from sys.types where collation_name is not null)
exec(@sql)
fetch next from tables into @table
end
select * from #results
close tables
drop table #results

Помимо курсоров принципы те же. Есть пара нюансов:

  • Ищем таблицы без флага is_ms_shipped, чтобы просматривать только таблицы, созданные на этапе разработки.
  • Для отбора строковых столбцов вместо списка типов (который поддерживать утомительно) используется фильтр "collation_name is not null".

Резюме

Внимательный читатель наверняка догадался по оформлению, что последний пример написан не в то же время что и два первых. Если быть точнее, последний запрос я написал месяц назад. Это я к тому, что на основе подобного подхода вы можете сами писать полезные скрипты, решающие ваши специфические задачи.

Если такой формат понравится читателям, буду иногда выкладывать полезные скрипты из личной коллекции.

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях, твиттер или на olegaxenow.reformal.ru. Постараюсь учесть.

среда, 8 февраля 2012 г.

Разработка пакетов NuGet + инфраструктура

В статье "Model scaffolding для MVC 3 – идеи и принципы" я рассказал об идеях и принципах, заложенных в основу пакета Model Scaffolding. Сейчас я расскажу о следующем:

  • Как написать простой пакет NuGet.
  • Как подготовить инфраструктуру для разработки и отладки пакетов NuGet.
  • Как начать реализовывать свой Scaffolder (генератор чего-либо в любом вашем проекте, необязательно ASP.NET MVC).

Важное замечание: после выхода NuGet 1.6 вы можете столкнуться с неожиданными ошибками при установке любых пакетов NuGet для Scaffolding (мои пакеты зависят от T4Scaffolding). В этом случае рекомендую такую последовательность:

  • Uninstall NuGet.
  • Install NuGet.
  • Затем то же самое для всех Scaffolding-пакетов (с учетом зависимостей некоторые установятся сами).

NuGet

Scaffolder имеет смысл делать именно NuGet-пакетом, поэтому коротко расскажу о том что это и как создать и опубликовать пакет в простом случае (подробнее можно почитать в документации). Про возможности NuGet я уже рассказывал раньше, в статье MVC 3 + scaffolding.

NuGet-пакет формируется из набора файлов с помощью файла спецификации и приложения NuGet.exe. Затем его можно опубликовать на NuGet.org или на другом (например, своем) сервере. Также можно просто скопировать в некоторую папку, а папку добавить в настройки Visual Studio (Tools / Library Package Manager / Package Manager Settings). Так что, если хотите использовать возможности NuGet в своей закрытой разработке – это тоже возможно.

воскресенье, 15 января 2012 г.

Code First Migrations + Entity Framework 4.3 Beta 1

MonarchButterflyНа днях, а точнее 12 января увидел свет Entity Framework 4.3 Beta 1. Основная тема этого релиза – EF Code First Migrations. Помимо этого, есть несколько изменений, которые тоже имеет смысл упомянуть.

На фото миграция бабочки монарх (CC BY-NC-ND 2.0, оригинал здесь).

В этой статье я, со своей точки зрения, разумеется, отвечу на вопросы, подкрепляя их примерами:

  • Что такое миграции и для чего они нужны?
  • Что интересного в этом релизе и в EF Code First Migrations вообще?

Думаю, вряд ли кто-то использует миграции в повседневной коммерческой разработке. Поэтому я не буду явно выделять список нововведений, а буду просто рассказывать про миграции, отдельно выделяя новое в EF 4.3 Beta 1.

Кстати, наверное вам будет интересно, что релиз EF 4.3 планируется на первый квартал этого года. Жаль только что релиз EF 5.0 с поддержкой enum и оптимизированной скоростью ждёт наc нескоро – с выходом .NET 4.5 ожидается только EF 5.0 Beta 1.

Краткое описание миграций

Миграции в EF предназначены для решения проблемы изменения структуры базы данных в процессе эволюции приложения. Один из вариантов решения этой проблемы я озвучивал ранее в статье “Основы SQL - DDL и рефакторинг БД”. Надеюсь, что когда будет готов EF Code First Migrations, он будет значительно лучше, чем подход со скриптами.

Важное замечание: основной момент, который нужно отслеживать при миграции базы данных вообще, помимо соответствия структуры, – сохранность клиентских данных.

понедельник, 9 января 2012 г.

Code Metrics + Refactoring

MicroscopeДумаю, большинство из вас знают, что в Visual Studio есть набор метрик кода. Моя цель – привлечь к ним ваше внимание, в том числе как к одной из отправных точек для ревью кода и рефакторинга.

Сначала я расскажу о запуске подсчёта метрик. Потом кратко опишу каждую метрику и дам некоторые рекомендации по рефакторингу в случае проблем. Затем немного расскажу о моём опыте использования метрик на практике.

Однако, начиная разговор о метриках кода в Visual Studio, хочу пояснить моё отношение к метрикам кода вообще.

С одной стороны, это мощный инструмент для оценки качества кода. По моему мнению, его обязательно стоит использовать как отправную точку для ревью кода (в том случае, когда идем не от коммита, а делаем ревью отдельного класса/модуля).

С другой стороны, не стоит идеализировать метрики кода – это всего лишь один из инструментов. Вы же не считаете, что раз молоток хорошо забивает гвозди, это лучшее средство для закручивания шурупов? ;)