“Think of SQLite not as a replacement for Oracle but as a replacement for fopen()”
— About SQLite

А также, скорее всего, под Android, BlackBerry и в сэнд-боксе браузеров для веб-приложений, но я не проверял.

Почему может быть нужно работать с SQLite напрямую?

Любой опытный iOS девелопер тут же упрекнёт меня за использование SQLite напрямую (вернее не напрямую, а через FmDb, но это почти всё равно что напрямую). Он скажет, что нужно использовать CoreData, т.к. оно много всяких ништяков делает автоматически, типа Undo и Redo. И в нём можно рисовать красивые схемки, которые потом приятно показывать заказчику. А в андроиде, например, есть OrmLite.

И я соглашусь – но до той поры, пока у вас база не перевалила, скажем, за 10 таблиц по 500,000 записей в каждой. А если таблиц 52, и есть особо жирные таблицы по миллиону и больше? И базу нужно синхронизировать с сервером через третий формат, к тому же заказчику критично, будет синхронизация длиться час или пять? Если вы встречали задачи такого объёма, добро пожаловать под кат! Если не встречали – то тоже, ведь никто не застрахован от проектов с большими данными, пускай даже таких мобильных и меньше.

Очерёдность операторов запроса

Дело в том, что, когда нас учат работать с базой данных, нас в большинстве случаев учат работать с лучшими из энтерпрайз решений. Меня, например, в институте учили на Oracle, кого-то – на MS SQL. Но SQLite в разы проще – это, например, следует из эпиграфа к статье, взятого с официального сайта SQLite.

Совершенно случайно я обратил внимание, что

SELECT * FROM tablename WHERE col1 LIKE ‘%string%’ AND col2 = 123456

работает в 3-4 раза медленнее чем

SELECT * FROM tablename WHERE col2 = 123456 AND col1 LIKE ‘%string%’

на таблице из, скажем, 300,000 записей. Мы просто поменяли операторы местами, а как изменился результат!

В учебниках по базам данных, скорее всего, даже не акцентируют внимания на таких особенностях, и правильно делают – во всех энтерпрайз решениях есть оптимизаторы запросов. К примеру, на MS SQL Server 2008 Web Edition на тех же данных и тех же запросах разницы никакой.

Но на SQLite есть. Это нужно помнить. В мире SQLite более “простые” операции всегда должны идти левее более “сложных”.

Базу SQLite тоже можно и нужно индексировать

Думая об SQLite как об альтернативе fopen, базе без хранимых процедур, семафоров и юзеров, забываешь, что она, как и любая нормальная база, поддерживает индексы. Про них столько написано, что не стоит заострять внимания на синтаксических особенностях – просто запомните, что как только размер базы превысит 50,000 строк – её нужно проиндексировать. А при использовании сложных запросов – и раньше.

Позволю себе только маленькое замечание – индексирование лучше всего проводить после того, как написаны основные запросы, на основе их анализа. При проектировании БД, когда девелопер ещё не знает назубок всю бизнес-логику приложения, он может заблуждаться относительно того, по какому полю будет чаще всего происходить поиск/выборка. Однако имея SQL-запросы перед глазами, написать правильный индекс ничего не стоит.

Если часто делаете выборку по нескольким таблицам, имеет смысл закешировать данные

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

• Год выпуска
• Производитель
• Модель
• Комплектация
• Некоторые иные технические характеристики, которые заказчик пожелал показывать при выборе машины. Например, MPG (miles per gallon, аналог наших километров на литр).

Делая запрос по пяти таблицам и составляя список машин, мы затормозили айфон до отклика 6-8 секунд на каждом пикере. Выхода два – можно при первом запуске (у нас – при синхронизации) либо составлять представление со всеми возможными данными, либо, если удобнее, сохранять объекты с данными прямо на жёсткий диск. Тоже один раз, в удобный момент, когда пользователь знает о задержке и готов подождать.

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

SQLite – однопоточная база

Не связано напрямую с оптимизацией, но об этом также не стоит забывать. Обращение к SQLite базе из двух потоков одновременно неизбежно вызовет краш. Выхода два:

• Синхронизируйте обращения при помощи директивы @synchronized. Это если поздно менять архитектуру, как было у меня;
• Если задача закладывается на этапе проектирования, завести менеджер запросов на основе NSOperationQueue. Он страхует от ошибок автоматически, а то, что делается автоматически, часто делается без ошибок.

Вообще, обращаться к базе всегда лучше из второстепенных потоков, даже если UI-ный поток ничего не делает, кроме прокрутки индикатора ожидания. Время на написание методов делегата и асинхронизацию бизнес-логики с лихвой окупится положительным user experience.

Вывод

Всё, что я перечислил выше, было лишь частными случаями. Нету универсального таска оптимизации, который можно заэстимировать в 1.5 дней или 10% от общего времени проекта. Этим приходится заниматься по мере необходимости. Главное правило, которое всегда помогало мне правильно выбрать путь оптимизации – время, затраченное на выборку, не должно увеличиваться с ростом размера базы кроме особых случаев. Оно должно оставаться примерно одинаковым как на ста записях, так и на ста тысячах.

И ещё одно правило, любимая фраза моего начальника – не оптимизируйте то, что не нуждается в оптимизации. Зачастую юзеру не важно, будет ваш код выполняться одну миллисекунду или сто, а заказчику важно, задержали вы проект на день или сдали вовремя.