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

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

Кроссплатформенные запросы, которые выполняются на уровне объектов класса.

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

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

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 минут(зависит от частоты транзакций и нагрузки на базу) и не вызывали блокировок.

Курс разработки по GlobalFramework

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

Contents

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

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

Методы#

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

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

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

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

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

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

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

ASQL#

ATSQL#

ASelect#

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

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

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

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

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

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

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

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