Всем привет, меня зовут Кирилл и я Android-разработчик в Scanny. Мы занимаемся тем, что делаем мерчандайзинг удобным, быстрым и эффективным. С помощью нейронных сетей мы стремимся создать карманного помощника, который поможет мерчендайзерам, следуя инструкциям, выполнять свою работу более эффективно. Производители в свою очередь получают полную информацию о своих товарах в торговых точках.
О чем эта статья?
В процессе своей работы я, как и многие, набивал и продолжаю набивать шишки, извлекая из этого ценные уроки. В этом мне помогает моя команда и отдельное спасибо хочется сказать Евгению Мацюку.
Эта небольшая статья про то, как мы ускорили работу с БД в Android-приложении, исправив очевидные и не слишком ошибки.
Это моя первая статья написанная в свободной форме, и хотелось бы сказать: "Не судите строго". Но я открыт к конструктивной критике, и если вам будет что добавить/исправить, буду рад видеть в комментариях.
Для начала небольшой дисклеймер: Вся работа происходит с СУБД SQLite + Room, а замеры времени производились на Samsung S21 FE.
Теперь к коду
1) Обращение к БД в циклах
Предыстория
История начинается с того, что в какой-то промежуток времени была добавлена логика, в которой производились операции над товарами. Представим себе ситуацию, мы обрабатываем товары, что-то делаем с сущностями и затем возвращаем результат. Перед итерацией по списку товаров, могут происходить обращения к репозиторию, в целом ничего существенного. Метод проблем не вызывал. Ниже представлен примерный код.
internal suspend fun doSomething(products: List<Product>): List<Product> {
val someValue = skuRepository.getSomething()
val resultList = mutableListOf<Product>()
products.forEach { product ->
/** Some code */
val modifiedProduct = product.copy(/** Some code */)
resultList.add(modifiedProduct)
}
return resultList
}Спустя какое-то время требования бизнеса поменялись и в данную логику были внесены изменения. И тут случилось кое что интересное, не обратив особого внимания, в цикле было добавлено новое обращение к репозиторию. Казалось бы, что может пойти не так?
internal suspend fun doSomething(products: List<Product>): List<Product> {
val someValue = skuRepository.getSomething()
val resultList = mutableListOf<Product>()
products.forEach { product ->
val sku = skuRepository.getSkuById(product.skuId) // Новое обращение к репозиторию
/** Some code */
val modifiedProduct = product.copy(/** Some code */)
resultList.add(modifiedProduct)
}
return resultList
}Может, еще как может. Тесты отрабатывали, логика работала, но через небольшое время мы обратили внимание на просевшую производительность при работе с товарами. Начали разбираться что к чему и нашли виновника, коим было наше обращение к репозиторию в цикле.
Дело в том, что репозиторий реализовывал работу с БД. Проблема не сильно заметна, когда список товаров состоит из 0 - 300 элементов, но что происходит, когда список содержит 1000 элементов и соответственно производит столько же обращений к БД?
Если отбросить всё, оставив только обращение к репозиторию в цикле, то среднее время выполнения кода ниже для 1000 элементов будет составлять 599мс, 90-й перцентиль 688мс (Samsung S21 FE).
internal suspend fun doSomething(products: List<Product>): List<Product> {
products.forEach { product ->
skuRepository.getSkuById(product.skuId)
}
return listOf()
}Почему так происходит?
Дело в том, чтобы выполнить наш SQL-запрос, СУБД необходимо сначала выполнить подготовительные операции и только после этого, происходит исполнение SQL-запроса. Что это за подготовительные операции? Давайте посмотри на схему, которая приблизительно отображает путь, который проходят наши SQL-запросы.
Штука сложная, но давайте немного разберем. Самая главная особенность SQL, это то, что это декларативный, а не процедурный язык. Программируя на SQL, мы говорим системе, что мы хотим вычислить, а не то, как вычислить.... Отсюда следует, что системе сначала надо понять, что требуется сделать и именно с этого начинается путешествие нашего SQL-запроса.
1. Parsing and Compilation
Все начинается с того, что наш запрос разбирается на токены (Tokenization), по другому это еще называют лексическим анализом. Токены - это ключевые слова, операторы, идентификаторы таблиц, колонок и т.д, например, таблица users, поле age, оператор OR .
Далее, Parser анализирует токены (Parsing) SQL-запроса и формирует структуру данных, которая описывает, какие действия необходимо выполнить. Если система видит, например, "FROM users", она понимает, что необходимо работать с таблицей users.
Результатом данного этапа будет синтаксическое дерево, описывающее структуру запроса.
2. Planning
Хорошо, теперь у БД появилось понимание что требуется делать, дальше наступает этап планирования, а начинается он с оптимизации (Optimization). Иногда еще оптимизатор, называют Query planner. На данном этапе продумывается лучший способ выполнения запроса.
Если упростить, все сводится к 2-м задачам:
Выбор порядка вложенности для различных циклов. Т.е. в каком порядке будут выполняться операции;
Выбор подходящих индексов для каждого цикла (при наличии).
Все это зависит от самого SQL-запроса и схемы данных. Для примера, давайте представим себе следующий SQL-запрос:
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.amount > 100Допустим, оптимизатор решит сначала пройтись по orders, а затем перейти к customers, то вложенность будет иметь следующий вид (для наглядности):
result = []
for order in orders:
if order.amount > 100:
for customer in customers:
if order.customer_id == customer.id:
result.append((order, customer))После оптимизации создается план выполнения запроса (Execution Plan Generation), который представляет из себя набор инструкций в виде байт-кода.
3. Execution
Когда план работы составлен, можно переходить к его выполнению. Этим занимается Virtual machine и в целом тут уже все прозрачно, виртуальная машина выполняет работу по заготовленному плану (Plan execution).
Когда запрос выполнен, результат необходимо подготовить уже к удобному "высокоуровневому" виду (Response preparation). Когда все этапы пройдены, работа сделана, данные подготовлены, мы получаем наш результат через высокоуровневый API.
Ну и в целом, если коротко, то про работу БД все, кто хочет узнать больше, вся информация есть на сайте SQLite. А начать уже свое путешествие можно с замечательной документации про архитектуру SQLite, обзора на оптимизатор запросов, планировщика запросов нового поколения, а дальше, я думаю, вы и сами поймете, что читать далее.
Обратно к проблеме
Исходя из того, как работает БД, можно сделать вывод, что какую-то часть времени выполнения нашего SQL-запроса занимает работа внутренних алгоритмов по подготовке данных, оптимизации, составлению плана работ и т.д. Логично, что это соотношение каждый раз будет разным в силу огромного множества причин, это может быть наличие уже готового набора инструкций для выполнения запроса, наличие результата запроса в кэше, непосредственно сам запрос, он может требовать получения как 1 элемента, так и миллиона.
Но также, можно сделать вывод, что чем меньше наш запрос, тем больший процент занимают подготовительные процессы, и наоборот, чем больше запрос, тем меньший процент всего времени занимает подготовка.
Я решил провести небольшой эксперимент, где собрал статистику по времени выполнения. В тестах из БД будет получено 100 / 1000 элементов тремя разными способами: по 1 элементу за обращение, по 1 элементу за обращение в рамках одной транзакции, разом все элементы за обращение. Таким образом у нас получается 6 тестов.
Результаты тестов указаны в таблице ниже. Каждый тест прогонялся по 50 раз.
Тип теста | Общее кол-во элементов | Время получения всех элементов в мс, 90-й перцентиль | Стоимость получения 1 элемента в мс, 90-й перцентиль |
|---|---|---|---|
По 1 элементу за обращение | 100 | 98.65 | 0.99 |
По 1 элементу за обращение | 1000 | 688.42 | 0.69 |
По 1 элементу за обращение в рамках одной транзакции | 100 | 21.92 | 0.22 |
По 1 элементу за обращение в рамках одной транзакции | 1000 | 106.95 | 0.11 |
Разом все элементы за обращение | 100 | 11.96 | 0.12 |
Разом все элементы за обращение | 1000 | 24.67 | 0.02 |
Кому удобно воспринимать на глаз, графики ниже.
Напоминаю, что конкретные результаты будут отличаться от устройства к устройству, все очень индивидуально.
Примеры SQL-запросов
SQL-запрос для получения 1 элемента:
SELECT * FROM Sku WHERE id = :idSQL-запрос для получения всех элементов разом:
SELECT * FROM Sku WHERE id IN (:ids)Примеры самих тестов
По 1 элементу за обращение:
repeat(50) {
repeat(100) {
val time = measureNanoTime {
skuRepository.getSkuById(id)
}
}
}По 1 элементу за обращение в рамках одной транзакции:
repeat(50) {
skuRepository.withTransaction {
repeat(100) {
val time = measureNanoTime {
skuRepository.getSkuById(id)
}
}
}
}Разом все элементы за обращение:
repeat(50) {
val time = measureNanoTime {
skuRepository.getSkusByIds(ids)
}
}Как видно из результатов, объединение запросов в транзакцию очень хорошо помогает улучшить производительность, но если в этом нет необходимости, такие запросы лучше объединять в один и разом получать всю информацию.
Что делать?
Тут все просто, не обращаемся к БД в циклах :). Т.е. если нам необходимы какие-то данные, то просто заранее подготавливаем все одним запросом, а потом уже используем их как нам угодно. То же самое рекомендует и Google.
Да, я уверен, что все прекрасно это знают, но на мой взгляд надо помнить, что человеческий фактор никто не отменял и каждый может столкнуться с этим.
И чтобы закрепить результат, посмотрим на код ниже. Тут мы уже заранее получаем всю информацию одним запросом, а дальше используем ее как нам угодно.
internal suspend fun doSomething(products: List<Product>): List<Product> {
val someValue = skuRepository.getSomething()
val skuIds = products.map { it.skuId }
val skus = skuRepository.getSkusByIds(skuIds) // Получаем всю необходимую нам информацию одним запросом
val resultList = mutableListOf<Product>()
products.forEach { product ->
/** Some code */
val modifiedProduct = product.copy(/** Some code */)
resultList.add(modifiedProduct)
}
return resultList
}Время выполнения нашего запроса для 90-го перцентиля теперь в составляет 25.67мс.
val skus = skuRepository.getSkusByIds(skuIds)2) Хранение JSON-объектов в БД
Предыстория
История связана с тем, что мы экспериментировали с результатами работы моделей нейронных сетей. Допустим, некоторые из моделей используются при работе с товарами. Это могут быть разные модели для различных задач, и, соответственно, они выдают разные результаты. На первых порах может быть сложно понять, какая информация, предоставляемая моделью, может быть полезной, а какая точно не пригодится.
С этой целью мы решили временно разделять все данные на соответствующие сущности и хранить их в виде JSON-объектов в таблице товаров. Такой подход позволил нам быстро и гибко изменять структуру данных. На изображении ниже представлен пример того, как выглядела таблица с товарами. Здесь мы храним информацию об ограничивающей рамке (Bbox) товара, а метаданные работы модели и прочая информация, не представляющая интереса, опускаются.
Объяснение понятия BBox'а
Для того чтобы определить, где расположен товар, необходимо описать его местонахождение на фотографии. Для этого используется понятие Bounding Box (Ограничивающая рамка), где left, right, top и bottom - это координаты каждой из сторон рамки относительно размеров фотографии. На изображении ниже схематичное описание Bounding Box'а.
Но у данного подхода есть и проблемы. Первая из них — невозможность работать с данными в SQL-запросах по отдельности. Это означает, что в нашем запросе мы не можем работать с товарами по параметрам left, right, top и bottom. Чтобы выполнить, например, фильтрацию, сначала необходимо получить все товары из БД, а затем уже обработать их в коде.
Вторая проблема связана с производительностью. Простой SELECT получения 1000 объектов Bbox с библиотекой Gson выполняется в среднем за 20.28мс (90-й перцентиль 26.55мс). Кажется не так много, но важно помнить, что при работе с товарами объем JSON-объектов и их количество может быть больше, а запросы сложнее. В результате выполнение SQL-запроса может занять не 20 мс, а 200, 400 и более.
Почему так происходит?
Для начала стоит определиться, зачем вообще может понадобиться хранить информацию в виде JSON'а в БД, подробнее в таблице ниже.
Когда имеет смысл | Когда смысла нет |
|---|---|
JSON в таблице уместно использовать в случае, когда формат данных регулярно меняется, либо информация необходима лишь на определенный промежуток времени. Примером могут быть метаданные. | Не следует применять JSON для хранения данных, которые остаются неизменными с течением времени, особенно если к ним требуется постоянный доступ. |
Какие могут быть последствия использования? Процесс сериализации и десериализации JSON занимает определенное время, что, в свою очередь, ведет к замедлению скорости выполнения алгоритмов и SQL-запросов.
Первое что хочется сделать, это протестировать разные парсеры JSON'ов. Возьмем 3 популярные библиотеки: Gson, Moshi и Kotlinx serialization. Тесты сделаем по аналогии как и в предыдущем разделе.
В тестах из БД будет получено 100 / 1000 элементов тремя разными библиотекам, напоминаю у нас это будут: Gson, Moshi и Kotlinx serialization. Таким образом получается 6 тестов.
Результаты тестов, как обычно, указаны в таблице ниже. Каждый тест прогонялся по 50 раз.
Библиотека | Общее кол-во элементов | Время получения всех элементов в мс, 90-й перцентиль | Стоимость получения 1 элемента в мс, 90-й перцентиль |
|---|---|---|---|
Gson | 100 | 14.55 | 0.15 |
Gson | 1000 | 26.55 | 0.03 |
Moshi | 100 | 18.68 | 0.19 |
Moshi | 1000 | 31.94 | 0.03 |
Kotlinx serialization | 100 | 15.71 | 0.16 |
Kotlinx serialization | 1000 | 33.11 | 0.03 |
Для удобства графики ниже.
Реализация наших парсеров
Gson:
private val gson = Gson()
@TypeConverter
internal fun bboxToJson(bbox: Bbox): String {
return gson.toJson(bbox)
}
@TypeConverter
internal fun jsonToBbox(json: String): Bbox {
return gson.fromJson(json, Bbox::class.java)
}Moshi:
private val moshi = Moshi.Builder().add(KotlinJsonAdapterFactory()).build()
@TypeConverter
internal fun bboxToJson(bbox: Bbox): String {
return moshi.adapter(Bbox::class.java).toJson(bbox)
}
@TypeConverter
internal fun jsonToBbox(json: String): Bbox {
return moshi.adapter(Bbox::class.java).fromJson(json)!!
}Kotlinx serialization:
@TypeConverter
internal fun bboxToJson(bbox: Bbox): String {
return Json.encodeToString(bbox)
}
@TypeConverter
internal fun jsonToBbox(json: String): Bbox {
return Json.decodeFromString(json)
}Как видно, в целом Gson довольно хорошо справляется, но я никаким образом не кастомизировал работу с библиотеками, и, возможно, есть варианты улучшить работу с ними. Дополнительно хочу обратить внимание, что в тестах выше для Gson и Moshi переиспользуется один и тот же Instance. Если мы будем создавать новый Instance каждый раз, когда конвертируем объект, результаты будут совсем другие.
Библиотека | Общее кол-во элементов | Время получения всех элементов в мс, 90-й перцентиль | Стоимость получения 1 элемента в мс, 90-й перцентиль |
|---|---|---|---|
Gson | 100 | 23.56 | 0.24 |
Gson | 1000 | 70.39 | 0.07 |
Moshi | 100 | 57.31 | 0.57 |
Moshi | 1000 | 170.74 | 0.17 |
Для удобства графики ниже.
Реализация парсеров, где мы каждый раз создаем новый Instance
Gson:
@TypeConverter
internal fun bboxToJson(bbox: Bbox): String {
val gson = Gson()
return gson.toJson(bbox)
}
@TypeConverter
internal fun jsonToBbox(json: String): Bbox {
val gson = Gson()
return gson.fromJson(json, Bbox::class.java)
}Moshi:
@TypeConverter
internal fun bboxToJson(bbox: Bbox): String {
val moshi = Moshi.Builder().add(KotlinJsonAdapterFactory()).build()
return moshi.adapter(Bbox::class.java).toJson(bbox)
}
@TypeConverter
internal fun jsonToBbox(json: String): Bbox {
val moshi = Moshi.Builder().add(KotlinJsonAdapterFactory()).build()
return moshi.adapter(Bbox::class.java).fromJson(json)!!
}Результаты получились интересные, т.к. видимо инициализация данных объектов довольно ресурсоемкая операция. Приблизительная разница в производительности составляет 3-5 раз.
Что делать?
Да в целом, если все устраивает, то мы можем ничего не трогать, но если нас такое положение дел не устраивает, то есть 2 выхода. Либо мы меняем библиотеку для работы с JSON'ами, либо делаем реструктуризацию, об этом будет написано ниже.
Поскольку вид и тип информации, которая содержится в сущности Bbox постоянна и используются часто, мы просто можем вынести все поля из нашей сущности, т.е. сделать реструктуризацию схемы данных. Подробнее на рисунке ниже.
В целом ничего сложного, хочется сразу перейти к замерам скорости. Тесты будут аналогичные предыдущим, 100 / 1000 элементов, каждый тест прогоняется 50 раз. Подробнее в таблице ниже.
Общее кол-во элементов | Время получения всех элементов в мс, 90-й перцентиль | Стоимость получения 1 элемента в мс, 90-й перцентиль |
|---|---|---|
100 | 9.47 | 0.09 |
1000 | 15.14 | 0.02 |
Для удобства графики ниже.
Результаты можно сказать хорошие, если сравнивать с Gson, то разница примерно в 1.5-1.75 раза.
Заключение
На этом все, получилось не много, но надеюсь кому-то эта статья будет полезна. Если вам есть что добавить или исправить, милости прошу в комментарии! С удовольствием буду читать.
Еще увидимся!
