Нестандартный подход к «повышению производительности» select-запросов в SQLite

  • Tutorial
Здравствуйте!

Прежде всего, хотелось бы отметить 2 момента:
— данная статья описывает алгоритм работы, а не как оптимизировать конкретные SQL-запросы;
— описываемый в статье подход не актуален для «производительного» железа, речь пойдет о том, как удалось повысить «отзывчивость» интерфейса приложения интенсивно работающего с БД на бюджетных смартфонах под управлением Android;

Кратко о приложении


Чтобы было понятно о чем идет речь, в 2-х словах скажу о самом приложении: это была система для организации мобильной торговли, большинство пользователей — торговые агенты (ТА)., Когда ТА агент приходит в торговую точку, ему нужно очень быстро находить необходимый товар, видеть его остатки на складах (на момент последней синхронизации) и отмечать необходимое клиенту количество. Т.е. алгоритм работы ТА выглядит примерно следующим образом:
— найти товар 1;
— ввести кол-во;
— найти товар 2;
— ввести кол-во;


Делают они это очень быстро :)

Требования к поиску


— Поиск по наименованию товара (а оно может быть как на русском, так и на английском языке);
— У каждого клиента может быть «свой» прайс-лист;
— Нужно иметь возможность видеть как все товары, так и только те, которые есть в остатках;
— Иметь возможность отфильтровать список товаров по выбранной категории со всеми вложенными в нее подкатегориями;

Исходные данные


Цифры, которые я привожу — реальные, иначе не пришлось бы все это изобретать.
Кол-во клиентов: 500
Кол-во товаров: 5000
При назначении каждому клиенту индивидуального прайса получаем: 5000 * 500 = 2 500 000 строк в таблице прайсов.

Несмотря на то, что SQLite весьма быстр, решить задачу «в лоб» не получилось. Или вернее будет сказать, что все работало, но недостаточно быстро.

Решение


Первая очевидная вещь, это то, что SQL-запрос формируется динамически (дабы исключить из него лишние join'ы и условия, если в них нет необходимости). Также в ходе экспериментов выяснилось, что объединение таблиц в виде:
select * from Table1, Table2 where Table1.Table2Id = Table2.Id

работает немного быстрее, чем:
select * from Table1 join Table2 on Table1.Table2Id = Table2.Id

Ну и еще один, вполне естественный момент (хотя сразу в голову не пришел), это то, что запрос вида:
select Table1.Id from Table1, Table2 ... TableN
where Table1.Table2Id = Table2.Id ...

выполнится гораздо быстрее, чем:
select Table1.Id, ... TableN.FieldN from Table1, Table2 ... TableN
where Table1.Table2Id = Table2.Id ...

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

А как же с остальными данными?


А остальные данные мы получаем серией простых запросов типа:
select * from Table1 where Id = ?

Этот запрос выполняется очень быстро т.к. Id это первичный ключ.

Итого


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

Как я уже говорил этот подход позволил существенно ускорить скорость работы приложения на бюджетных моделях смартфонов. При скроллинге по списку товаров (когда для каждого появляющегося элемента списка выполняется несколько «простых» запросов) никаких видимых глазу подтормаживаний не наблюдается.

Комментарии 39

    +2
    Как минимум mysql, postgresql и oracle создают одинаковый план в случае неявного объединения через запятую, и явного в виде INNER JOIN.

    Любопытно, что sqlite пошло каким-то другим путём и одну и ту же операцию, записанную разными способами, выполняет также по-разному.

    Вы уверены, что вы не ошиблись в своих выводах?
      0
      При выполнении запросов о которых идет речь разница во времени не очень большая, но в моем случае она все-же была (тестировалось на устройстве). Хотя полностью исключать вероятность ошибки не стоит, да и делалось все это более года назад, возможно уже новая версия SQLite вышла.
      +3
      О чем вообще эта статья? О том что неплохо бы смотреть план запроса прежде чем выпускать приложение в продуктив?
        0
        Статья о том, что хороший план запроса в данном случае не помогал. Да, он выполнялся не минуты, секунды, но этого было недостаточно. Описанное решение позволило ускорить работу, не SQL запросов (они остались такими же, за исключением кол-ва возвращаемых данных), а конкретного интерфейса приложения в целом.
          +1
          Вы сравнивали планы запроса select * и select Table1.Id?
          Я думаю если вы сравните планы запросов, то увидите разницу. Скорее всего в случае когда вы выбирает в запросе столбец с индексом, то читается только индекс, а когда выбираете * то происходит либо full table scan либо кроме индекса читается так же сама таблица. Соответственно логично что чтение индекса получается намного быстрее.

          В общем планы запросов в студию! Говорить о каком то ускорении без их анализа это все равно что ходить с бубном вокруг сервера.
            0
            Да я и не говорю, что планы запросов не меняются. Естественно все так как Вы написали. НО! Выполнение одного запроса, который возвращает все необходимые мне данные (для отображения в UI) происходит дольше, чем если я беру тот же запрос, но возвращающий одно поле, а остальные данные получаю потом. И хотя количество запросов к базе возрастает, общее время «отклика» интерфейса уменьшается.
            Хочу еще раз обратить внимание: статья НЕ про оптимизацию SQL запросов и я даже постарался отметить это до хабраката.
              +2
              Если вы не понимаете почему все происходит так как вы описали, тогда извините, мне ничего не остается как добавить в ваш топик эту картинку:


                –5
                похоже, что та простая мысль, которая изложена в моей статье оказалась для вас такой же «магией», увы но я не знаю как можно было написать еще проще
        +3
        эмммм, а где всетаки Нестандартный подход к «повышению производительности»?
          –3
          ну все-таки разбиение процесса получения данных из БД не всегда себя обравдывает, да и как-то не встречалось мне подобное решение раньше, может плохо искал
            0
            Кстати на счет разбиения процесса получения данных — погуглите MapReduce. Это не в коем случае не руководство в действию, просто иногда бывает полезно расширять кругозор :)
              0
              Это Вы мне на дешевом смартфоне для тривиальной задачи его рекомендуете применить?
              Может туда еще OLAP всунуть, полнотекстовый поиск и чтобы наверняка еще нейронную сеть? Да уж наверное люди работающие с этой программой даже не представляют какого счастья я их лишил.
                0
                Это Вы мне на дешевом смартфоне для тривиальной задачи его рекомендуете применить?

                Это не в коем случае не руководство в действию

                OMG, вы читаете по диагонали, или видите только то что хотите, и не видите всего остального?
                  –1
                  Ну подождите, в статье я привел описание где и для чего было применено это решение, ну при чем тут все то, что Вы начали мне предлагать? Ну Вы что-то знаете, где-то это применяли — отлично. Я не ставил своей целью поведать миру о «серебрянной пуле» я привел конкретное решение конкретной проблемы. Уверен, что есть еще много других способов решить эту проблему и кто-то сможет выбрать лучший для него вариант.
                    0
                    Я не ставил своей целью поведать миру о «серебрянной пуле» я привел конкретное решение конкретной проблемы


                    Если бы вы описали полностью суть проблемы и предложили какие то альтернативные решения с обоснованием, то это можно было бы считать полезной статьей. Пока же я вижу только грязный хак который был применен без изучения мат. части, и посему этот топик полезным считать нельзя.
          –7
          странно, но я сейчас вижу -1 у статьи, которая только что позволила ускорить работу моего приложения. хочется сказать спасибо автору, удивительно простое и эффективное решение
            –2
            пожалуйста, нужно было раньше написать, все руки не доходили
              0
              отпишите хоть, из-за чего минусуете?
                0
                Я думаю из за того что данное решение не более чем костыли к плохо продуманной структуре БД и не оптимизированным запросам. Я бы эту статью вообще отнес к вредным советам, потому что она на первый взгляд описывает полезное действие но описывает его как магию, совершенно не объясняю почему именно так а не иначе необходимо делать.
                  0
                  Какая структура? Для данного примера достаточно 3-х таблиц: товар, клиент, и прайс-листы.
                  Какая магия?
                  Запрос:
                  select p.Id, p.Name, p.Description, pp.PriceNal, pp.PriceBNal
                  from prod p
                  join prod_price pp on pp.prod_id = p.id
                  where pp.client_id = ?
                  

                  будет выполняться дольше чем:
                  select p.Id
                  from prod p
                  join prod_price pp on pp.prod_id = p.id
                  where pp.client_id = ?
                  

                  потому что, он возвращает меньше по объему данных (не строк). И на обработку такого запроса (при одиновых исходных условиях: наличие индексов и пр.) у SQLite уходит меньше времени чем на 1-й. Так при чем тут индексы? Вы хоть на все возвращаемые поля индексы навешайте. Повторяю еще раз: статья не про оптимизацию SQL-запросов, ну я уж не знаю как Вам это объяснить :)
                  И еще раз: речь идет не о десктопе, а о «маломощных» смартфонах, на десктопе Вы не заметите разницу в скорости (она будет очень несущественной).
                    +3
                    Ну а как получить-то название, описание и тд?))
                    Вы сравниваете запросы, которые выдают РАЗНЫЙ результат!

                    Или у Вас тут progressive enhancement?) Только id для дешевых смартфонов!
                      0
                      Ну а как получить-то название, описание и тд?))

                      Для этого нужно прочесть статью.

                      Вы сравниваете запросы, которые выдают РАЗНЫЙ результат!

                      Я привел пример показывающий что позволяет получить ускорение работы интерфейса.
                      0
                      Конечно объем данных может как то влиять на скорость работы. Я мало работал с MySQL и SQLite, работаю в основном с Oracle, но не думаю что в SQLite сильно отличается структура индексов.
                      Основная суть здесь заключается в том, что при выборке p.Id, p.Name, p.Description у вас происходит чтение блоков данных, а в случае одного p.Id у вас происходит только чтение индекса (все необходимые данные уже есть в индексе), а блоки данных не читаются. Вот при чем тут индексы.
                        0
                        Есть много вещей с которыми я тоже не работал или работал мало, но если Вы работаете с Oracle, то наверное знаете кто такой Том Кайт, так вот в своих книгах он пишет о том, что начиная работать с какой-то СУБД нужно изучить ее особенности, а не пытаться применить к ней знания полученные при работе с другими СУБД (я имею ввиду какие-то тонкости естественно, а не базовые принципы). В данном случае, описанный мной подход позволил получить заметный прирост в скорости работы UI, и для меня именно это имеет значение, а не то, что данный подход, возможно, не укладывается в какие-то принципы работы Oracle или другой СУБД, кроме того, если Вы внимательно почитаете статью и мои ответы на комментарии, то увидите, что я уже писал, что этот подход не стоит рассматривать для применения на десктопах/серверах и пр.
                          0
                          Ваш подход вообще не стоит рассматривать для применения в серьезных приложениях.
                            –1
                            Ну да, он же на на Oracle и без блэкджета.
                            Так хоть скажите Кайта то читали? А-то совсем уж печально становится.
                              0
                              Если бы не читал, то и не стал бы говорить о том что недостаточно глубоко знаю SQLite.
                              К тому же я не Oracle разработчик, так, могу несложные запросики на пару сотен строк написать, да пакеты для приложения создавать. А вот наши Oracle разработчики пишут такие вещи на PLSQL что они мне иногда кажутся магией, приходится читать Кайта что бы поддерживать уровень.
                                –1
                                если вы не разработчик, как вы можете утверждать, что «Ваш подход вообще не стоит рассматривать для применения в серьезных приложениях»?
                                  0
                                  ilichme, давайте уже завершим этот «пинг-понг» :) мы с Silver_Clash говорим о разных вещах я ему про ускорение работы интерфейса (объяснение которому он дал в своем же комментарии), а он мне про оптимизацию SQL-запросов — тему безусловно интересную, но выходящую за рамки этой статьи (о чем я написал, кстати, еще до ката)
                                    0
                                    А вы похоже тоже читаете по диагонали?
                                    0
                                    видимо, что за кучей технического описания вы пропустили одну из самых важных вещей о которой он (Кайт) говорит очень часто, а именно, что работая с к-л СУБД не нужно пытаться применить к ней знания полученные при работе с другими СУБД (речь сейчас не об sql-запросах). любая СУБД это прежде всего инструмент и чтобы им правильно воспользоваться нужно, как минимум, изучить его (типы индексов, блокировки и т.д. и т.п.). вы уцепились за планы приведенных мною запросов, но не хотите понять, что дело не в них, здесь вы совершенно верно объяснили почему один из запросов работает быстрее, но упорно не хотите понять, почему это ведет у скорению работы UI
                                      0
                                      Ваше замечание не принимается пока вы не покажете что в SQLite при чтении индексных данных оптимизатор читает и блоки данных (даже если требуются только данные индекса).
                          +1
                          скажите, а вы пробовали в ListVIew отобразить, скажем, 100к строк из базы данных? и как результаты?
                            0
                            возможно я не совсем правильно понял, но ведь ListView хранит в памяти, только строки видимые на экране? в нашем случае на экране помещалось примерно 7-10 строк
                              0
                              да, но при фестскроле оно заметно притормаживает (суть тормозов кроется в WindowedCursor), а тот подход, который вы описали в статье, позволяет тормозов избежать. собственно, мой вопрос был адресован Silver_Clash
                      +2
                      Все плохо. Как обычно.

                      Вот смотрите — приехал ваш ТА к клиенту. Он работает с этим единственным клиентом. Где вы извлекаете из этого выгоду?

                      А ведь у ТА наверняка есть 5 минут до начала общения. В пути там.

                      Отчего в этот период во времени не скопировать данные только по этому клиенту во временную таблицу? И работать с ней, 5000 записей. Можно вообще в in-memory базу, устройство на аккумуляторе. Затем по нажатию волшебной кнопки данные переносятся обратно.

                      Можно вообще отказаться от таблицы прайсов и хранить у клиента xml в поле. Данные из xml переносятся в основную базу тоже по кнопке.

                      Короче, надо как следует продумать сценарий взаимодействия. Тогда не придется изобретать велосипед и боротьсяс O(n^2)
                        +1
                        В нашем городе много рынков и очень часто ТА работают именно там, на рынке у них нет лишних 5 минут. А что делать в той ситуации, если ТА ушел от одного клиента к другому, начал уже вводить новый заказ, а тут звонит предыдущий клиент и просит что-то изменить в его заказе? Ждать 5 минут, а потом еще 5 минут, чтобы вернуться к незавершенному заказу 2-го клиента?

                        Можно наверное и xml использовать, но как быть, если не все клиенты работают с уникальными прайсами? Наша схема позволяет ряду клиентов назначить индивидуальные прайсы, ряду какие-то стандартные, а для ряда клиентов позволить ТА самому выбрать из разрешенного списка. И это все в одной простой схеме.

                        Если Вы внимательно прочтете, что написано в статье (без обид), то увидите, что приведенное решение очень, очень простое и оно не предлагает каких-то неизвестных вещей, а просто показывает как можно решить проблему минимальными усилиями.
                          0
                          Ну 5 минут из головы взято. Скорее всего намного меньше займет.

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

                          Решение понятно, но вы заложили n^2. Появится еще 5000 товаров и еще 1000 клиентов и рано или поздно даже самая красивая оптимизация не поможет.

                            0
                            в качестве теста я грузил 1000 клиентов и 30 000 товаров, это давало 3 000 000 записей в таблице прайсов — скорость работы визуально такая же (SQLite очень быстр и запросы возвращающие данные для отображения в списке выполняются очень быстро), но это уже объемы которые не применимы для приложений данного типа

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

                      Самое читаемое