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

[zerkms]

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

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

Вы уверены, что вы не ошиблись в своих выводах?

[shuslav]

При выполнении запросов о которых идет речь разница во времени не очень большая, но в моем случае она все-же была (тестировалось на устройстве). Хотя полностью исключать вероятность ошибки не стоит, да и делалось все это более года назад, возможно уже новая версия SQLite вышла.

[Silver_Clash]

О чем вообще эта статья? О том что неплохо бы смотреть план запроса прежде чем выпускать приложение в продуктив?

[shuslav]

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

[Silver_Clash]

Вы сравнивали планы запроса select * и select Table1.Id?
Я думаю если вы сравните планы запросов, то увидите разницу. Скорее всего в случае когда вы выбирает в запросе столбец с индексом, то читается только индекс, а когда выбираете * то происходит либо full table scan либо кроме индекса читается так же сама таблица. Соответственно логично что чтение индекса получается намного быстрее.

В общем планы запросов в студию! Говорить о каком то ускорении без их анализа это все равно что ходить с бубном вокруг сервера.

[shuslav]

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

[Silver_Clash]

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

[shuslav]

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

[serveravt]

эмммм, а где всетаки Нестандартный подход к «повышению производительности»?

[shuslav]

ну все-таки разбиение процесса получения данных из БД не всегда себя обравдывает, да и как-то не встречалось мне подобное решение раньше, может плохо искал

[Silver_Clash]

Кстати на счет разбиения процесса получения данных — погуглите MapReduce. Это не в коем случае не руководство в действию, просто иногда бывает полезно расширять кругозор :)

[shuslav]

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

[Silver_Clash]

Это Вы мне на дешевом смартфоне для тривиальной задачи его рекомендуете применить?

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

OMG, вы читаете по диагонали, или видите только то что хотите, и не видите всего остального?

[shuslav]

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

[Silver_Clash]

Я не ставил своей целью поведать миру о «серебрянной пуле» я привел конкретное решение конкретной проблемы

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

[ilichme]

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

[shuslav]

пожалуйста, нужно было раньше написать, все руки не доходили

[ilichme]

отпишите хоть, из-за чего минусуете?

[Silver_Clash]

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

[shuslav]

Какая структура? Для данного примера достаточно 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-запросов, ну я уж не знаю как Вам это объяснить :)
И еще раз: речь идет не о десктопе, а о «маломощных» смартфонах, на десктопе Вы не заметите разницу в скорости (она будет очень несущественной).

[aeryaguzov]

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

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

[shuslav]

Ну а как получить-то название, описание и тд?))

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

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

Я привел пример показывающий что позволяет получить ускорение работы интерфейса.

[Silver_Clash]

Конечно объем данных может как то влиять на скорость работы. Я мало работал с MySQL и SQLite, работаю в основном с Oracle, но не думаю что в SQLite сильно отличается структура индексов.
Основная суть здесь заключается в том, что при выборке p.Id, p.Name, p.Description у вас происходит чтение блоков данных, а в случае одного p.Id у вас происходит только чтение индекса (все необходимые данные уже есть в индексе), а блоки данных не читаются. Вот при чем тут индексы.

[shuslav]

Есть много вещей с которыми я тоже не работал или работал мало, но если Вы работаете с Oracle, то наверное знаете кто такой Том Кайт, так вот в своих книгах он пишет о том, что начиная работать с какой-то СУБД нужно изучить ее особенности, а не пытаться применить к ней знания полученные при работе с другими СУБД (я имею ввиду какие-то тонкости естественно, а не базовые принципы). В данном случае, описанный мной подход позволил получить заметный прирост в скорости работы UI, и для меня именно это имеет значение, а не то, что данный подход, возможно, не укладывается в какие-то принципы работы Oracle или другой СУБД, кроме того, если Вы внимательно почитаете статью и мои ответы на комментарии, то увидите, что я уже писал, что этот подход не стоит рассматривать для применения на десктопах/серверах и пр.

[Silver_Clash]

Ваш подход вообще не стоит рассматривать для применения в серьезных приложениях.

[shuslav]

Ну да, он же на на Oracle и без блэкджета.
Так хоть скажите Кайта то читали? А-то совсем уж печально становится.

[Silver_Clash]

Если бы не читал, то и не стал бы говорить о том что недостаточно глубоко знаю SQLite.
К тому же я не Oracle разработчик, так, могу несложные запросики на пару сотен строк написать, да пакеты для приложения создавать. А вот наши Oracle разработчики пишут такие вещи на PLSQL что они мне иногда кажутся магией, приходится читать Кайта что бы поддерживать уровень.

[ilichme]

если вы не разработчик, как вы можете утверждать, что «Ваш подход вообще не стоит рассматривать для применения в серьезных приложениях»?

[shuslav]

ilichme, давайте уже завершим этот «пинг-понг» :) мы с Silver_Clash говорим о разных вещах я ему про ускорение работы интерфейса (объяснение которому он дал в своем же комментарии), а он мне про оптимизацию SQL-запросов — тему безусловно интересную, но выходящую за рамки этой статьи (о чем я написал, кстати, еще до ката)

[Silver_Clash]

А вы похоже тоже читаете по диагонали?

[shuslav]

видимо, что за кучей технического описания вы пропустили одну из самых важных вещей о которой он (Кайт) говорит очень часто, а именно, что работая с к-л СУБД не нужно пытаться применить к ней знания полученные при работе с другими СУБД (речь сейчас не об sql-запросах). любая СУБД это прежде всего инструмент и чтобы им правильно воспользоваться нужно, как минимум, изучить его (типы индексов, блокировки и т.д. и т.п.). вы уцепились за планы приведенных мною запросов, но не хотите понять, что дело не в них, здесь вы совершенно верно объяснили почему один из запросов работает быстрее, но упорно не хотите понять, почему это ведет у скорению работы UI

[Silver_Clash]

Ваше замечание не принимается пока вы не покажете что в SQLite при чтении индексных данных оптимизатор читает и блоки данных (даже если требуются только данные индекса).

[ilichme]

скажите, а вы пробовали в ListVIew отобразить, скажем, 100к строк из базы данных? и как результаты?

[shuslav]

возможно я не совсем правильно понял, но ведь ListView хранит в памяти, только строки видимые на экране? в нашем случае на экране помещалось примерно 7-10 строк

[ilichme]

да, но при фестскроле оно заметно притормаживает (суть тормозов кроется в WindowedCursor), а тот подход, который вы описали в статье, позволяет тормозов избежать. собственно, мой вопрос был адресован Silver_Clash

[Krovosos]

Все плохо. Как обычно.

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

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

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

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

Короче, надо как следует продумать сценарий взаимодействия. Тогда не придется изобретать велосипед и боротьсяс O(n^2)

[shuslav]

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

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

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

[Krovosos]

Ну 5 минут из головы взято. Скорее всего намного меньше займет.

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

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

[shuslav]

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