В системе DIRECTUM 5.0 реализована долгожданная поддержка Microsoft SQL Server 2012, а также возможность работы в режиме AlwaysOn! C помощью новых возможностей можно распределять запросы между несколькими серверами, таким образом снижая нагрузку на главный сервер.

SQL 2012

Итак, во время разработки версии DIRECTUM 5.0 перед нами стояла задача реализовать поддержку SQL 2012 в IS-Builder 7.

Для этого, сперва было проведено тестирование работы на SQL 2012, которое показало, что необходим отказ от старого стандарта  ANSI SQL-89, в первую очередь от условий «*=» и «=*». В связи с этим, внешние соединения таблиц в SQL-запросах были переведены с использования условий «*=» на синтаксис JOIN, что позволило перейти к поддержке новых уровней совместимости базы данных: 90 (SQL 2005), 100 (SQL2008), 110 (SQL2012).

Список несовместимостей SQL Server 2012 по отношению к другим версиям SQL Server можно посмотреть здесь Discontinued Database Engine Functionality in SQL Server 2012.

Теперь при конвертации ISBL-текстов проверяется наличие условий типа «*=» и некоторые другие несовместимости, которые необходимо устранить для работы на SQL Server 2012. К примеру:

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

Объектная модель IS-Builder

В связи с поддержкой SQL 2012, в объектную модель к уже существующим методам AddSelect, AddWhere, AddFrom, AddOrderBy и Del* добавлены новые: AddJoin и DelJoin.

Метод AddJoin добавляет к итоговому запросу условие. При вызове двух и более AddJoin с одинаковыми параметрами TableName и JointType, они сливаются в один JOIN с двумя и более условиями при помощи операции and. Если параметры TableName одинаковые, а JointType разные, то генерируется исключение.

Если необходимо несколько раз сделать соединение с одной и той же таблицей, например, MBAnalit с фильтром по разным типам справочника, то в качестве параметра TableName нужно указывать имя таблицы и алиас, например, dbo.MBAnalit Analit.

Метод DellJoin удаляет условие из запроса. DelJoin работает аналогично другим Del* методам: из списка условий удаляется условие с указанным ИД. Если в списке было несколько JOIN к одной и той же таблице, то из результирующего запроса удаляется только условие, относящееся к переданному ИД.

Примеры использования методов можно посмотреть в справке здесь и здесь.

В объектную модель были также добавлены перечислимые типы: TJoinType – тип соединения и TAddPosition – позиция соединения.

Пример изменения текста SQL-запроса при добавлении соединений можно посмотреть в TAddPosition.

Рекомендации по доработке SQL-запросов

Существенным отличием операторов «*=» и «=*» от Left Join и Right Join является задание ограничений на связываемые таблицы. Важно понимать, что при использовании оператора Left Join, условия, записанные в секции on, проверяются только для правой таблицы и не влияют на включение строк левой таблицы в итоговую выборку. А вот условия в секции where применяются ко всей итоговой выборке. Поэтому, если условия для правой таблицы наложены в секции where и не выполняются, то они исключат и строки левой таблицы из итоговой выборки. При использовании Left Join все условия на правую таблицу должны быть записаны в секции on. Аналогично будет и для Right Join.

Рассмотрим это правило на примере. Допустим, нужно получить все действующие записи справочника с Vid = 3118, и утвержденные записи табличной части, относящиеся к записям справочника, если они есть.

Вариант 1. Условие находится в правой таблице секции where всего запроса:

select
  *
from
  MBAnalit mbanalit
  left join MBAnValR mbanvalr on
    mbanalit.Analit = mbanvalr.Analit    
where
  mbanalit.Vid = 3118
  and mbanalit.Sost = 'Д'
  and mbanvalr.XRecStat = '+' 

Результат: если выполнить этот запрос, то он вернет 14 записей. При этом в итоговой выборке будут только записи справочника, у которых есть записи в табличной части. Запрос возвращает не все необходимые данные.

Вариант 2. Условие находится в секции on:

select
  *
from
  MBAnalit mbanalit
  left join MBAnValR mbanvalr on
    mbanalit.Analit = mbanvalr.Analit    
    and mbanvalr.XRecStat = '+' 
where
  mbanalit.Vid = 3118
  and mbanalit.Sost = 'Д'

Результат: если выполнить этот запрос, то он вернет 22 записи, в том числе записи справочника, у которых нет записей в табличной части. Запрос возвращает все данные.

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

select
  *
from
  MBAnalit mbanalit
  left join MBAnValR mbanvalr
    on mbanalit.Analit = mbanvalr.Analit
where
  mbanvalr.Analit is null

В этом случае условие на правую таблицу должно быть в секции where.

Эти правила действуют при отборе данных для всех типов запросов: Select, Update, Insert, Select, Delete.

Статические SQL-запросы

Большую часть запросов можно изменить простым переносом условия связывания из секции where в секцию on.

select 
  Groups.NameAn as GroupName,
  RefUsers.NameAn as UserName, 
  Users.UserLogin
from
  MBAnalit Groups,
  MBUser Users,
  MBAnValR ValR,
  MBVidAn VidAn,
  MBAnalit RefUsers
where 
  Groups.Vid = VidAn.Vid
  and VidAn.Kod = 'ГПЛ'
  and Groups.Analit = ValR.Analit
  and Groups.NameAn like 'М%'
  and ValR.PolzovatelT = RefUsers.Analit
  and RefUsers.IDSpr *= Users.UserID
  order by  GroupName, UserName

select 
  Groups.NameAn as GroupName,
  RefUsers.NameAn as UserName, 
  Users.UserLogin
from
  MBAnalit Groups,
  MBAnValR ValR,
  MBVidAn VidAn,
  MBAnalit RefUsers
  left join MBUser Users on
    RefUsers.IDSpr = Users.UserID
where 
  Groups.Vid = VidAn.Vid
  and VidAn.Kod = 'ГПЛ'
  and Groups.Analit = ValR.Analit
  and Groups.NameAn like 'М%'
  and ValR.PolzovatelT = RefUsers.Analit
 order by GroupName, UserName

При этом сложность исправления запроса не всегда зависит от его размера.

Параметры типа AddWhere

Если значение, передаваемое в качестве параметра AddWhere какой-либо функции (например, ChangeReferenceDataset, СпрИзмНабДан), содержит «*=», то нужно:

1. Найти правую таблицу в параметре AddFrom и перенести её в параметр AddJoin с типом соединения jtLeft.
2. У параметра AddJoin в секции on написать условие из параметра AddWhere.
3. Удалить из параметра AddWhere условие с «*=».

Для «=*» будут аналогичные действия, только замена выполняется на параметр AddJoin с типом соединения jtRight.

!AddFrom = "tmpTable t"
!AddWhere = "MBAnalit.Analit *= t.Analit and t.YesNoT = 'Д'" 
Справочник(!Код;;"t.AccT as Счет";!AddFrom;!AddWhere)

Справочник(!Код;;"t.AccT as Счет";;;;;;;;;;; ;;True; "tmpTable t|jtLeft|MBAnalit.Analit = t.Analit and t.YesNoT = 'Д'))

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

При присоединении таблицы через параметр AddJoin нужно обращать внимание на последовательность добавления, так как при добавлении из условия выше нельзя обратиться к таблицам объявленным ниже.

Параметр AddJoin всегда присоединяет таблицу к первой таблице в блоке From.

К примеру, исходный запрос выглядит так:

select 
  Analit.Kod, 
  Analit.NameAn,
  ValR.ISBRole
from
  MBAnalit Analit,
  MBAnValR ValR
where
  Analit.Analit = ValR.Analit
  and Analit.Vid = 3140
  and ValR.ISBRole is not null

Если нам нужно добавить новую присоединяемую таблицу, не к первой таблице, а ко второй, то нужно объявление MBAnValR переделать на выражение с JOIN и только потом вносить наши изменения.

AlwaysOn

Со временем нагрузка на SQL-сервер возрастает. Это может быть связано с увеличением объема данных, либо с увеличением числа пользователей, которые работают с сервером. Инвестиции в новый сервер с более высокими аппаратными характеристиками не всегда оправданы. AlwaysOn позволяет задействовать в работе «спящие» ресурсы (старые серверы) и оптимально распределить нагрузку между этими серверами.

Режим Microsoft SQL Server 2012 AlwaysOn представляет собой объединение нескольких серверов для работы одной базы данных. На одном из серверов база находится в режиме для чтения и записи, на других серверах находятся ее копии в режиме только для чтения.

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

Все серверы должны входить в отказоустойчивый кластер WSFC, т.к. AlwaysOn использует для работы механизмы кластера (своих механизмов у AlwaysOn нет). В свою очередь, на всех серверах должны быть расположены экземпляры SQL Server Server 2012 Enterprise Edition. Подробнее системные требования можно посмотреть в статье «Предварительные требования, ограничения и рекомендации».

Для распределения нагрузки в IS-Builder 7 между «основным» сервером и серверами «только для чтения» в системе DIRECTUM появились новые установки:

• PercentageOfPossibleConnectionsToReadOnlyServers – процент возможных подключений к серверам для чтения с поддержкой AlwaysOn. Предназначена для распределения нагрузки между основным сервером и серверами для чтения. После конвертации системы автоматически устанавливается значение 0 – все запросы на чтение отправляются только на основной сервер. Рекомендуется установить значение 100.

• GetDataFromReadOnlyServerTimeLag – интервал задержки в получении данных с сервера для чтения. Указывается в миллисекундах. После конвертации системы автоматически устанавливается значение 2000 – после обновления объекта системы данные будут считываться с сервера для чтения спустя две секунды. До указанного значения данные будут считываться с главного сервера.

Порядок настройки для работы в режиме AlwaysOn подробно описан в соответствующей инструкции. Посмотреть ее можно на сайте поддержки support.directum.ru.