Чем точнее указан путь – тем быстрее можно по нему идти ((С) Кэп)

Недавно попросили помочь с оптимизацией обработки XML в SQL Server-е. Запрос выглядит примерно следующим образом:

SELECT
  nref.value('./../../@id', 'int') AS articleid,
  nref.value('type[1]', 'nvarchar(256)') AS type,
  nref.value('theme[1]', 'varchar(256)') AS theme,
  nref.value('score[1]', 'varchar(32)') AS score
INTO #tmpTheme
FROM @bulk_xml.nodes('//theme') AS R(nref)

Очень простое действие – указание точного XPath выражения вместо обобщенного ('//theme') позволяет ускорить работу данного запроса в 1.5 – 2 раза.

HTH,

AlexS

Право на жизнь для табличных переменных

С момента появления в SQL Server табличных переменных я все никак не мог понять (для себя): зачем же они прямо так нужны (по большому счету). Аргумент “передавать много параметров в хранимую процедуру в табличной форме” звучал (и до сих пор звучит) не слишком убедительно: никто не отменял упаковки/распаковки (в случае, когда вызов идет из кода) и … собственно таблиц (в случае, когда мы вызываем хранимку из другой хранимки/скрипта).

Недавно на StackOverflow был задан вопрос, который в вольном переводе звучит следующим образом: как мне сохранить какие-то данные из транзакции, которая затем будет отменена? Сценарий, который стоит за этом вопросом, более чем реален: протоколирование. Ответом на этот вопрос и являются табличные переменные: изменения значений переменных не является частью транзакции (и, соответственно, не подлежат отмене).

Проверил: да, таки работает. Но тут как в том старом анекдоте: “ложечки нашлись, но осадок остался”. Табличная переменная на самом деле – это временная таблица (что легко проверить). При этом изменения, вносимые в “обычные” временные таблицы протоколируются и являются частью транзакции, в которой эти изменения происходят (что тоже легко проверяется). Получается некоторое противоречие: табличная переменная – это временная таблица, но изменения, которые в нее вносятся не протоколируются. Покопавшись в гуглоридере, нашел (отмеченный мною же!) пост в блоге SQL Server Storage Engine на эту тему – “перечитал и переосмыслил” :-)

HTH

AlexS

T-SQL Simple Facts

В последние пару недель читал блоги/Books Online и (заново) открыл для себя некоторые "простые" факты, некоторые из которых здорово меня удивили (признаюсь). Некоторые другие всплыли "по ассоциации", в итоге решил поделиться всеми сразу:

1. Всем хорошо известен оператор GO, означающий окончание пакета. Так вот, полный синтаксит этого оператора: GO [count] - где count - положительное целое число, указывающее, что предшествующий пакет необходимо выполнить count раз (квадратные скобки указывают, что этот параметр может отсутствовать (что и происходит в ошеломляющем большинстве случаев)).
2. Для типа данных datetime определена операция сложения, причем вторым аргументом может быть целое число, которое интерпретируется как количество дней. Так:
   DECLARE @D datetime
   SET @D = '2009/06/24 15:00'
   SET @D = @D + 2
   PRINT @D
   выдаст 26 июня 2009, 15:00 ... когда я увидел эту запись, то признаться сильно удивился.
3. Для добавления данных в таблицу можно использовать следующий синтаксис:
   INSERT INTO <Имя таблицы> DEFAULT VALUES;
   Сработает, конечно, только в том случае, когда для всех полей таблицы, не считая автоинкрементных и timestamp, определены значения по-умолчанию, но все-равно любопытно.
4. Тип данных timestamp не имеет ничего общего с *nix timestamp (отсчитывающего количество миллисекунд с начала Unix-эры). В SQL Server колонки этого типа данных обновляются автоматически при любых операциях добавления/изменения записи - т.е. это некий аналог "версии строки", за который отвечает сам сервер. Гарантируется уникальность значений полей timestamp в рамках каждой базы данных.
5. Переменные не покрываются транзакцией:
   DECLARE @i
   SET @i = 10
   BEGIN TRANSACTION
   SET @i = 20
   INSERT INTO SomeTable VALUES (@i)
   ROLLBACK TRANSACTION
   PRINT @i
   напечатает 20 (а не 10, как можно было бы ожидать).
6. Синтаксис ALTER TABLE ... ALTER позволяет изменить определение поля таблицы но не его имя (что можно было бы ожидать). Для переименования необходимо воспользоваться хранимой процедурой sp_rename (не слишком элегантно, но действенно).

HTH,

AlexS

Для быстрой страницы шепчите на ушко богам SQL-я

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

Итак, речь пойдет о списочных представлениях (aka list view). Я уже как-то ранее упоминал о них здесь. Вкратце напомню особенности этого способа отображения данных:

• отображаются данные сразу из нескольких таблиц (от двух до десятка)
• предполагается сортировка по любому полю
• предполагается разбиение данных на страницы (для удобства просмотра)
• чаще всего пользователь имеет возможность фильтровать данных по различным (порой довольно сложным) условиям
• в большенстве случаев НЕ предполагается inline-редактирование данных (в силу пункта 1)

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

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

CREATE TABLE [dbo].[CityData](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [CityName] [varchar](50) NULL,
    [Province] [varchar](50) NULL,
    [ZipCode] [varchar](10) NULL);

CREATE TABLE [dbo].[PersonData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CityId] [int] NULL,
    [FullName] [varchar](255) NULL,
    [ZipCode] [varchar](10) NULL,
    [Email] [varchar](255) NULL,
    [ContactPhone] [varchar](100) NULL);

Отображать будем данные о людях (PersonData) вместе с информацией о городе:

select *
from
(
    select
        p.Id, p.FullName, c.CityName,
        ROW_NUMBER() OVER (ORDER BY p.FullName) as RowNumber
    from
        PersonData p
        left join CityData c on c.id = p.CityId
) q
where q.RowNumber between @StartRow and @EndRow

Все хорошо, НО: зачем присоединять данные о городах ко всем записям из таблицы PersonData в то время, как нам нужно получить всего лишь (@EndRow - @StartRow) записей? Правильно, севершенно незачем. Поэтому можем сделать так:

select
    q.*,
    c.CityName
from
(
    select
        p.Id, p.FullName, p.CityId,
        ROW_NUMBER() OVER (ORDER BY p.FullName) as RowNumber
    from
        PersonData p
) q
left join CityData c on c.id = q.CityId
where q.RowNumber between @StartRow and @EndRow

Что изменилось? Пришлось явно выбирать p.CityId (чтобы потом можно было присоединить данные о городах) и ... в общем-то все. Каков результат? Вот что говорит нам план выполнения запроса:

• таблица CityData в первом случае присоединяется ДО того, как происходит фильтрация данных для страницы, а во-втором ПОСЛЕ
• стоимость выполнения второго запроса в два раза меньше (это на нашем простом примере, где в PersonData 100 записей, а в CityData - 50)
• статистика ввода/вывода тоже вдохновляет: удалось почти вдвое сократить количество логических чтений на обработке таблицы CityData

Примерно такая же картина наблюдается в MySQL и PostgreSQL (только план запроса не так наглядно выглядит).

Несколько замечание касательно этой техники:

1. Чем больше таблиц нужно присоединять и чем больше у нас данных - тем значительнее выигрыш (хотя зависимость нелинейная)
2. Те таблицы, которые связаны с главной таблицей через inner join, должны всегда быть во внутреннем запросе, а вот те, которые через left join можно смело выносить наружу
3. Та таблица, по полю которой нужно сортировать результат, должна быть во внутреннем запросе

HTH

Об опциях соединения вообще и об IDENTITY_INSERT в частности

Сегодня пришлось "играться" с IDENTITY_INSERT. С удивлением обнаружил прочувствовал что это таки "установка уровня соединения". Не скажу, что это прямо так удивительно, в MSDN об этом явно сказано:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2000/2005/2008 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

но как-то до сих пор я не особенно обращал внимание на это примечание.

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

Для того, чтобы постигнуть "всю глубину наших глубин", рассмотрим такой сценарий. Мы выполняем некоторый SQL код и хотим повысить уровень его изоляции:

using(SqlConnection = new SqlConnection())

{

con.Open();

SqlCOmmand com = new SqlCommand("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- очень важный SQL код", con);

com.ExecuteNonQuery();

}

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

Граждане! Будьте бдительны применяя опции соединения (особенно уровень изоляции).

HTH