Оптимизируем скорость SQL вставки на Android устройствах

В ходе разработки своего проекта под Android, столкнулся с задачей делать обновление (update) большого количества строк в SQLite базе, прямо на устройстве.

Изначальное решение в лоб дало жутко медленный результаты, т.к. обновлять предстояло более 40 000 строк. О том как я улучшал производительности данных обновлений строк в базе, и пойдёт рассказ.

Более детальное описание задачи:

Приложение под Андроди распространялось с SQLite базой внутри (внутри APK, в asset-ах). В базе была информация по городам. Но информация базовая, независимая от языков, а зависимые от языка поля были только на английском.
Распространять программу со всеми языками было бы не реально, т.к. каждый язык в базе добавлял бы исходному APK файлу установщику от 1-2 мб. А языков поддерживалось 11.

Посему было придумано языковые патчи к базе, докачивать из интернета (с сервера), и уже на устройстве накатывать их на базу.
Патч из себя представлял зажатый gzip-ом текстовый файл, в каждой строке которого были значения, разделённые табом (\t)

Со скачиванием проблем не возникло. Это быстро. Но узкое место этой схемы — вставка в базу на устройстве.
Первый вариант я написал на Java с помощью известных всем встроенных в андроид возможностей работы с SQLite базой.
Надо было обновлять 3 поля в строке (добавлять значения из патча). Вот только строк таких было от 20 000 до 60 000, в зависимости от языка.

Первый вариант Java кода

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

 try   {     buffRead = new BufferedReader(fileIn, (1000*1024)); // 1000 KB        String line;     int lineNum = 0;     while ((line = buffRead.readLine()) != null)      {       try       {         String[] values = line.split("\t");         if (values.length < 2) // cause 3rd value van be empty         {           // some error, try next line           continue;         }          int idInt = Integer.valueOf(values[0]);         String name = values[1];         getDb().execSql("update " + getTableNabe() + " set " + lang.getColumnName() + " = ? where " + COLUMN_ID + " = ? ",                                            new String[] { name, String.valueOf(idInt) }); 										           getDb().execSql("update " + getTableNabe() + " set " + lang.getColumnSort() + " = ? where " + COLUMN_ID + " = ? ",                                           new String[] { String.valueOf(lineNum++), String.valueOf(idInt) });          if (values.lengh == 3 && values[2].length != 0)         {           String data = values[2];           getDb().execSql("update "+ getTableNabe() + " set " + lang.getColumnData() + " = ? where " + COLUMN_ID + " = ? ",                                            new String[] { data, String.valueOf(idInt) });         }       }       catch (NumberFormatException e)       {         e.printStackTrace();         return false;       }       catch (SQLException e)       {         e.printStackTrace();         return false;                   }        createIndexOnLang(lang); 	}// end of while   }   catch (IOException e)   {    e.printStackTrace();    return false;   }   finally   {     if (buffRead != null)     {       try       {         buffRead.close();       }       catch (IOException e)       {         e.printStackTrace();       }     }   }    return true; 

Ну естественно работало такое решение супер медленно. Точнее медленно сказать — это ничего ни сказать. Оно добавляло десятки минут.
Первое что напросилось, это добавить транзакцию.

Добавил перед циклом

    getDb().beginTransaction(); 

После цикла добавил

    getDb().setTransactionSuccessful(); 

А в блок finaly

    getDb().endTransaction(); 

Прирост это дало не большой. Тут я вспомнил что в Eclipse после установки туда Android Developer Tools (ADT) есть отличная перспектива DDMS, в которой есть отличная функция профилирования функций.

Как ей пользоваться можно почитать тут. На хабре кстати что-то не нашёл статей описывающих этот чудесный функционал ADT (может плохо искал, но нашёл только про анализ памяти.)

Просадки перформанса

С помощью этого механизма профилировано, стало сразу видно что просадки в производительности у меня в следующих местах
1. Работа со строками. spit жутко медленный метод. Может быть ни для кого это не откровение, но я был удивлён.
2. Работа со строками в плане склеивания строк для запросов. Внутри execSql как видите в цикле каждый раз делалось куча новых StringBuilder-ов, которые потом выкидывались за не надобностью. Как писали вот тут никогда в циклах не склеивайте строки по средством плюса (+). Используйте один заранее готовый StringBuilder. А ещё лучшее вообще строки заготовить заранее, до цикла. Что в моём случае очевиднейшее улучшение.
3. Работа самого SQL внутри библиотек андроида. Там делалась куча каких то локов и анлоков, почитав документацию нашёл метод у базы данных setLockingEnabled выставив который в false получаем не плохую прибавку к скорости.
4. Подготовка SQLiteStatement для каждого вызова execSql. Это тоже дорогая операция. Немного нагуглив, нашёл что стейтменты можно как и строки, заготовить заранее, а в цикле просто байндить в них параметры, и тут же выполнять.

Вторая версия Java кода

Решив все эти проблем, избавившись от split, вынув из цикла подготовку SQLiteStatement, убрав из цикла все работы со строками, добавив перед циклом getDb().setLockingEnabled(false) получился такой вариант

try   {     buffRead = new BufferedReader(fileIn, (1000*1024)); // 1000 KB          String line;     int lineNum = 0;     checkDbErrors();     getDb().beginTransaction();     getDb().setLockingEnabled(false);          // Prepare SQL queries     String updateStatment = "update " + getTableName() + " set ";     String whereStatment = " where " + COLUMN_ID + " = ?";     String updateNameSQL = updateStatment + lang.getColumns().getColumnName() + " = ? " + whereStatment;     String updatqDataSQL = updateStatment + lang.getColumns().getColumnData() + " = ? " + whereStatment;     String updatqSortSQL = updateStatment + lang.getColumns().getColumnSort() + " = ? " + whereStatment;     SQLiteStatement updateName = getDb().compileStatement(updateNameSQL);     SQLiteStatement updateData = getDb().compileStatement(updatqDataSQL);     SQLiteStatement updateSort = getDb().compileStatement(updatqSortSQL);          while ((line = buffRead.readLine()) != null)      {       try       {         int idInt = parseIdFromString(line);         String name = parseNameFromString(line, line.indexOf('\t') + 1);         String data= parseDataFromString(line, name.length() + 1);                          updateName.bindString(1, name);         updateName.bindLong(2, idInt);         updateName.execute();                  if (data.length() != 0)         {           updateWiki.bindString(1, data);           updateWiki.bindLong(2, idInt);           updateWiki.execute();         }                  updateSort.bindLong(1, lineNum++);         updateSort.bindLong(2, idInt);         updateSort.execute();                           }       catch (NumberFormatException e)       {         e.printStackTrace();         return false;       }       catch (SQLException e)       {         e.printStackTrace();         return false;                   }     }          getDb().setTransactionSuccessful();   }   catch (IOException e)   {     e.printStackTrace();     return false;   }   finally   {     getDb().endTransaction();     if (buffRead != null)     {       try       {         buffRead.close();       }       catch (IOException e)       {         e.printStackTrace();       }     }   }      return true; 

Методы
parseIdFromString(String line), parseNameFromString(String line, int from) и parseDataFromString(String line, int from) крайне топорны, но работают быстрее варианта со split

Вот они, кому интересно:

  private int parseIdFromString(String line)   {     int ind = line.indexOf('\t');     if (ind == -1)     {       return 0;     }          String idStr = line.substring(0, ind);     int length = idStr.length();     if (length == 0)     {       return 0;     }          int result = 0;     int zero = '0';     for (int i = 0; i < length; ++i)     {       result += (idStr.charAt(i) - zero) * Math.pow(10, length - i - 1);     }          return result;   }      private String parseNameFromString(String line, int from)   {     int ind = line.indexOf('\t', from);     if (ind == -1)     {       return new String();     }          return line.substring(from, ind);   }    private String parseDataFromString(String line, int from)   {     int ind = line.indexOf('\t', from);     if (ind == -1)     {       return new String();     }          return line.substring(from, ind);   }   

В итоге этот вариант обновления в базе данных работал в десятки раз быстрее первоначального. Ну т.е. на конкретных цифрах, обновление 43 000 строк этот алгоритм делал примерно за 1,5-2 минуты, на HTC Desire

Но данный результат не мог меня удовлетворить. Не очень круто пользователю программы ждать 2 минуты, пока мы ему в базу вставим нужный ему язык.

С Java можно было бы ещё пошаманить, но на порядки скорость увеличить точно не получилось бы, ибо результаты прогона профайлера на последнем варианте кода, явно показывал, что теперь самое долгое делается внутри native_execute метода из ( Android SDK\sources\android-14\android\database\sqlite\SQLiteStatement.java) нативный метод, уже явно работаюший на с++
Но наравне с ним мы теряли кучу времени в каком то магическом метода logTimeStat, надобность которого я так и не понял, и как его отключить, тоже. Кроме того байнды в стейтменте работали тоже не сильно быстро, да и вообще, это же Java… какая в нём может быть производительность (сарказм, к Java ничего плохого не имею)

Пишем всё на С++

В итоге я решил дальше не заморачиваться с Java и написать свою вставку на С++ (как говорится с блэкджеком и… ). Собрать по средствам NDK и звать её из Java через JNI.

С этой идеей есть одна проблема, где взять sqlite под NDK. Ну, собственно — элементарно. Берём исходный файл sqlite c оф. сайта и просто добавляем его целиком в свою либину под NDK.

Как собирать код под NDK писать не буду, ибо на Хабре, да и не только на нём есть много информации

Небольшая ремарка, по поводу включения в свою библиотеку исходников SQLite. Пока гуглил эту тематику, находил официальные гугл группы андроид разработчиков (к сожалению ссылки не сохранились), на которых обсуждали варианты работы с базой через свои нативные либы в которые вбилженны какие-то свои версии SQLite (ведь версии разные). Так вот там официальные люди из Андроида не очень одобрительно относились к такой практике, говорили что в теории это может попортить базу, ведь на самом устройстве может быть какая-нибудь другая версия SQLite, и что работая из своей либы а потом из Java уже стандартными средствами с одной и той же базой, вы можете разломать её. Но в моей практике разламывание базы случалось только при принудительном завершении программы, в момент когда моя либа обновляла содержимое базы. Случай это редкий, ибо делает программа это не долго. Но и для меня это случай не критичный, т.к. база всегда лежит у меня в asset и я при любом разломе могу её восстановить, и попросить пользователя снова скачать языки и накатить их снова.
Так вот люди из Андроида советовали, что раз вам очень надо работать с SQLIte базами из нативного кода, то и работайте только из него, и Java средствами Андроида эти базы не трогайте. В этом случае с базой гарантированно всё будет хорошо, т.к. вы будете работать с ними только с той версией SQLite что у вас есть.

Вернёмся к истории.
Решил я писать свой апдейт. SQLite затащил. Сам же код на С++ повторяет все идеи что уже были улучшены в Ява коде.
Мне очень помогла ещё вот эта статья. В ней очень подробно описывается как увеличить скорость вставки.

Кода получилось много, кому интересно могут посмотреть

std::vector<std::string>& split(const std::string &s, char delim, std::vector<std::string> &elems) {   elems.clear();   std::stringstream ss(s);   std::string item;   while (std::getline(ss, item, delim))    {     elems.push_back(item);   }    return elems; }  std::string prepareUpdateStatment(std::string columnName, std::string columnValue, std::string id) {   std::ostringstream constructor;   constructor << "update cities set " << columnName << " = \"" << columnValue << "\" where _id = " << id;   return constructor.str(); }  std::string prepareUpdateStatmentForBind(std::string columnName) {   std::ostringstream constructor;   constructor << "update cities set " << columnName << " = ? where _id = ? ";   return constructor.str(); }  std::string getColumnName(std::string column, std::string lang) {   std::ostringstream constructor;   constructor << lang << "_" << column;   return constructor.str(); }  std::string parseInt(int i) {   std::ostringstream ss;     ss << i;     return ss.str(); }  bool pushToDBWithPreparedStatments(std::string line, sqlite3* db, std::string lang, int lineNum, sqlite3_stmt* stmtnUpdateName, sqlite3_stmt* stmtnUpdateSort, sqlite3_stmt* stmtnUpdateData) {   if (line.size() == 0)   {     return true; // end of file   }    int error = SQLITE_OK;   std::vector<std::string> elems;   elems = split(line, '\t', elems);    if (elems.size() < 2)   {     log("line parse error");     return false;   }    std::string& idStr = elems[0];   int idInt = atoi(idStr.c_str());    std::string& nameStr = elems[1];   sqlite3_bind_text(stmtnUpdateName, 1 , nameStr.c_str(), -1, SQLITE_STATIC);    sqlite3_bind_int(stmtnUpdateName, 2 , idInt);    if ((error = sqlite3_step(stmtnUpdateName)) != SQLITE_DONE)   {     logError(error, sqlite3_errmsg(db));     return false;   }      sqlite3_clear_bindings(stmtnUpdateName);   sqlite3_reset(stmtnUpdateName);    sqlite3_bind_int(stmtnUpdateSort, 1 , lineNum);   sqlite3_bind_int(stmtnUpdateSort, 2 , idInt);    if ((error = sqlite3_step(stmtnUpdateSort)) != SQLITE_DONE)   {     logError(error, sqlite3_errmsg(db));     return false;   }      sqlite3_clear_bindings(stmtnUpdateSort);   sqlite3_reset(stmtnUpdateSort);    if (elems.size() == 3)   {     std::string& DataStr = elems[2];     sqlite3_bind_text(stmtnUpdateData, 1 , DataStr.c_str(), -1, SQLITE_STATIC);      sqlite3_bind_int(stmtnUpdateData, 2 , idInt);      if ((error = sqlite3_step(stmtnUpdateData)) != SQLITE_DONE)     {       logError(error, sqlite3_errmsg(db));       return false;     }      sqlite3_clear_bindings(stmtnUpdateData);     sqlite3_reset(stmtnUpdateData);   }    return true; }  void parseAndUpdateDB(std::string databasePath, std::string patchPath, std::string lang) {   time_t beforeStartTime = time(NULL);   sqlite3* db;   if (sqlite3_open_v2(databasePath.c_str(), &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX |  SQLITE_OPEN_PRIVATECACHE, NULL) != SQLITE_OK)   {     logError("Error wile opening db", sqlite3_errmsg(db));     return;   }    std::string line;   std::ifstream myfile(patchPath.c_str());   if (!myfile.is_open())   {     log("Error wile opening patch file");     return;   }    int lineNum = 0;   int error = SQLITE_OK;   // Begin transaction   if ( (error = sqlite3_exec(db, "begin", NULL, NULL, NULL)) != SQLITE_OK)   {     logError(error, sqlite3_errmsg(db));     return;   }    sqlite3_stmt* stmtnUpdateName;   std::string updateName = prepareUpdateStatmentForBind(getColumnName("name", lang));   if ( (error = sqlite3_prepare(db, updateName.c_str(), updateName.length(), &stmtnUpdateName, NULL)) != SQLITE_OK)   {     logError(error, sqlite3_errmsg(db));     return;   }    sqlite3_stmt * stmtnUpdateSort;   std::string updateSort = prepareUpdateStatmentForBind(getColumnName("sort", lang));   if ( (error = sqlite3_prepare(db, updateSort.c_str(), updateSort.length(), &stmtnUpdateSort, NULL)) != SQLITE_OK)   {     logError(error, sqlite3_errmsg(db));     return;   }    sqlite3_stmt * stmtnUpdateData;   std::string updateData = prepareUpdateStatmentForBind(getColumnName("data", lang));   if ( (error = sqlite3_prepare(db, updateData.c_str(), updateData.length(), &stmtnUpdateData, NULL)) != SQLITE_OK)   {     logError(error, sqlite3_errmsg(db));     return;   }    // For fast work   sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);   sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, NULL);    while ( myfile.good() )   {     std::getline(myfile, line);     if (!pushToDBWithPreparedStatments(line, db, lang, lineNum++, stmtnUpdateName, stmtnUpdateSort, stmtnUpdateData))     {       break;     }   }    sqlite3_finalize(stmtnUpdateName);   sqlite3_finalize(stmtnUpdateSort);   sqlite3_finalize(stmtnUpdateData);    // End transaction   if ( (error = sqlite3_exec(db, "end", NULL, NULL, NULL)) != SQLITE_OK)   {     logError(error, sqlite3_errmsg(db));     return;   }    sqlite3_close(db);   myfile.close();   time_t afterFinishTime = time(NULL);   int result = afterFinishTime- beforeStartTime;   log("result of run is %d secs" , result); } 

Кстати писал и отлаживал этот кода я под Windows в Visual Studio, а потом собрал его же под NDK, и всё магическим образом заработало и под Android.
Версия кода не финальная, так что придираться к каким-то не лучшим решениям не стоит. Суть кода, показать как на С++ делать тоже c базой данных что и на Ява, но только на С++ оно будет работать в разы быстрее.

Так вот о скорости.

Та же вставка 43 000 строк, на том же HTC Desire под дебагом (с подключенным Eclipse) отрабатывал порядка 43 секунд. Т.е. где то одна строка за 1 мс. Если же отрубить Eclipse и дебаг, получали поистине быстрый результат в районе 20-25 секунд. На более мощных девайсах типа HTC One S, процесс вставки вообще занимал около 10-15 секунд. Что с первоначальными минутами, показывает что все усилия по улучшению производительности были предприняты не зря.

Мораль

На примере моей задачи я показал как можно убыстрять работу с SQLite при разработке под Android (вплоть до перехода на нативный уровень). Не спорю что есть ещё масса вариантов проделать тоже самое, но думаю кому-нибудь эта информация поможет сделать свои приложения ещё более отзывчивыми и быстродейственными.