Повысить эффективность импорта данных в SQLite с помощью С#

Ответ 1

Это довольно быстро для 6 миллионов записей.

Кажется, что вы делаете это правильно, некоторое время назад я читал на sqlite.org, что при вставке записей вам нужно поместить эти вставки внутри транзакции, если вы этого не сделаете, ваши вставки будут ограничены до 60 в секунду! Это связано с тем, что каждая вставка будет рассматриваться как отдельная транзакция, и каждая транзакция должна ждать полного вращения диска. Вы можете прочитать полное объяснение здесь:

http://www.sqlite.org/faq.html#q19

Фактически SQLite будет легко выполнять 50 000 или более инструкций INSERT в секунду на среднем настольном компьютере. Но это займет всего несколько десятков транзакций в секунду. Скорость передачи ограничена скоростью вращения вашего дисковода. Обычно транзакция требует двух полных оборотов дискового диска, который на диске 7200RPM ограничивает вас до 60 транзакций в секунду.

Сравнение вашего времени против среднего значения, указанного выше: 50 000 в секунду = > , которое должно занимать 2 000 секунд. Это немного быстрее, чем ваше время.

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

По умолчанию каждый оператор INSERT является собственной транзакцией. Но если вы окружите несколько инструкций INSERT с помощью BEGIN... COMMIT, тогда все вставки сгруппированы в одну транзакцию. Время, необходимое для фиксации транзакции, амортизируется по всем прилагаемым операторам вставки, и поэтому время для инструкции вставки значительно уменьшается.

В следующем абзаце есть некоторые намеки на то, что вы можете попытаться ускорить вставку:

Другой вариант - запустить PRAGMA synchronous = OFF. Эта команда заставит SQLite не ждать, пока данные достигнут поверхности диска, что сделает операции записи намного быстрее. Но если вы потеряете власть в середине транзакции, ваш файл базы данных может испортиться.

Я всегда думал, что SQLite был разработан для "простых вещей", мне кажется, что 6 миллионов записей - это работа для какого-то реального сервера базы данных, такого как MySQL.

Подсчет записей в таблице в SQLite с таким количеством записей может занять много времени, просто для вашей информации, вместо использования SELECT COUNT (*) вы всегда можете использовать SELECT MAX (rowid), который очень быстрый, но не настолько точным, если вы удаляете записи в этой таблице.

ИЗМЕНИТЬ.

Как заявил Майк Вудхаус, создание индекса после того, как вы вставили записи, должно ускорить все это, что является общим советом в других базах данных, но не может точно сказать, как это работает в SQLite.

Ответ 2

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

Я не могу сказать, что это определенно будет работать с SQLite, но поскольку для его перемещения нужно всего две строки.

Мне также интересно, может ли транзакция размером 6 миллионов строк зайти слишком далеко - не могли бы вы изменить код, чтобы попробовать разные размеры транзакций? Скажите 100, 1000, 10000, 100000? Есть ли "сладкое пятно"?

Ответ 3

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

...
string insertText = "INSERT INTO Data (ID,RAW) VALUES( ? , ? )";  // (1)

SQLiteTransaction trans = conn.BeginTransaction();
command.Transaction = trans;

command.CommandText = insertText;

//(2)------
   SQLiteParameter p0 = new SQLiteParameter();
   SQLiteParameter p1 = new SQLiteParameter();
   command.Parameters.Add(p0);
   command.Parameters.Add(p1);
//---------

Stopwatch sw = new Stopwatch();
sw.Start();
using (CsvReader csv = new CsvReader(new StreamReader(@"C:\Data.txt"), false))
{
   var f = csv.Select(x => new Data() { IDData = x[27], RawData = String.Join(",", x.Take(24)) });

   foreach (var item in f)
   {
      //(3)--------
         p0.Value = item.IDData;
         p1.Value = item.RawData;
      //-----------
      command.ExecuteNonQuery();
   }
 }
 trans.Commit();
...

Внесите изменения в разделы 1, 2 и 3. Таким образом, привязка параметров выглядит довольно быстро. Особенно, когда у вас много параметров, этот метод может сэкономить довольно много времени.

Ответ 4

Я сделал аналогичный импорт, но я позволю своему коду С# просто записать данные в csv, а затем запустить утилиту импорта sqlite. Я смог импортировать более 300 миллионов записей в течение, возможно, 10 минут таким образом.

Не уверен, что это можно сделать непосредственно из С# или нет.

Ответ 5

Какой читатель CSV вы используете? Я попробовал модуль Nuget CsvHelper, он не предоставляет Select Methid, как вы использовали.