Взаимодействие с базой данных#

Объектные запросы#

Кроссплатформенные запросы, которые выполняются на уровне объектов класса. При выполнении запроса идет обращение к базе данных, если по классу не настроено кэширование запросов.

Пример запроса

new OQuery(Bs_GoodsAta.Type) {  
  where(t.sSystemName === spMnemoCode)  
}

OQuery предоставляет подмножество JPQL и используется для выбора данных по классу объектный кэш.

Методы#

where
Условие запроса.
orderBy
Выражение для сортировки результата
batchAll
Массовая загрузка объектов. Возвращает строки с прогруженными записями всех коллекций этого класса.
batchIn
Выполнение запроса с указанным перечнем коллекций, в которых требуется прогрузить данные.
forUpdate
Выполнение запроса с блокированием вернувшихся записей
forUpdateNoWait
Выполнение запроса с блокирование вернувшихся записей, без ожидания разблокирования, если записи уже заблокированы другой сессией.
tryCacheQueryResults
Попытаться закешировать результат запроса. Смотри пункт «Кеширование»
unique
Говорит, что запрос возвращает одну уникальную запись. Позволяет использовать cache-index’ы, указанные в orm класса

Объектный запрос c помощью выражений EclipseLink#

Функционал, расширяющий использование объектных запросов. который позволяет запрашивать данные из неизвестных классов (когда заранее неизвестен тип Ata). Класс:ru.bitec.app.gtk.eclipse.query.ElExpOQuery

Пример:

new ElExpOQuery(api).forWhere(  
  _.get("sSystemName").equal("test".ns.get)  
).addOrdering(  
  _.get("sCaption")  
)

смотри примеры использования: ru.bitec.app.btk.ElExpOQueryTest

Объектный запрос большого списка#

in.ru.bitec.app.btk.Btk_QueryPkg#largeInQuery

смотри примеры использования: ru.bitec.app.btk.Btk_QueryPkgTest

Кеширование объектных запросов#

Кеширование запросов работает только для классов с разделяемым режимом кеширования(Shared).

Кэширование по полю#

Кеширование через cache-index’ы указанные в orm классе. Такой запрос возвращает одну строку и дополняется командой unique(). Например для атрибутов мнемокода класса в orm формируется запись:

<cache-index>  
  <column-name>SSYSTEMNAME</column-name>  
</cache-index>

Запрос выглядит следующим образом:

new OQuery(entityAta.Type) {  
  unique()  
  where(t.sSystemName*=== spMnemoCode)  
}

Кэширование объектных запросов#

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

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

Пример запроса:

new OQuery(entityAta.Type) {  
  tryCacheQueryResults ()  
  where(t.sSystemName === spMnemoCode)  
}

Внимание

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

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

Транзакционный индекс#

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

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

Примечание

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

Пример объявления:

lazy val idxidParent = TxIndex(Btk_GroupAta.Type)(_.idParentGroup)

Методы

byKey
Возвращает итератор по ключу индекса.
refreshByKey
Возвращает итератор по ключу индекса c обновлением из базы данных.
queryKeys
Кеширование ключей индекса.
forPartition
Открывает секцию для массового обновления индекса. Используется для прозрачного массового обновления после очистки транзакционного кэша. Секции могут быть вложенными друг в друга, в таком случае ключи суммируются.

Реляционные запросы#

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

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

Внимание

Реляционные запросы полностью игнорируют изменения в рабочем пространстве. Если требуется выполнить согласованный запрос, используйте метод session.flush()

ASQL#

Выполнение запроса на чтение

ATSQL#

Выполнение запроса с изменением данных или блокировками

ASelect#

Выполнение запросов на чтение\запись с большим кол-вом колонок.

Пример:

for (rv <- new ASelect {
  val nParentLevel = asInt("nParentLevel")
  val gidParent = asString("gidParent")
  val gidChild = asString("gidChild")
  val idParent = asLong("idParent")
  SQL"""
  select nParentLevel,gidParent,gidChild,idParent,idChild from table
  """
}) {
  println(rv.nParentLevel())
  //запрос поля без его предварительного объявления
  println(rv.get("idChild").asNLong())
}

Типичные ошибки#

Некорректное использование запросов на чтение#

Внимание

Даже если такой код работает в тестовом формате, это будет приводить к ошибкам в продакшене.

Пример неверного запроса:

ASQL"select * from table where id=$id for no key update".execute()

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

ASQL"update table set a=$v where id=$c".execute()

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

Внимание

Внимание, все запросы на изменения данных должны быть транзакционными:

ATSQL"select * from table where id=$id for no key update".execute()

ATSQL"update table set a=$v where id=$c".execute()

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

Некорректное использование блокировок#

ATSQL"select * from reference where id=$id for update".execute()

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

Всегда пишите for no key update, если у вас нет четкого обоснования необходимости иной блокировки.

Некорректное формирование запроса#

Будьте осторожнее с подставлением значений в строку (интерполяции) при формировании sql запроса.

ATSQL(s"select * from reference where gid=$gid for update")
ASQL(s"select * from reference where gid=$gid for update")
new ASelect {
  //...
  SQL(s"select * from reference where gid=$gid for update")
}

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

ATSQL(s"select * from reference where gid={gid} for update").on(Symbol("gid") -> gid)
ASQL(s"select * from reference where gid={gid} for update").on(Symbol("gid") -> gid)
new ASelect {
  //...
  SQL(s"select * from reference where gid={gid} for update")
    on(Symbol("gid") -> gid/*, другие параметры*/)
}

Для случаев, когда вам нужно собрать запрос из обычной строки, используйте замену символов on(Symbol("символ для замены") -> ваша переменная, ...)

ATSQL"select * from reference where gid=$gid for update"
ASQL"select * from reference where gid=$gid for update"
new ASelect {
  //...
  SQL"select * from reference where gid=$gid for update"
}

Тут будет подставлено все правильно

Работа с большими данными#

Postgresql не поддерживает bulk insert\select\update, более того, коммит закроет любой открытый курсор. Это делает невозможной обработку больших объёмов данных частями на сервере, так как очень длинные транзакции перегружают wal и в конечном счете сильно снижают общую производительность сервера. Поэтому необходимо выполнять партиционирование на сервере приложения.

Замеры производительности показывают, что наиболее производительным средством перегонки данных является sql комманда postgresql «copy».

Сравнительная таблица производительности:

Операция 100000 записей	Приблизительное время(ms) на сервере
Вставка записей одним sql в базе	214
Общее время запроса\вставки через copy	728
Вставка записей одним sql в базе при наличии индексов	2785
Общее время вставки через массовый flush при наличии индексов из сервера в базу	19562
Обще время вставки через flush по одной записи при наличии индексов из сервера в базу	156710
Запрос данных через ResultSet	396
Запрос данных через команду copy	330

Примечание

На десктопе разница между вставкой через copy и вставкой прямым sql может достигать различий в 10 раз. Финальные тесты производительности нужно замерять исключительно на серверном оборудовании.

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

Вставка через batch на чистом sql сравнима по порядку с copy, хоть и медленнее.

Массовая вставка на чистом sql должна быть раза в 4 быстрее за счет отсутствия затрат на кэш.

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

Массовая вставка#

Через команду copy

Массовый update\delete#

Сохранение данных в файл через команду copy
Для каждой пачки
- Начало транзакции
- Копирование данных в gtt
- Выполнение dml операции
- Завершение транзакции

Внимание

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

Размер транзакции#

Постгресс по своей архитектуре менее зависим от длинны транзакции (смотри наполнение базы данных). Таким образом нет потребности в дроблении на мелкие транзакции.

Слишком длинные транзакции негативно влияют на базу, происходят следующие негативные эффекты:

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

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

Желательно, чтобы оперативные транзакции не занимали больше 10 минут (зависит от частоты транзакций и нагрузки на базу) и не вызывали блокировок.

Взаимодействие с базой данных

Содержание

Взаимодействие с базой данных#

Объектные запросы#

Методы#

Объектный запрос c помощью выражений EclipseLink#

Объектный запрос большого списка#

Кеширование объектных запросов#

Кэширование по полю#

Кэширование объектных запросов#

Транзакционный индекс#

Реляционные запросы#

ASQL#

ATSQL#

ASelect#

Типичные ошибки#

Некорректное использование запросов на чтение#

Некорректное использование блокировок#

Некорректное формирование запроса#

Работа с большими данными#

Массовая вставка#

Массовый update\delete#

Размер транзакции#