UML – быстрый старт

Некоторое время назад я проводил небольшое обучение проектной группы основам UML. Для этого я составил краткое руководство по UML, которое помещаю здесь.

1.     Введение.

UML – это Unified Modeling Language, как следует из названия – унифицированный язык моделирования. UML представляет собой набор соглашений, которые предназначены для облегчения процесса моделирования и обмена информацией в проектной группе. Наличие стандартизированной нотации позволяет сократить время на усвоение информации, упрощает общение и взаимодействие, облегчает документирование.

В этом документе описаны самые основные разделы языка UML, которые потребуются в повседневной работе.

2.     Основы.

UML представляет собой графическую нотацию которая предназначена для моделирования и описания всех процессов протекающих в процессе разработки. Основу UML представляют диаграммы, которые различаются по типам и предназначены для моделирования различных аспектов разработки.

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

3.     Описание типов диаграмм.

3.1.          Диаграмма вариантов использования (Use-case diagram).

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

Основными понятиями при работе с диаграммой вариантов использования являются Актор (Actor) и Вариант использования (Use case).

Актор – это роль, которую выполняет пользователь или другая система, при взаимодействии с проектируемой системой.

Проектирование диаграммы вариантов использования начинается с определения списка Акторов. На диаграммах Актор обозначается следующим значком:

Каждый Актор обладает уникальным именем.

Друг с другом акторы могут быть связаны различного рода отношениями.

Например, акторы могут наследоваться друг от друга.

Это означает, что акторы-наследники наследуют характеристики базовых акторов.

Следующим этапом после определения списка акторов является определение списка вариантов использования.

Вариант использования – это конечная единица взаимодействия актора и системы. Совокупность всех вариантов использования полностью определяет поведение системы.

Вариант использования обозначается значком:

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

Например:

означает, что актор User инициирует вариант использования Login.

Один и тот же вариант использования может использоваться несколькими акторами, например:

вариант использования Login используется двумя акторами.

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

1.       «Включение» одного варианта использования в другой. Означает, что один вариант использования инициируется в процессе другого. Например:

2.       «Расширение».  Означает, что один вариант использования является дополнением или уточнением другого варианта использования в случае наступления некоторых условий. Например:

3.       «Реализация». Означает, что один вариант использования является реализацией другого варианта использования. Например, если один из них описан в терминах бизнес-процессов, а другой – в терминах проектируемой системы.
Например:

Кроме того, варианты использования могут быть связаны отношением «Реализация» с требованиями к системе и с классами. При наличии таких связей есть возможность проследить в каких классах реализованы требования и какие классы могут быть затронуты при изменении требований или вариантов использования.
Например:

Кроме Акторов и Вариантов использования на диаграмме также могут находиться следующие элементы:

“Collaboration” – элемент, предназначенный для визуальной группировки объектов – акторов и вариантов использования – по принципу их совместной работы.
Обозначается значком

Например,

«Boundary» - элемент, предназначенный для визуальной группировки объектов – акторов и вариантов использования – по принципу их распределения на подсистемы или компоненты.

Обозначается значком

Например:

Среди акторов могут быть не только пользователи, но и внешние системы и внутренние подсистемы.

Пример внутренней подсистемы:

3.2.                     Диаграмма последовательностей (Sequence diagram)

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

Иными словами, если вариант использования отвечает на вопрос «Что делает актор?», то последовательность отвечает на вопрос «Как работает система при выполнении данного варианта использования?».

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

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

Проще всего продемонстрировать суть диаграммы последовательностей на примере:

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

На самой диаграмме показаны линии жизни каждого из объектов и процесс их взаимодействия.

Взаимодействие объектов показано стрелками. В терминах диаграмм последовательностей такое взаимодействия называется Сообщение (Message) В приведенном примере под сообщением понимается вызов методов тех или иных классов. При этом, как видно на примере, сообщения могут быть вложены друг в друга, что означает, что один метод вызывается в теле другого.

Кроме сообщений, которые вызываются другими объектами, существуют собственные сообщения, которые объект вызывает сам у себя (Self-message).

Например:

Кроме того, существуют также обратные сообщения (Return message), которые обозначают передачу некоторой информации вызывающему сообщению. В терминах классов это означает возврат некоторого значения.

Например:

На этой диаграмме метод возвращает результаты своей работы.

Собственные сообщения также могут быть обратными.

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

Сообщения могут объединяться в комбинированные фрагменты (Combined fragment), который предназначен для отображения циклов, ветвлений, критических секций и пр.

Пример ветвления:

Пример цикла:

Некоторые сообщения могут заканчиваться Конечными точками (End point), которые означают выход из алгоритма.

Пример:

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

Например:

3.3.                     Диаграмма классов

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

Так же как и предыдущие диаграммы она может быть представлена как в терминах конкретных классов, так и в терминах бизнес-объектов.

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

Основным элементом диаграммы классов является класс.

Обозначается значком:

Класс состоит из двух частей – заголовка с именем класса и тела с описанием его полей (Атрибуты – в терминах UML) и методов (Операции - в терминах UML).

Абстрактные классы отличаются наклонным написанием заголовка:

Под атрибутами класса в терминологии UML понимают его поля.

Атрибуты записываются с указанием доступности, имени и типа.

Например:

Знак «-» означает, что атрибут является приватным (private).

Знак «+» означает, что атрибут является публичным (public).

Знак «#» означает, что атрибут является защищенным(protected).

После имени следует указание типа атрибута.

Под операциями в терминологии UML понимаются методы, свойства, индексаторы и пр.

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

Например:

Статические атрибуты и операции записываются с подчеркиванием, например:

Абстрактные методы записываются наклонным шрифтом, например:

Кроме классов, важным элементом диаграмм классов являются интерфейсы.

Интерфейс обозначается так:

Кроме классов и интерфейсов на диаграмме классов также могут помещаться перечисления.

Перечисление обозначается так:

Обычно перечисление указывается с перечнем возможных значений, например:

На диаграмме классов отображаются не только классы, интерфейсы и пр., но и их взаимосвязи.

Взаимосвязи бывают различных типов и отображаются, соответственно, по-разному.

Генерализация или наследование (Generalize) обозначается так:

На примере показано, что два класса являются наследниками абстрактного класса.

Реализация (Realize) – означает, что данный класс реализует данный интерфейс:

Ассоциация (Associate). Наиболее широко используемая связь между классами. Она имеет достаточно широкое значение и может означать, например, следующее:

1.       Один класс осуществляет взаимодействие с другим каким-либо образом.
Например:

2.       Один класс включает в себя экземпляр другого класса.
Например:

Видим, что перечисление Status включено в класс User, при этом имя поля Status, с областью видимости public.

3.       Один класс включает в себя несколько экземпляров другого класса.
Например:

Видим, что коллекция UsersCollection может включать от нуля до бесконечности объектов User.
Возможные значения количества объектов:

·         «*» или «0..*» - любое количество объектов

·         «0..n» - любое количество объектов, но не больше n, например «0..5»

·         «n» - точное количество объектов, например «1», «5»

·         «n..*» - любое количество объектов, но не меньше n, например«1..*» - хотя бы один.

Если Ассоциация не может быть реализована без дополнительных классов (например, отношение «многие-ко-многим»), то она реализуется при помощи дополнительных классов ассоциации, которые предназначены решить эту задачу.
Обозначается это следующим образом:

В данном примере вы видите, что для того, чтобы установить связь «многие-ко-многим» между переводчиками (класс Translator) и языками (класс Language) используется вспомогательный класс TranslatorToLanguage.

Композиция (Compose)  - означает, что объекты одного класса могут быть включены в объекты другого класса и при этом этот вложенный объект может находиться только в одном объекте-контейнере. Если объект контейнер удаляется, то вложенный объект тоже удаляется.

Например:

Агрегация (Aggregate) - означает, что объекты одного класса могут быть включены в объекты другого класса и при этом этот вложенный объект может находиться в нескольких объектах-контейнерах. Если объект контейнер удаляется, то вложенный объект не удаляется.

Например:

Для большей наглядности кроме стандартного значка класса, есть также дополнительные значки, которые применяются для некоторых типов классов. Я привел здесь три наиболее часто используемых обозначения, которые применяются для работы с шаблоном «Модель-Представление-Контроллер»

1.       Класс-сущность (Entity) – обычно применяется для обозначения классов, которые хранят некую информацию о бизнес-объектах.
Обозначается:

2.       Класс-контроллер (Controller) – обычно используется для классов, которые используются для выполнения некоторых операций над объектами.
Обозначается:

3.       Класс-Разграничитель (Boundary) – обычно используется для классов, отделяющих внутреннюю структуру системы от внешней среды. Это могут быть WebServices, пользовательский интерфейс и пр.
Обозначается:

3.4.          Диаграмма коммуникаций

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

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

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

Проще всего привести описание диаграммы коммуникаций на примере:

На примере видим, что актор User взаимодействует с экземпляром страницы LoginPage, который в свою очередь работает с классом SecutiryManager, который оперирует объектами типа User.

Элементы диаграммы коммуникаций могут быть связаны отношением «Ассоциация». Как я уже указывал выше Ассоциация имеем достаточно широкое значение и может трактоваться по разному (см. Диаграммы классов).

Однако, в отличие от диаграмм последовательностей, где порядок инициации сообщений определяется шкалой времени, на диаграммах коммуникаций используется нумерация ассоциаций, которая определяет этот порядок.

Например:

3.5.          Диаграммы состояний. (State diagram или State Machine)

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

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

Диаграммы состояний состоят из элементов двух основных типов: Состояний и Переходов.

Элемент Состояние (State) отображает состояние объекта или процесса в какой-либо момент времени.

Обозначается значком:

Название Состояния является его описанием, т.е., например, состояние

означает, что пользователь находится в «незалогиненном» состоянии.

Переход (Transition) – элемент, отображающий путь перехода из одного состояния в другое.

Например:

Для обозначения начала и конца всего процесса переходов используются псевдо-состояния: Инициирующее (Initial)

и Финализирующее (Final)

Применятся они могут, например, так:

Если Состояние сопряжено с некоторой деятельностью, то это тоже отображается на диаграмме.

Например:

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

Например:

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

Другой вариант того же самого примера:

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

Переход также может осуществляться между одним и тем же состоянием.

Например:

Кроме обычных Состояний (State) существуют также Суперсостяния (State Machine), которые могут включать в себя другие состояния и переходы.

При этом контекст Суперсостояния является актуальным для всех элементов, находящихся внутри этого Суперсостояния.

Например:

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

Например:

На одной диаграмме состояний может быть отображено несколько одновременных состояний одного и того же объекта, если эти состояния изменяются параллельно друг другу.

Например (из книги Фаулера):

Если несколько параллельных потоков переходов должны быть синхронизироваться в какой-то момент, то для это используется псевдо-состояние Synch

Существует также дополнительный элемент Fork/Join – используется для разбивки или объединения нескольких потоков состояний.

Например:

3.6.          Диаграммы деятельности. (Activity diagram)

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

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

Основным объектом диаграмм активностей является Активность (Activity), которая обозначается следующим значком:

Диаграммы активностей имеют те же элементы, что и диаграммы состояний, а именно: псевдо-активности начала и конца потоков, переходы, Fork/Join, Суперактивности.

Пример:

На примере показан поток работ по работе с клиентом. Здесь видно, что диаграмма не посвящена состояниям только одного элемента или объекта – вместо этого она отражает поток деятельностей, который затрагивает различные элементы или подсистемы.

Кроме того, на этой диаграмме присутствует новый элемент Решение (Decision) –

Этот элемент знаком нам по блок-схемам и обозначает момент ветвления или соединения потоков.  Отличие от Fork/Join состоит в том, что процесс продолжается только по одной ветке, в то время как после Fork/Join – по всем веткам параллельно.

Существенным дополнением диаграммы деятельности является элемент Partition, который сам по себе логического значения не имеет и предназначен для визуальной группировки работ по какому-либо признаку.

Обозначается значком:

Например:

На примере видно, что потоки работ условно сгруппированы в работу по обработке заказа и по проведению платежа.

Кроме обычных активностей, есть несколько дополнительных:

1.       Отправка (Send) – используется для отображения запросов к каким-либо внешним источникам.
Обозначается:

2.       Получение (Receive) – используется для получения информации от каких-либо внешних источников.
Обозначается:


Например:

4.     Пакеты (Packages)

Пакеты – это способ группировки любых элементов языка UML – диаграмм, вариантов использования, классов и пр.

Обычно пакеты представляют собой:

·         подсистемы, если речь идет о системе

·         компоненты или слои, если речь идет о классах

·         наборы бизнес-логики, если речь идет о вариантах использования и т.п. 

Конкретный способ группировки элементов в пакеты остается на усмотрение проектировщика.

Пакет обозначается следующим образом:

Например, может существовать пакет Акторов -

в котором находятся диаграммы с перечнем Акторов.

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

Например:

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

Как связать продажи и источники трафика. Weighting

08Здесь я привожу копию моей статьи "Как связать продажи и источники трафика. Weighting", опубликованной на сайте SeoNews (http://www.seonews.ru/masterclasses/detail/29891.php)

1. Введение

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

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

Среди источников прибыли (или трафика на сайте) можно выделить следующие:

1. Платные поисковые системы

2. Бесплатные поисковые системы

3. Партнеры (обмен ссылками, баннерами и пр.).

4. Почтовые рассылки

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

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

1. Платные поисковые системы содержат множество ключевых слов, кампаний, аккаунтов и пр.

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

3. Партнеры – имеют множество сайтов.

4. Рассылки – предназначены для рекламы различных товаров и услуг.

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

2. Описание механизма

Для того чтобы дальнейшие рассуждения были более наглядными, я привел здесь небольшой рисунок:

На нем вы можете видеть типичный путь пользователя по сайту – от попадания на сайт с одного из источников до оплаты заказа. Основными страницами здесь являются:

• Входная страница (Landing page) – страница, на которую пользователь попадает из «внешнего» интернета.

• Thank you page – страница благодарности за покупку. Эта страница отображается пользователю только после оплаты заказа. Отображение этой страницы является гарантией того, что пользователь совершил покупку.

Данная схема является приблизительной, т.е., например, внешняя платежная система может отсутствовать, если сайт сам имеет возможность принимать платежи.

Итак, основная задача - связать информацию о продаже ( ее сумме, товаре и пр.) с источником трафика и с расходами на этот источник.

Для каждого из источников способы его определения различаются, и поэтому я опишу их отдельно. Однако есть нечто общее, что их объединяет, а именно – счетчик трафика. Без него будет невозможно связать источник трафика и продажу. Счетчик трафика может быть частью сайта – т.е. просто анализировать логи вызовов его страниц, или может быть отдельной самостоятельной системой, которая имеет свой код на всех страницах.

В любом случае, счетчик трафика должен обладать следующим набором минимальных возможностей:

1. Должен уметь сохранять сессию посетителя от начальной страницы до последней.

2. Должен уметь сохранять идентификатор посетителя – он потребуется для решения проблемы weigthing'а (о weigthing'е ниже в этой статье).

3. Должен уметь сохранять все действия посетителя, по крайней мере, за несколько последних месяцев (опять же, для решения проблемы weigthing'а – см. ниже).

4. Иметь возможность регистрации факта продажи и как минимум ее объема.

5. Иметь возможность определения источника трафика , т.е. определения поисковых систем, поисковых запросов и пр. (как определить тот или иной источник – ниже в этой статье).

6. Иметь счетчики как минимум на входной странице и последней странице.

В том случае, когда вы имеете несколько сайтов, между которыми осуществляется переход посетителей, наличие счетчика трафика в виде отдельной системы является более предпочтительным, так как в этом случае вы имеете возможность сохранять идентификаторы посетителя и сессий не в Cookie сайта, а в Cookie счетчика, получив, таким образом, уникальные идентификаторы в пределах всех ваших сайтов. Однако для этого ваш счетчик должен обладать P3P-сертификатом.

Имея такой счетчик трафика, можно связать продажи и их объемы с источниками трафика и расходами.

3. Платные поисковые системы

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

1. Само слово (его текст). Некоторые из поисковых систем обладают идентификаторами слов (Google, MSN), другие не обладают (MIVA).

2. Способ сопоставления ключевого слова и поискового запроса (полное совпадение, совпадение с учетом словоформ и пр.).

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

4. Кампания. Понятие кампании существует не на всех поисковых системах, например, такие системы как MIVA, WebFinder, Mirago не имеют кампаний. Основные системы – Google, Yahoo, MSN, ASK, LookSmart и пр. обладают кампаниями.

5. Аккаунт на поисковой системе. Вы можете иметь несколько аккаунтов на одной системе с похожим набором слов.

6. Ad. Понятие Ad также есть не на всех поисковых системах, например, его нет на MIVA, WebFinder и пр. Для привязки продаж к ключевым словам знать Ad не обязательно, однако же, он может быть необходим для определения продаж по результатам рекламы в контекстных сетях (например, Google Аdsense).

Определить эти показатели через анализ реферреров на входной странице невозможно в силу ряда причин:

• Платные поисковые системы часто используются партнерами, которые размещают на своих сайтах результаты поиска или контекстную рекламу. В этом случае в качестве реферрера будет выступать неизвестный вам сайт, хотя, на самом деле, данный заход является для вас платным и вы обязаны за него заплатить.

• В случае Google Adsense в качестве реферрера будет GoogleSyndication, который также не предоставит никакой информации.

• Даже в случае, когда реферрер содержит поисковый запрос, все равно невозможно определить способ сопоставления, группу, кампанию и Ad.

Поэтому единственный способ определить все эти параметры – это заранее поместить их в URL ключевого слова или Ad'а.

Т.е. если URL вашего сайта выглядит так –

http://www.mysite.com/

То URL'ы ключевых слов будут выглядеть примерно так –

http://www.mysite.com/?ppc=google&keyword={keyword}&Ad={creative}&MatchType=Standard&AdGroup=12345&Campaign=67890

Здесь я привел общий вид URL’а – на разных поисковых системах он будет выглядеть по-разному, так как набор параметров будет различаться.

Как видим, в URL были добавлены дополнительные параметры, а именно:

• ppc – поисковая система или аккаунт на поисковой системе. В примере я привел значение google, но оно не обязательно должно содержать имя поисковой системы. Если вы имеете несколько аккаунтов, то оно может быть, например, google1 или google2, MyYahoo и т.п.

• Keyword – ключевое слово. Здесь в качестве значения я привел шаблон {keyword} который работает на Google, Yahoo и MSN. В момент нажатия на ссылку он будет заменен на текст слова, однако другие поисковые системы обладают другими шаблонами или не обладают ими совсем. В этом случае в URL придется помещать текст слова как он есть – т.е. keyword=my+keyword

• Ad – идентификатор Ad'а. Здесь в качестве примера я привел {creative}, который обслуживается Google, но на других системах он пишется по-другому – на MSN - {AdId}, на Yahoo - {YSMADID}.

• MatchType – способ сопоставления ключевого слова и поискового запроса. На разных поисковых системах существует разные способы сопоставлений. Здесь также могут применяться шаблоны – для Yahoo - {YSMMTC}, для MSN – {MathType}. Google не обладает таким шаблоном.

• AdGroup – идентификатор группы слов. Не на всех поисковых системах это понятие выглядит именно как AdGroup – где-то есть такие понятия как категория, листинг и пр., но суть у них одна – группировка слов.

• Campaign – идентификатор кампании. Опять же, не на всех системах есть кампании, но там, где они есть, их нужно определять.

С помещением таких параметров в URL ключевого слова также связаны некоторые проблемы:

1. Если у вас достаточно длинные ключевые слова, то максимальной длины строки URL, принятой в поисковой системе, может не хватить.

2. Набор параметров индивидуален для каждой поисковой системы (я уже отмечал, что кампании есть не везде и что набор способов сопоставления различается), поэтому придется помнить об этих различиях и формировать URL'ы соответствующим образом.

3. Проблема партнеров – партнеры поисковых систем могут кешировать результаты поиска на довольно длительное время. Это приводит к тому, что вы будете получать на сайте заходы по ключевым словам, которые приостановлены или даже удалены. С другой стороны, плюсом добавления параметров в URL является то, что при переходе от партнера, который не кеширует результаты запросов, вы получите его сайт в качестве реферрера, который мог бы попасть в источники трафика сам по себе, как разместивший ссылку на ваш сайт. Но в действительности этот сайт-партнер вас не интересует, так как на самом деле это платный переход и вам необходимо знать эффективность именно ваших ключевых слов, а не партнеров поисковых систем.

4. Проблема закладок – пользователь может попасть на входную страницу, которая содержит дополнительные параметры в URL и сохранить этот URL в Избранное. Если через некоторое время пользователь откроет ваш сайт из Избранного, то ваш счетчик зарегистрирует заход по платному ключевому слову, хотя на самом деле оно таковым не является.

5. Проблема ротации URL'ов (для Google): указав точный URL слова, вы теряете возможность создания нескольких Ad'ов с разными URL'ами – вместо них всегда будут использоваться URL'ы слов.

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

URL’ы Ad’ов тоже нуждаются в аналогичных дополнительных параметрах, так как они могут выступать самостоятельно в контекстных сетях и их эффективность также должна быть оценена.

Имея все эти параметры и сохраняя их в вашем счетчике, вы сможете затем связать их и с продажей при помощи идентификатора сессии посетителя. Вот почему наличие счетчика на входной странице обязательно – без него вы не сможете проанализировать заходы. Однако иногда бывают ситуации, при которых нет возможности поместить счетчик на входную страницу. Например, если она находится не на вашем сайте или если это связано с какими-то техническими или иными трудностями. Для того чтобы решить эту проблему, вы можете применить прием «Redirect». Суть приема в том, чтобы создать дополнительную страницу, которая не будет содержать никакой информации, а только лишь регистрировать факт захода с платной поисковой системы, а затем автоматически направлять посетителя на основной сайт. На следующей схеме я проиллюстрировал, как это работает.

В этом случае для ключевого слова вам вместо URL'а основного сайта потребуется указать URL этой дополнительной страницы. Целевой URL при этом (тот, куда пользователь должен быть перенаправлен) следует также указывать в качестве дополнительного параметра URL'а ключевого слова. Жестко кодировать URL'ы в коде дополнительной страницы не стоит, так как при этом вы лишитесь гибкости управления словами. Таким образом, URL’ы слов будут иметь следующий вид:

http://www.mysite.com/redirectpage.aspx?ppc=google&keyword={keyword}&Ad={creative}&MatchType=Standard&AdGroup=12345&Campaign=67890&DestinationURL= http://www.mysite.com/

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

Понятно, что страница redirectpage должна работать очень устойчиво, так как в случае сбоев в ее работе будет происходить расход средств на поисковой системе, а посетители при этом не будут попадать на сайт.

Таким образом, используя дополнительные параметры в URL'ах, вы сможете накапливать статистику по продажам.

Для определения возврата инвестиций вам потребуется сравнить накопленные таким образом данные со статистикой расходов по ключевым словам и Ad'ам. Эту статистику можно получить с любой поисковой системы, а затем выполнить сравнение этих величин.

При этом главным показателем здесь является ROI = (Profit-Expenses)/ Expenses, который отображает эффективность вложений в то или иное слово.

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

4. Бесплатные поисковые системы

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

Для определения системы и поискового запроса необходимо знать, какими URL'ами могут обладать эти системы (с учетом региональных подсистем) и какие параметры запросов они используют.
Например, если посмотреть на ссылки, которые формирует Google, можно увидеть, что получаются запросы подобного вида:

http://www.google.ru/search?complete=1&hl=ru&newwindow=1&q=my+search+query&lr=&aq=f

Видно, что текст поискового запроса (my search query) находится после параметра q=. Зная параметры поисковых систем, вы сможете получить тексты поисковых запросов, а затем связать с ними продажи, используя идентификатор сессии посетителя.

Для бесплатных поисковых систем нет смысла в подсчете ROI, так как по ним не производится никаких затрат. Поэтому единственные показатели, которые можно посчитать – это объем и количество продаж.

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

5. Партнеры и рассылки

Под партнерами здесь понимаются любые сайты (кроме поисковых систем), которые имеют ссылки на ваш сайт.

Среди них могут быть:

• баннеры, размещенные на сайтах ваших непосредственных партнеров;

• баннеры, размещенные в баннерных сетях;

• ссылки, размещенные на сайтах ваших непосредственных партнеров;

• ссылки, размещенные без вашего участия на каких-либо сайтах в интернете;

• ссылки, размещенные в почтовых рассылках;

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

Для анализа доходности такого рода переходов обычно необходимо получить статистику продаж по следующим направлениям:

1. Сайтам, ссылающимся на ваш сайт.

2. Баннерам и ссылкам, размещенным у партнеров, в баннерных сетях, в почтовых рассылках и пр.

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

Статистику по баннерам и различным ссылкам (в том числе и в рассылках) можно получить, используя промо-код. Промо-код должен находится в URL'ах баннеров и ссылок и должен однозначно их идентифицировать.

Т.е. URL баннера для вашего сайта должен иметь следующий вид:

http://www.mysite.com/?promocode=banner1

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

Затем, имея статистику затрат на размещение баннеров, вы сможете сравнить ее со статистикой продаж и подсчитать ROI баннеров и ссылок аналогично платным словам.

6. Weighting

Описанные выше подходы работают хорошо, но имеют один существенный недостаток, который проще будет продемонстрировать на примерах.

Пример 1.

Предположим, посетитель находит ваш сайт, доходит до просмотра товара и создает закладку.

На следующий день посетитель возвращается по своей закладке и производит покупку.

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

Пример 2.

Предположим, посетитель находит ваш сайт и совершает покупку.

Через непродолжительное время посетитель возвращается на ваш сайт и совершает еще одну покупку, предварительно запомнив его адрес –

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

В качестве решения этой проблемы можно было бы предложить сохранение платного ключевого слова (или другого источника трафика) например, в Cookie посетителя и все его последующие сессии считать относящимися к этому слову. Однако это тоже неверно, что видно на примере 3.

Пример 3.

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

Здесь продемонстрированы два варианта событий.
Первый вариант: две сессии посетителя – первая по платному ключевому слову, вторая – по бесплатному.

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

Здесь видно, что при сохранении первого платного ключевого слова в Cookie возникает вопрос: как быть со вторым переходом? При таком подходе второе слово не будет учтено как источник продаж.

Можно было бы предположить, что правильным решением станет привязка продажи всегда к последнему, а не к первому переходу. Однако и этот подход не лишен недостатка: в этом случае первое слово остается без внимания, несмотря на то, что оно все-таки сыграло определенную роль в привлечении будущего покупателя. Кроме того, нам неизвестно, какой же из переходов больше повлиял на посетителя и привел его к окончательному решению о совершении покупки именно на этом сайте.

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

Кроме того, при таком подходе продажи должны быть ассоциированы не с одним, а с несколькими переходами. Сумма продажи при этом должна распределяться между несколькими источниками трафика. В этом и есть суть подхода, который называется weighting.

Пример работы weighting'а.

Рассмотрим этот более сложный пример, чтобы лучше понять, как работает weighting. Как видим, посетитель три раза заходил на сайт и совершил три покупки. При этом два раза он заходил по различным платным ключевым словам и один раз по закладке. При анализе на основе сессий первая продажа была бы привязана к первому ключевому слову, вторая – ко второму, а третья продажа была бы ни к чему не привязана. При использовании weighting'а первая продажа будет также привязана к первому слову (т.к. это единственный переход данного посетителя на сайт, совершенный до продажи), а вот вторая и третья продажи уже будут отнесены одновременно на первое и второе слово.

Возникает вопрос: каким образом распределить сумму продаж между ключевыми словами? Есть два пути – распределять ее равными частями или с использованием весовых коэффициентов – например, на последнее слово относить 70% суммы, а на первое – 30%. Какой именно способ выбрать, зависит от потребностей конкретного сайта и системы.

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

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

	Привязка
	По сесcиям	Weighting
Первая продажа	Первое ключевое слово	Первое ключевое слово
Вторая продажа	Второе ключевое слово	Первое и второе ключевые слова
Третья продажа	-	Первое и второе ключевые слова

		Объем продаж		ROI
	Затраты	По сесcиям	Weighting	По сесcиям	Weighting
Первое ключевое слово	$50	$100	$100+$200/2+$300/2=$350	100%	600%
Второе ключевое слово	$70	$200	$100+$300/2=$250	186%	257%
Итого	$120	$300	$350+$250=$600	150%	400%

Теперь рассмотрим, как отличаются значения объемов продаж и ROI для метода анализа сессий и weighting'а. Предположим, что продажи посетителя были на сумму $100, $200 и $300 соответственно, а затраты были по ключевым словам были $50 и $70.

Как видим из таблиц, метод weighting’а дает более точное и полное представление о возврате инвестиций в интернет-рекламу и позволяет точнее ассоциировать продажи и источники трафика.

С использованием weighting'а связано несколько вопросов, а именно:

• Для какого количества ключевых слов или других источников трафика следует производить распределение суммы продаж?
Дело в том, что если посетитель пользуется большим количеством различных источников трафика – например, осуществляет сотни переходов по различным ключвым словам – то в этом случае не следует распределять суммы продаж между всеми из них, так как это снизит достоверность метода. Лучше ограничиться количеством из последних 3-5 источников, которые предшествуют продаже.

• Как долго ключевое слово или источник трафика можно считать применимым к вновь поступающим продажам?
В самом деле, если пользователь попал на сайт по платному ключевому слову год назад, затем целый год не заходил на сайт, а потом снова зашел, но уже по другому слову, то вряд ли имеет смысл относить 50% продажи на первое слово, так как это тоже снизит достоверность метода. Лучше ограничиться каким-то осязаемым сроком в 2-3 месяца, в течение которого слова и другие источники трафика будут считаться актуальными для той или иной продажи.

• Как распределять сам факт продажи?
При распределении суммы продаж между несколькими источниками трафика все более-менее ясно – сумма делится равными частями или с учетом весовых коэффициентов. Однако в случае когда кроме учета объемов вы ведете еще и учет количества продаж, для вас может оказаться неприемлемым наличие нецелого числа продаж по какому-либо ключевому слову. Например, при распределении одной продажи между пятью ключевыми словами вы увидите, что каждое из них принесло вам 0.2 продажи. Если такого рода значения не являются для вас источником проблем, то вы можете все оставить, как есть. Если же для вас это не подходит, то вам придется оставить факт продажи неделимым, привязав его, например, к последнему слову из пяти. При этом, однако, при просмотре отчетности вы увидите, что первые четыре слова принесли вам некоторую прибыль и при этом 0 продаж. Т.е. в этом случае вам придется держать в голове такую особенность учета.

• Производительность
Если сайт достаточно популярен (имеет сотни миллионов посетителей), то для него использование weighting'а может быть достаточно затратным, так как потребуется хранить и анализировать историю действий посетителей, которая будет довольно внушительной.

• Платежные системы
Если посмотреть на самый первый рисунок мастер-класса, то, с точки зрения weighting'а, все может выглядеть так:

Видим, что платежная система в таком случае может быть воспринята как источник трафика (сайт-партнер), так как переход с нее предшествует факту продажи, и в этом случае на нее будет отнесено 50% продажи. Для устранения этого эффекта вам необходимо знать адреса платежных систем (а возможно, и других сайтов), которые необходимо будет исключить из анализа путем weighting'а.

7. Заключение

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

Если у вас есть любые вопросы, комментарии или пожелания по поводу этого материала или данной тематики, пожалуйста, направляйте их мне через http://www.msmirnov.ru/ - я всегда буду рад их получить и обсудить.

Опыт практического применения t-sql в системах распределенных баз данных

Здесь я привожу статью "Опыт практического применения t-sql в системах распределенных баз данных", опубликованную совместно с Андреем Смирновым в журнале RSDN Magazine #2 2007 г http://www.rsdn.ru/article/db/tsql.xml

Реализация параллельного выполнения запросов на T-SQL

ПРИМЕЧАНИЕ Стоит сразу оговориться, речь не пойдет о реализации многопоточности в ее классическом понимании, т.е. о создании потоков, объектов синхронизации и прочих сопутствующих сущностей. Речь пойдет об имитации многопоточного выполнения t-sql кода в распределенной системе (на нескольких linked серверах) и синхронизации этого кода средствами, предоставляемого sql server job agent.

Всем известно, что в MS SQL Server отсутствует встроенный механизм асинхронного выполнения запросов – вы не можете из одного запроса или хранимой процедуры запустить другой запрос так, чтобы ваш запрос продолжил свою работу, не дожидаясь его окончания. Однако в некоторых случаях такая возможность может оказаться весьма полезной. Например, если ваш запрос или процедура проводит подготовку каких-либо данных, затем запускает несколько различных, независимых друг от друга процедур, а затем выполняет некоторые завершающие дествия. В обычном случае вы вынуждены запускать эти процедуры последовательно, одну за другой. Однако, было бы намного удобнее, если бы у вас была возможность запустить их параллельно. Это позволило бы существенно сэкономить время и не повлияло бы на работу этих процедур, так как они не зависят друг от друга.
Продемонстрируем это на диаграмме:

1. Классический вариант. Последовательный вызов процедур.

Рисунок 1.

1. Второй вариант. Параллельный запуск процедур из тела основной процедуры или запроса.

Рисунок 2.

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

• Есть несколько подряд идущих процессов обработки данных, последовательность выполнения которых не важна, и которые логически не зависят друг от друга.
• Выполнение процессов происходит на разных серверах или на одном сервере, но важна синхронизация их завершения (т.е. в ситуациях, когда итоговому процессу требуются результаты выполнения нескольких процессов – см. диаграмму 2).
• Параллельно выполняемые запросы являются достаточно независимыми в части обновления, удаления или вставки данных. В противном случае ни о каком выигрыше в производительности не может быть и речи. Напротив, результатом будет только падение производительности из-за ожидания процессами друг друга на защелках (“pageiolatch”) или блокировках (“lock”).
• Для большей отдачи от данного подхода рекомендуется, чтобы каждый из процессов использовал таблицы, располагающиеся в разных файловых группах, идеально – когда еще и на отдельных дисковых устройствах, хотя это и не обязательно.

Когда не стоит применять данный подход:

• Процессы пытаются добавлять, удалять, обновлять данные в одной таблице.
• Есть дефицит ресурсов памяти.
• Слабая дисковая подсистема

Предварительная настройка

Для демонстрации параллельного выполнения запросов необходимо иметь два экземпляра SQL Server. Между ними должны быть корректно настроены связи (“linked server”). Назовем их linked_server_01 для первого сервера, linked_server_02 – для второго. Для корректной работы приложенных примеров необходимо также, чтобы у каждого из серверов была настроена связь, ссылающаяся на самого себя. Важно правильно настроить security context для каждого из linked server-ов – доступ должен осуществляться от имени пользователя, имеющего набор прав для управления job’ами – в простейшем случае sysadmin. Для простоты можно установить security context от имени sa. В настройках server options каждого из linked server необходимо установить свойства RPC и RPC Out.
Более того, на каждом из серверов должен быть запущен SQL Server Job Agent.
К данной статье прилагается два набора SQL-скриптов. Первый из них реализует всю функциональность, описываемую в этой статье. Он содержит весь набор хранимых процедур, который потребуется для реализации описываемого механизма. Необходимо создать отдельную базу Tools, в которой следует выполнить данные запросы, создав, таким образом, все процедуры. Второй прилагаемый набор скриптов демонстрирует пример использования данного механизма. Вы можете создать базу Article_Sample_Data на первом сервере и выполнить на ней второй прилагаемый скрипт.
Проверить правильность настройки можно, запустив на каждом из серверов системную процедуру sp_helpserver.
Вы должны получить следующий результат:

Рисунок 3.

Здесь видно, что rpc, rpc out свойства linked_server-ов настроены правильно.
Для корректной работы скриптов на linked server-ах также необходимо убедиться в том, что на каждом из серверов работает служба Microsoft Distributed Transaction Coordinator (о проблемах с ее настройкой читайте дальше в разделе “ Описание проблем при настройке DTC и возможные их решения”). MS DTC используется MS SQL Server для поддержки распределенных транзакций (а здесь будет именно такой случай). Проверить работоспособность msdtc можно, запустив на каждом из MSSQL серверов следующий скрипт:

begin transaction execute linked_server_01.article_sample_data.dbo.sp_executesql N'select getdate()' execute linked_server_02.article_sample_data.dbo.sp_executesql N'select getdate()' commit

Если вы получили текущую дату, то можно сразу перейти к описанию реализации, в противном случае посмотрите подраздел статьи «Описание проблем при настройке DTC и возможные их решения»

Описание реализации

Как уже упоминалось, никаких встроенных средств для реализации параллельного исполнения запросов в t-sql нет, но есть SQL Server Job Agent, отвечающий за выполнение запросов по расписанию. Так что параллельного исполнения T-SQL можно добиться через создание заданий (“job”) для агента. Именно этот подход и рассматривается в этой статье. Стоит отметить, что он дает возможность параллельного выполнения запросов не только на одном сервере, но и на нескольких.
Кроме того, данный подход предоставляет возможность ограничения количества одновременно выполняющихся процессов. Если количество асинхронных запросов в пуле превышает установленный максимум, то запросы дожидаются своей очереди и обрабатываются по мере высвобождения ресурсов сервера. Такая необходимость возникает, если сервер ограничен в ресурсах памяти.
В самом общем случае такая система состоит из центрального управляющего сервера, на котором располагается пул запросов, и произвольного количества управляемых серверов, которым запросы передаются на исполнение (в нашем случае управляющим сервером будет linked_server_01).

Рисунок 4.

Единственным необходимым условием работы такой системы является возможность подключения серверов к центральному серверу как linked server (на каждом из серверов должен быть запущен Sql Server Job Agent). Это необходимо для того, чтобы предоставить возможность центральному серверу контролировать ход исполнения задач.
В простейшем случае, когда требуется выполнение нескольких задач в параллельном режиме, никаких дополнительных настроек или установки linked server на себя не требуется.
Целиком весь SQL-код, реализующий управление очередью заданий, можно увидеть в коде хранимых процедур прилагаемой базы данных Tools (см. Предварительная настройка).
Этот код можно изучить и детально обсудить с авторами – здесь же мы приводим только описание его «интерфейсной» части, доступной пользователю.
Использование данного механизма состоит из двух этапов – подготовка списка запросов и собственно его обработка.
Пул запросов должен содержаться в таблице со следующей структурой:

create table [TaskList] ( linked_server nvarchar(100) DEFAULT (''), job_name nvarchar(100) DEFAULT (''), toDelete bit, checkRun bit, job_step_name nvarchar(200), job_step_sql_script nvarchar(4000), job_step_database nvarchar(200), status bit NULL DEFAULT (1), job_started bit NULL DEFAULT (0), job_prepare_for_synchronize bit NULL DEFAULT (0), job_executed bit NULL DEFAULT (0), id int identity(1,1) NOT NULL )

Из всех полей этой таблицы обязательными к заполнению являются следующие:

1. job_name – имя задания, которое будет создано для выполнения запроса. Это имя может помочь вам ориентироваться среди заданий, чтобы определить время или успешность его выполнения.
2. toDelete – параметр, определяющий, следует ли удалять задание после его завершения.
3. linked_server – имя сервера, на котором должен быть выполнен запрос.
4. job_step_database – имя базы данных, к которой адресуется запрос.
5. job_step_name – имя шага (“step”), который будет создан в задании (в данной реализации в каждом задании создается только один шаг).
6. job_step_sql_script – это тот самый запрос (T-SQL), который должен быть выполнен в асинхронном режиме.

Остальные поля используются в процессе управления запросами.
В качестве простейшего примера приведем формирование пула запросов:

insert into [TaskList] ( linked_server, job_name, toDelete, job_step_name, job_step_sql_script, job_step_database) select '', 'demo_UpdateCustomers', 1, 'step 01', N'update Customers set Region = ‘’N/A’’ where Region is null', 'Northwind' ---------------------------------------------------------------------------- insert into [TasksList] ( linked_server, job_name, toDelete, job_step_name, job_step_sql_script, job_step_database) select '', 'demo_RemoveOrders', 1, 'step 01', N'delete from Orders where ShipCity is null', 'Northwind' ---------------------------------------------------------------------------- insert into [TasksList] ( linked_server, job_name, toDelete, job_step_name, job_step_sql_script, job_step_database) select '', 'update_Employees', 1, 'step 01', N'update Employees set Region = ‘’London’’ where Country = ‘’UK’’', 'Northwind'

Как вы видите, эти SQL-запросы подготавливают пул из трех запросов на текущем сервере в базе Northwind:

update Customers set Region = ’N/A’ where Region is null delete from Orders where ShipCity is null update Employees set Region = ‘London’ where Country = ‘UK’

Данные запросы довольно просты, но понятно, что вместо них могут использоваться сложные запросы или вызовы хранимых процедур, выполнение которых может занимать несколько часов.
Для запуска этих запросов в параллельном режиме используется процедура support_StartJobsAndWaitFor (Tools.dbo.support_StartJobsAndWaitFor), которая реализует всю логику управления пулом, запуска запросов и обработки результатов их работы.
Основные параметры процедуры:

CREATE PROCEDURE [dbo].[support_StartJobsAndWaitFor] @temporary_table_name nvarchar(100), @SimultaneousJobsMaxCount tinyint = 10

Здесь @temporary_table_name – имя таблицы-списка запросов, @SimultaneousJobsMaxCount – максимальное количество параллельно запущенных задач.
Примером вызова может быть следующий запрос:

execute support_StartJobsAndWaitFor @temporary_table_name = 'Northwind.dbo.[TasksList]'

После запуска процедуры три подготовленных запроса будут выполнены в параллельном режиме:

Рисунок 5.

Все задания, созданные процедурой support_StartJobsAndWaitFor, будут запущены, и по их окончании процедура закончит свою работу.
Рассмотрим более сложный пример: допустим, требуется произвести вышеупомянутые t-sql-операции на двух настроенных серверах параллельно. Более того, допустим, что одна из выполняемых операций приведет к ошибке:

Рисунок 6.

Данная ситуация реализована в коде хранимой процедуры Linked_server_01.Article_Sample_Data.dbo.demo_StartJobsAndWaitFor.
Запустим ее на выполнение, предварительно открыв Job Activity Monitor на серверах. Мы должны увидеть следующее:
На linked_server_01 были созданы и выполнены следующие задания:

Рисунок 7.

На linked_server_02 также были созданы и выполнены следующие задания:

Рисунок 8.

После выполнения мы увидим сообщение об ошибке выполнения последнего задания на linked_server_02:

Рисунок 9.

Скрипт ниже реализует этот пример – он создает и запускает по три запроса на каждом из linked server-ов. При этом в одном из запросов содержится заведомо некорректный t-sql-код, сообщение об ошибке выполнения которого мы увидим после выполнения всех процессов:

-- ------------------------------------------------------------------------ -- 1.2 Формируем список job-ов -- ------------------------------------------------------------------------ insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select '', 'demo_UpdateCustomers', 0, 1, 'step 01', N'update Customers set Region = ''N/A'' where Region is null', 'Northwind' -- ------------------------------------------------------------------------ insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select '', 'demo_RemoveOrders', 0, 1, 'step 01', N'delete from Orders where ShipCity is null', 'Northwind' -- ------------------------------------------------------------------------ insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select '', 'update_Employees', 0, 1, 'step 01', N'update Employees set Region = ''London'' where Country = ''UK''', 'Northwind' -- ------------------------------------------------------------------------ -- !!! -- Регистрация задачи на удаленном linked_server insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select 'linked_server_02', 'demo_UpdateCustomers', 0, 1, 'step 01', N'update Customers set Region = ''N/A'' where Region is null', 'Northwind' insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select 'linked_server_02', 'demo_RemoveOrders', 0, 1, 'step 01', N'delete from Orders where ShipCity is null', 'Northwind' insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select 'linked_server_02', 'update_Employees', 0, 1, 'step 01', N'update Employees set Region = ''London'' where Country = ''UK''', 'Northwind' -- Регистрация задачи на удаленном linked_server -- с заведомо некоректным t-sql кодом insert into [TaskList] ( linked_server, job_name, toDelete, checkRun, job_step_name, job_step_sql_script, job_step_database) select 'linked_server_02', 'Incorrect democode', 0, 1, 'step 01', N'incorrect t-sql code', 'Northwind' -- ------------------------------------------------------------------------ -- 1.3 Запускаем подготовленные job-ы и ждем их завершения -- ------------------------------------------------------------------------ -- executing and wait for execute Tools.dbo.support_StartJobsAndWaitFor @temporary_table_name = 'linked_server_01.Article_Sample_Data.dbo.[TaskList]', -- Максимальное количество одновременно работающих @SimultaneousJobsMaxCount = 03, -- timeOut между итерациями проверки статуса job-ов @delay_time = '00:00:03', @delete_jobs = 0

Комментарии к коду примера

Как указывалось выше, процедура Tools.dbo.support_StartJobsAndWaitFor выполняет всю работу по управлению пулом. В качестве ее параметров указывается количество одновременно запущенных задач и время между периодическими опросами работающих процессов (параметр @delay_time – что-то вроде Thread.Sleep (), на самом деле waitfor delay). Параметр @temporary_table_name – для данного примера мы указываем полный путь к таблице описания пула запросов, видимой как с linked_server_01, так и с linked_server_02 – поэтому здесь и уделено особое внимание описанию правильной настройки межсерверных связей на обоих серверах в самом начале статьи.
Если вы посмотрите свойства любого созданного задания, то увидите не тот скрипт, который поместили в таблицу [TaskList], а немного видоизмененный, например, такой:

declare @sql_script nvarchar(4000) set @sql_script = ' update TaskList set job_started = 1 where linked_server = ''linked_server_02'' and job_name = ''demo_UpdateCustomers'' ' execute linked_server_01.Article_Sample_Data.dbo.sp_executesql @sql_script update Customers set Region = 'N/A' where Region is null

Обновление флага job_started требуется для того, чтобы зафиксировать момент старта задания. Дело в том, что SQL Server не запускает задание на выполнение непосредственно в тот момент, когда получает соответствующую команду. Если в этот момент нагрузка на сервер будет достаточно высока или количество работающих заданий будет достаточно велико, то он отложит момент старта на неопределенное время – до тех пор, пока не посчитает текущую нагрузку приемлемой. Все это время задание будет находиться в состоянии ожидания – промежуточном состоянии между Not Running и Executing. Поэтому, в общем случае, не стоит ожидать от SQL Server запуска ваших заданий точно в то время, на которое они были запланированы. Однако требуется где-то зафиксировать момент начала выполнения задания, и только после этого начинать анализировать его состояние – в работе оно или уже закончило свое выполнение. Единственным возможным подходом представляется изменение скрипта самого задания – первым делом после старта задание само изменяет свой статус в соответствующей таблице.

Описание проблем при настройке DTC и возможные их решения

Сообщение об ошибке:

ПРЕДУПРЕЖДЕНИЕ MSDTC on server '' is unavailable.

Возможные варианты разрешения проблемы:
Необходимо запустить службу msdtc. Для этого в командной строке нужно запустить dcomcnfg (Component services), в списке computers найти текущий, из контекстного меню вызвать start MS DTC.

Рисунок 10.

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

1. Remote Procedure Call (RPC) Locator.
2. Remote Procedure Call (RPC).
3. Должен быть запущен COM+ System Application – вот с запуском этой службы могут возникнуть проблемы. После старта может быть выдано сообщение о том, что сервис не может стартовать. В таком случае необходимо просмотреть event log – если он содержит сообщение такого плана:

Event Type: Error Event Source: COM+ Event Category: (98) Event ID: 4822 Date: 4/22/2007 Time: 1:08:54 PM User: N/A Computer: DUSHES-MOBILE Description: A condition has occurred that indicates this COM+ application is in an unstable state or is not functioning correctly. Assertion Failure: SUCCEEDED(hr) Server Application ID: {02D4B3F1-FD88-11D1-960D-00805FC79235} Server Application Instance ID: {88E823F3-8336-4499-82BF-5670D772B9F7} Server Application Name: System Application The serious nature of this error has caused the process to terminate. Error Code = 0x8000ffff : Catastrophic failure COM+ Services Internals Information: File: d:\qxp_slp\com\com1x\src\comsvcs\tracker\trksvr\trksvrimpl.cpp, Line: 3000 Comsvcs.dll file version: ENU 2001.12.4414.258 shp

то нужно поступить по инструкции:

1. Click Start, click Run, type secpol.msc, and then click OK. 2. In the left pane, expand Local Policies, and then click User Rights Assignment. 3. In the right pane, double-click Impersonate a client after authentication. 4. Click Add User or Group. 5. Type service in the Enter the object names to select box, and then click OK two times. 6. Restart the computer.

1. Щелкнуть по «Start», выбрать пункт «Run», ввести команду «secpol.msc», и затем щелкнуть по «ОК» — откроется окно консоли «Local Security Settings».
2. В левой панели окна консоли развернуть ветку «Local Policies» и затем выбрать «User Rights Assignment».
3. В правой панели консоли двойным щелчком мыши вызвать диалог «Impersonate a client after authentication».
4. Щелкнуть по «Add User or Group».
5. Ввести в текстовое поле «the Enter the object names to select box» значение «service» и затем дважды нажать «OК», закрыв все открытые диалоги консоли «Local Security Settings».
6. Перезапустить компьютер.
Если же и после этого сервис COM+ System Application не запускается, тогда последнее, что можно сделать, это перерегистрировать следующие com-серверы:

regsvr32 comsvcs.dll regsvr32 ole32.dll regsvr32 oleaut32.dll

Если выдается сообщение об ошибке:

ПРЕДУПРЕЖДЕНИЕ The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300) [SQLSTATE 01000] (Error 7312). The step failed.

То, возможно, неправильно настроена Security Configuration у службы MSDTC. Для ее настройки нужно открыть Component Services, в списке Computers выбрать текущий, в контекстном меню выбрать properties, в открывшемся диалоге – вкладку MSDTC, и внести, например, такие настройки:

Рисунок 11.

Master.dbo.sysProcesses как альтернатива Activity monitor, недокументированные возможности

ПРЕДУПРЕЖДЕНИЕ Нижеприведенный код будет работать на MSSQL 2000 Server только при условии установки Service Pack 3 и выше. Код является рабочим также и для MS SQL 2005.

Activity monitor из состава MS SQL Server 2005 и вкладка Current Activity в Enterprise manager из состава MS SQL Server 2000 являются очень полезными инструментами для поиска проблемных мест в текущей работе сервера. Они позволяют обнаружить проблемную сессию, причину возникновения проблемы и многое другое - например, заблокированную сессию, ресурс, из-за которого произошла блокировка, список текущих сессий, их состояния и т.д. О работе этих инструментов написано достаточно, и не имеет смысла описывать здесь их функциональность. Однако часто приходится видеть процесс, очень долго выполняющий какой-либо пакет SQL-команд – и хотелось бы иметь возможность узнать, а какая именно команда выполняется в текущий момент времени.
Оказывается, все это можно увидеть и получить с помощью master.dbo.sysprocesses.
Для следующей демонстрации стоит открыть две сессии, необязательно на тех базах данных, скрипты для создания которых идут вместе с данной статьей. В первой сессии выполним следующее:

select @@spid waitfor delay '00:10'

Сначала запустим только select @@spid, чтобы увидеть spid текущей сессии, потом можно запустить весь скрипт – для данной задачи нам нужно запомнить только идентификатор текущей сессии (в моем случае это было значение 57).
Во второй сессии выполним следующее:

select spid, cpu, stmt_start, stmt_end, sql_handle, * from master..sysprocesses processes (nolock) where dbid = db_id() order by processes.cpu desc

Получим результат, аналогичный показанному ниже:

Рисунок 12.

Обратите внимание на поля sql_handle, stmt_start, stmt_end. Зная sql_handle, можно получить t-sql-код, который выполняется в текущей сессии, соответственно поля stmt_start и stmt_end – начальная и конечная позиция текущей команды в batch. Поскольку используется unicode, то придется произвести дополнительно еще и деление на 2 для получения реальной позиции в тексте.
Пока в первой сессии выполняется скрипт waitfor, во второй запустим следующее (после предыдущего select):

Declare @sql_handle varbinary(20), @sql_text nvarchar(4000), @stmt_start int, @stmt_end int select @sql_handle = sql_handle, @stmt_start = stmt_start, @stmt_end = stmt_end from master..sysprocesses (nolock) where spid = 57 select * from ::fn_get_sql(@sql_handle) select @sql_text = [text] from ::fn_get_sql(@sql_handle) select @sql_text as all_executed_code, substring(@sql_text, @stmt_start/ 2, case when @stmt_end > 0 then @stmt_end /2 else len(@sql_text) end) as current_executed_code

Здесь 57 – это идентификатор первой сессии. У вас это значение будет другим.
Здесь использована недокументированная функция ::fn_get_sql(varbinary sql handle), которая по полученному sql_handle сессии возвращает весь выполняющийся t-sql код (собственно, это то, что мы можем увидеть в activity monitor, щелкнув по интересующей нас сессии). Результат выполнения:

Рисунок 13.

Во второй таблице показан весь пакет команд, выполняемых в текущей сессии, а также текущая инструкция waitfor delay (для получения которой использованы поля stmt_start, stmt_end). Именно значения полей stmt_start и stmt_end и дают нам возможность из всего пакета команд увидеть только те инструкции, которые реально выполняются в текущий момент. Данные значения содержат смещения начала и конца текущего кода в байтах.
В коде:

select @sql_text as all_executed_code, substring( @sql_text, @stmt_start/ 2, case when @stmt_end > 0 then @stmt_end /2 else len(@sql_text) end) as current_executed_code

для получения кода текущей команды при помощи substring мы делим значения полей stmt_start|end на 2 намеренно, чтобы получить позиции начала и конца кода в строке всего пакета команда в формате unicode (в котором, как известно, для хранения символа используется два байта).

Динамические скрипты, вызов на linked_server

На форумах часто приходится видеть примерно такие вопросы: «Как получить данные из таблицы, имя которой зависит от ряда причин?». Ответом, как правило, является совет использовать динамические скрипты (хотя, в зависимости от задачи, возникают и варианты использования разветвления кода через if). В своей практике мы тоже часто используем динамические скрипты, например, в таких случаях, когда условие отбора записей where неизвестно и зависит от определенной логики, для описанного случая с именем таблицы, и прочих. Но отдельно нужно выделить те случаи, когда скрипт необходимо запустить на другом сервере, имя которого на момент написания скрипта неизвестно или может меняться в зависимости от каких-либо условий.

СОВЕТ Для запуска динамических скриптов один из авторов использует вызов системной процедуры sp_executesql, у которой, по сравнению с инструкцией execute(N’string’), синтаксис гораздо богаче и позволяет параметризовать скрипт. Это удобнее для восприятия (хотя и имеется ограничение на максимальную длину скрипта в 4000 символов, в отличие от execute(N’string’), позволяющей произвести конкатенацию нескольких строк максимальной длиной по 4000 символов – речь идет об nvarchar type). Поясним на примере: -- таблица, используемая для теста динамического скрипта if ( isnull(object_id('tempdb..#test'), 0) > 0 ) drop table #test create table #test ( id int identity, [value] int ) insert into #test ([value]) select 1 union select 2 union select 3 union select 4 union select 5 declare @sql_script nvarchar(4000), @where_value int -- значение для отбора записей по полю [value] set @where_value = 3 -- использование dynamic script через execute (N'string') set @sql_script = N' select * from #test where [value] >= ' + cast(@where_value as nvarchar(10)) execute(@sql_script) -- использование dynamic script через sp_executesql set @sql_script = N' select * from #test where [value] >= @value ' execute sp_executesql @sql_script, N'@value int', @value = @where_value Если в случае использования execute(N’string’) мы сформировали строку, где для условия where было подставлено определенное значение посредством приведения числового значения переменной @where_value к типу данных nvarchar(), то во втором случае мы явно указали объявление переменной @value, указали ее тип, и уже затем использовали ее в логике самого запроса, проинициализировав при вызове dynamic script переменную @value значением @where_value. Возможно, данный пример не является показательным, но во втором случае мы имеем явное указание параметров запроса, видим их использование в самом запросе, видим их инициализацию, аналогично объявлению хранимой процедуры и ее вызову.

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

declare @destination_linked_server nvarchar(100), @source_linked_server nvarchar(100), @execute_script nvarchar(4000), @sql_script nvarchar(4000) set @destination_linked_server = 'linked_server_02' set @source_linked_server = 'linked_server_01' -- 1. -- на указанном сервере нужно просто запустить хранимую процедуру ... -- для данного случая подразумеваем, что наша БД указана, -- путь это будет TempDB, а вызвана будет просто -- системная процедура (хотя можно было подставить имя своей БД -- и имя конкретной процедуры) set @execute_script = 'execute ' + @destination_linked_server + '.TempDB.dbo.sp_helpserver' execute sp_executesql @execute_script -- если проанализировать профайлером, то такой вызов -- ничем не отличается от прямого вызова -- execute linked_server_02.TempDB.dbo.sp_helpserver -- 2. -- Во втором случае рассматривается ситуация, когда имя linked–сервера -- неизвестно на момент вызова скрипта, более того, -- сам скрипт должен работать на linked–сервере -- (скажем, затянуть данные с другого сервера, может потребоваться -- для реализации метода pull для передачи данных между серверами; -- о разнице между pull и push говорится в разделе -- "Как вернуть данные из temporary table чужой сессии") set @sql_script = ' select * from ' + @source_linked_server + '.master.dbo.sysservers ' set @execute_script = 'execute ' + @destination_linked_server + '.TempDB.dbo.sp_executesql @outer_sql_script' execute sp_executesql @execute_script, N'@outer_sql_script nvarchar(4000)', @outer_sql_script = @sql_script

Результат выполнения должен быть приблизительно таким:

Рисунок 14.

В приведенном примере рассмотрены два случая. В первом осуществляется вызов хранимой процедуры для известной БД с указанного через переменную @destination_linked_server linked-сервера.
Во втором случае имеется текст запроса, который должен быть вызван на стороне указанного сервера. Т.е. мы имеем ситуацию, когда один динамический скрипт вызывает другой.
Схематично это можно изобразить так:

Рисунок 15.

В данном случае мы сначала формируем скрипт для вызова sp_executesql на linked-сервере, т.е. в данном контексте это будет внутренний скрипт:

set @execute_script = 'execute ' + @destination_linked_server + '.TempDB.dbo.sp_executesql @outer_sql_script'

Также укажем, что внутренний скрипт должен запустить @outer_sql_script, передаваемый снаружи. То есть @outer_sql_script является просто переменной, которую мы объявим во внешнем скрипте и проинициализируем необходимым значением (т.е. практически то же самое, что и выше, при использовании переменной @value). Наконец, во внешнем скрипте:

execute sp_executesql @execute_script, N'@outer_sql_script nvarchar(4000)', @outer_sql_script = @sql_script

мы объявляем переменную @outer_sql_script, которая будет использована в скрипте @execute_script (во внутреннем), и инициализируем ее значением @sql_script.
Если проанализировать работу такого кода профайлером, можно убедиться, что запуск скрипта

select * from linked_server_01.master.dbo.sysservers

на удаленном сервере (@destination_linked_server) ничем не отличается от нашего вызова.

Проверка linked_server на loopback

Иногда возникает необходимость проверить, ссылается linked_server сам на себя или нет (является ли он loopback-сервером) – хотя бы для того, чтобы избежать проблем, описанных в BOL (только при использовании на серверах MS SQL 2000, проблема loopback linked servers решена на MS SQL 2005).

ПРЕДУПРЕЖДЕНИЕ Loopback-серверы нельзя использовать в распределенной транзакции. Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction causes an error:

Описание реализации

Собственно, алгоритм достаточно прост. Ищем указанный сервер в списке master.dbo.sysservers, анализируем DataSource на соответствие хосту текущего сервера (host_name()) или IP-адресу (IP-адреса текущего сервера получаем, запустив через xp_cmdShell команду ipconfig) – если есть совпадение, то считаем что linked_server ссылается сам на себя. Собственно, данный алгоритм и реализован в коде прилагаемой хранимой процедуры Tools.dbo.support_CheckLinkedServer.
Для демонстрации запустим следующий скрипт на ранее настроенном сервере, где размещена база данных Tools:

declare @isRemote bit declare @isExists bit -- loopback server execute linked_server_01.Tools.dbo.[support_CheckLinkedServer] @linked_server_name = 'linked_server_01', @isRemote = @isRemote output, @isExists = @isExists output select @isRemote, @isExists -- not loopback execute linked_server_01.Tools.dbo.[support_CheckLinkedServer] @linked_server_name = 'linked_server_02', @isRemote = @isRemote output, @isExists = @isExists output select @isRemote, @isExists -- linked server not exists execute linked_server_01.Tools.dbo.[support_CheckLinkedServer] @linked_server_name = 'not_exists_linked_server', @isRemote = @isRemote output, @isExists = @isExists output select @isRemote, @isExists

Результаты выполнения должны быть следующими:

Рисунок 16.

Т.е. первый сервер существует, но ссылается сам на себя (isRemote = 0, сервер не удаленный), второй сервер реально существует и является удаленным, третий сервер заведомо не существует – isЕxists = 0.

Как вернуть данные из temporary table чужой сессии (только mssql2000)

ПРЕДУПРЕЖДЕНИЕ Все нижеописанное относится только к MSSQL 2000, для MSSQL 2005 описанный подход работать не будет.

Известно, что временная таблица (речь идет о локальных временных таблицах) живет только в пределах одной сессии, заканчивается сессия – удаляется временная таблица. Доступ к временной таблице ограничен рамками текущей сессии. Но существует прием, с помощью которого из одной сессии можно получить данные из временной таблицы другой сессии. В общем-то, такой прием был не один раз описан на www.sql.ru.
Итак, для демонстрации сделаем следующее – в той сессии, где мы собираемся создать тестовую временную таблицу, запустим следующий код:

select @@spid create table #test_table (id int identity, test varchar(100)) insert into #test_table select 'string_01' union all select 'string_02' select * from #test_table select object_id('tempdb..#test_table')

Mы должны увидеть @@spid текущей сессии, выборку из двух записей и ID временной таблицы. Зная, что временные таблицы размещаются в базе данных tempdb, мы можем также получить object_id такой временной таблицы. Результат выполнения данного скрипта:

Рисунок 17.

Откроем другую сессию. Зная object_ID временной таблицы из другой сессии, можно увидеть ее реальное имя. Для этого выполним следующий скрипт (вместо id подставьте свое полученное значение):

select * from tempdb..sysobjects(nolock) where id = 761105802

Результат:

Рисунок 18.

Т.е. мы получили реальное имя временной таблицы, используемой в другой сессии. Но попытка использования такого имени для каких-либо запросов ни к чему не приведет. Запрос вида

select * from tempdb..[#test_table_________________________________________________________________________________________________________000000000048]

приведет к сообщению об ошибке:

ПРЕДУПРЕЖДЕНИЕ Database name 'tempdb' ignored, referencing object in tempdb. Msg 208, Level 16, State 0, Line 1 Invalid object name '#test_table_________________________________________________________________________________________________________000000000048'.

Теперь мы дошли до самой сути. Чтобы получить данные из такой таблицы, нужно обновить ее имя в системном каталоге, дав новое уникальное имя согласно правилам именования объектов в MSSQL. Т.е. для примера можно сделать следующее:

declare @table_name varchar(100) set @table_name = 'temp_' + cast(newid() as varchar(36)) update tempdb..sysobjects set name = @table_name where id = 761105802

Здесь также может возникнуть проблема, а именно, можно получить следующее сообщение об ошибке:

ПРЕДУПРЕЖДЕНИЕ Msg 259, Level 16, State 2, Line 3 Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

Это значит, что мы не можем править содержимое системного каталога. Чтобы все-таки получить такую возможность, нужно правильно настроить сервер. Запустим следующий скрипт:

execute sp_configure 'allow updates', 1 reconfigure with override execute sp_configure

Результат будет приблизительно таким:

Рисунок 19.

Нас интересует настройка сервера allow updates. Данная настройка позволяет править системный каталог, в частности, позволит произвести правку имени временной таблицы в tempdb..sysobjects.
Запустим скрипт:

declare @table_name varchar(100) set @table_name = 'temp_' + cast(newid() as varchar(36)) update tempdb..sysobjects set name = @table_name where id = 761105802 select * from tempdb..sysobjects(nolock) where id = 761105802

Данный скрипт вернет новое имя таблицы. В моем случае это было значение [temp_8A04E767-299B-48DE-A4A8-B17F70BCCEF5]. Теперь, находясь в этой же сессии, выполнив следующий скрипт, подставив новое имя временной таблицы, мы получим реальные данные из временной таблицы из другой сессии:

select * from tempdb..[temp_8A04E767-299B-48DE-A4A8-B17F70BCCEF5]

Результат выполнения должен быть таким:

Рисунок 20.

Остается добавить, что изменение имени таблицы в tempdb..sysobjects никоим образом не повлияет на работоспособность запросов к исходной временной таблице в первой сессии. В этом легко убедиться, запустив любую из DML-инструкций к таблице.

ПРИМЕЧАНИЕ В начале данной темы я упомянул, что мне приходится использовать такой подход для реализации передачи данных между linked-серверами. Объясню суть возникшей передо мной в свое время проблемы и выбранное для нее решение. Проблема: есть множество процедур, производящих определенные предварительные расчеты статистики в нашей системе. Есть необходимость пересылать полученные данные на другой сервер, который, собственно, не отвечает за предварительные расчеты, но отвечает за отображение данной статистики, т.е. на указанном сервере находятся таблицы-приемники конечных результатов. Объем полученных данных, как правило, большой. Существует два варианта передачи – условно их можно назвать pull и push. При первом варианте (pull) данные затягиваются с сервера-источника на сервер-приемник данных скриптом, который выполняется на самом сервере-приемнике данных. То есть если бы у нас сервером-источником данных являлся linked_server_01, а сервером-приемником данных - linked_server_02, то данный вариант выглядел бы на t-sql так: -- скрипт выполняется на Linked_server_02: Insert into destination_table select * from linked_server_01.catalog.owner.source_table В случае push – данные не забираются с сервера-источника данных, а заталкиваются на сервер-приемник данных, то есть: -- скрипт выполняется на Linked_server_01: Insert into linked_server_02.catalog.owner.destination_table select * from source_table На первый взгляд, оба варианта равноценны, но на самом деле разница очень и очень существенная. Можно сразу сказать, что первый вариант на порядки производительнее второго, так как процесс переноса данных во втором случае происходит по сути своей построково, в чем легко убедиться путем профилирования запросов описанных типов. О разнице в использовании обоих подходов можно подробнее узнать из статьи Михаила Смирнова по следующей ссылке: http://www.sql.ru/subscribe/2007/354.shtml#20 Полученные в результате выполнения хранимых процедур (по расчету статистики на сервере-источнике) данные сохранялись в постоянных таблицах. Затем эти данные забирались посредством хранимой процедуры, запускаемой на сервере-приемнике, которая собственно и выполняла сохранение данных методом pull, т.е. данные именно забирались с сервера-источника. Соответственно, для каждого вида статистики имелась отдельная постоянная таблица на локальном сервере (источнике) и отдельная хранимая процедура на удаленном (приемнике). Лично я противник дублирования кода и создания всякого рода дополнительных объектов в БД, так как это приводит к дополнительной нагрузке на сопровождение таких объектов – а именно, в моем случае, поддержка соответствия структуры таких таблиц как процедурам передачи данных, так и процедурам расчета. Решение: Именно поэтому я решил отказаться от использования постоянных таблиц и перешел на использование единой процедуры передачи данных, которой передаю object_id() временной таблицы с результатами, полученными с сервера-источника данных. Процедура передачи данных описанным выше методом обновляет имя временной таблицы в tempdb сервера-источника данных и осуществляет собственно выборку данных. Не скажу, что на все 100% уверен в правильности выбора такого решения, но за полгода использования проблем не возникало. Однако при переходе на MS SQL Server 2005 возникнет проблема, так как описанное выше решение работать уже не будет. – прим. Андрея Смирнова

Возврат курсора из динамического скрипта

Следует иметь в виду, что для корректной работы из динамического скрипта можно вернуть только открытый курсор, что и демонстрируется на примере:

declare @sql_script nvarchar(4000), @outer_cursor cursor set @sql_script = ' set @return_cursor = cursor local forward_only for select ''string_01'' union all select ''string_02'' union all select ''string_03'' open @return_cursor ' execute sp_executesql @sql_script, N'@return_cursor cursor output', @return_cursor = @outer_cursor output fetch next from @outer_cursor while (@@fetch_status = 0) fetch next from @outer_cursor close @outer_cursor deallocate @outer_cursor

Заключение.

Все приведенные в этой статье техники, механизмы и запросы были использованы в реальных проектах и зарекомендовали себя. Авторы с радостью рассмотрят любые комментарии и вопросы по тематике статьи.

---