Pull to refresh

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

Reading time 11 min
Views 17K
Добрый день.

В ходе разработки своего проекта под 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);
  }  


Как видите в parseNameFromString есть даже лобовой перевод Строки в int, но этот топорный вариант работает быстрее Integer.valueOf (проверенно через профайлер)


В итоге, этот вариант обновления в базе данных работал в десятки раз быстрее первоначального. Т.е. обновление 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 затащил. Сам же код на С++ повторяет все идеи, что уже были улучшены в Java коде.
Ещё, мне очень помогла вот эта статья. В ней очень подробно описывается, как увеличить скорость вставки.

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

Код на C++
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.
Версия кода не финальная, так что придираться к каким-то не лучшим решениям не стоит. Суть кода, показать, как на С++ делать то же, что и на Java, но только на С++ оно будет работать в разы быстрее.

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

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

Мораль

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

UPD:
Спасибо to_climb, упомянул об ещё одном очевидном улучшении. Склеить 3 запроса на обновления в 1н запрос.
Т.е. не 3 стейтмента, а одни длинный вида
update table set var1= ?, var2 =? where _id =?
Tags:
Hubs:
+23
Comments 26
Comments Comments 26

Articles