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

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

Прежде всего, хотелось бы отметить 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 это первичный ключ.

Итого

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

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

Автор: shuslav