Или если я использую другой молоток для решения этой проблемы.
У меня есть очень простой пример использования для того, чтобы хранить данные, эффективно разреженная матрица, которую я попытался сохранить в базе данных SQLite. Я составил таблицу:
create TABLE data ( id1 INTEGER KEY, timet INTEGER KEY, value REAL )
в который я вставляю много данных, (800 элементов каждые 10 минут, 45 раз в день), большинство дней года. Кортеж (id1, timet) всегда будет уникален.
Значение timet является секундами с эпохи и будет всегда увеличиваться. id1, для всех практических целей, случайного целого числа. Существует, вероятно, только 20 000 уникальных идентификаторов все же.
Я затем хотел бы получить доступ ко всем значениям где id1 == someid или доступ все элементы где timet == когда-то. На моих тестах с помощью последнего SQLite через интерфейс C на Linux поиск для одного из них (или любой вариант этого поиска) занимает приблизительно 30 секунд, который не достаточно быстр для моего варианта использования.
Я пытался определить индекс для базы данных, но это замедлило вставку к абсолютно неосуществимым скоростям (я, возможно, сделал это неправильно хотя...),
Приведенная выше таблица приводит к очень медленному доступу для любых данных. Мой вопрос:
Извините мое очень простое понимание SQL!
Спасибо
Я включаю пример кода, который показывает, как скорость вставки замедляется к проверке при использовании индексов. С 'создают индекс' операторы на месте, код занимает 19 минут для завершения. Без этого это работает через 18 секунд.
#include <iostream>
#include <sqlite3.h>
void checkdbres( int res, int expected, const std::string msg )
{
if (res != expected) { std::cerr << msg << std::endl; exit(1); }
}
int main(int argc, char **argv)
{
const size_t nRecords = 800*45*30;
sqlite3 *dbhandle = NULL;
sqlite3_stmt *pStmt = NULL;
char statement[512];
checkdbres( sqlite3_open("/tmp/junk.db", &dbhandle ), SQLITE_OK, "Failed to open db");
checkdbres( sqlite3_prepare_v2( dbhandle, "create table if not exists data ( issueid INTEGER KEY, time INTEGER KEY, value REAL);", -1, & pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
checkdbres( sqlite3_prepare_v2( dbhandle, "create index issueidindex on data (issueid );", -1, & pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
checkdbres( sqlite3_prepare_v2( dbhandle, "create index timeindex on data (time);", -1, & pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
for ( size_t idx=0; idx < nRecords; ++idx)
{
if (idx%800==0)
{
checkdbres( sqlite3_prepare_v2( dbhandle, "BEGIN TRANSACTION", -1, & pStmt, NULL ), SQLITE_OK, "Failed to begin transaction");
checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute begin transaction" );
checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize begin transaction");
std::cout << "idx " << idx << " of " << nRecords << std::endl;
}
const size_t time = idx/800;
const size_t issueid = idx % 800;
const float value = static_cast<float>(rand()) / RAND_MAX;
sprintf( statement, "insert into data values (%d,%d,%f);", issueid, (int)time, value );
checkdbres( sqlite3_prepare_v2( dbhandle, statement, -1, &pStmt, NULL ), SQLITE_OK, "Failed to build statement");
checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
if (idx%800==799)
{
checkdbres( sqlite3_prepare_v2( dbhandle, "END TRANSACTION", -1, & pStmt, NULL ), SQLITE_OK, "Failed to end transaction");
checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute end transaction" );
checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize end transaction");
}
}
checkdbres( sqlite3_close( dbhandle ), SQLITE_OK, "Failed to close db" );
}
Вы вставляете сразу все 800 элементов? Если да, то выполнение вставок внутри транзакции значительно ускорит процесс.
См. http://www.sqlite.org/faq.html#q19
SQLite может обрабатывать очень большие базы данных. См. http://www.sqlite.org/limits.html
Я просмотрел ваш код и думаю, что вы можете переборщить с операторами prepare
и finalize
. Я ни в коем случае не эксперт по SQLite, но при подготовке оператора каждый раз при прохождении цикла требуются значительные накладные расходы.
Цитата с веб-сайта SQLite:
После того, как подготовленный оператор был оценивается одним или несколькими вызовами
sqlite3_step ()
, его можно сбросить в чтобы быть оцененным снова по звонку вsqlite3_reset ()
. С использованиемsqlite3_reset ()
на существующем подготовленное заявление, а скорее создание новый подготовленный оператор избегает ненужные звонки вsqlite3_prepare ()
. Во многих SQL операторы, время, необходимое для запускаsqlite3_prepare ()
равно или превышает время, необходимое дляsqlite3_step ()
. Так что избегая звонковsqlite3_prepare ()
может привести к значительное улучшение производительности.
http://www.sqlite.org/cintro.html
В вашем случае, вместо того, чтобы каждый раз готовить новый оператор, вы можете попробовать привязать новые значения к существующему оператору .
Все это говорит о том, что я думаю, что на самом деле виноваты индексы, поскольку время продолжает увеличиваться по мере того, как вы добавляете новые данные. Мне достаточно любопытно, что я планирую провести несколько тестов на выходных.
Рассмотрите возможность использования таблицы для новых вставок заданного дня без индекса. Затем в конце каждого дня запускайте сценарий, который:
Если вы можете выполнять поиск исторических данных в O ( log n) и поиск сегодняшних данных в O (n), это должно обеспечить хороший компромисс.
Поскольку мы знаем, что сбор данных происходит быстро, когда в таблице нет индекса, на самом деле может работать следующее:
Захватить 800 значений во временной таблице без индекса.
Скопируйте записи в главную таблицу (содержащую индексы), используя форму INSERT INTO, которая принимает оператор SELECT.
Удалить записи из временной таблицы.
Этот метод основан на теории, согласно которой INSERT INTO, который принимает оператор SELECT, выполняется быстрее, чем выполнение отдельных INSERT.
Шаг 2 может выполняться в фоновом режиме с использованием асинхронного модуля , если он все еще оказывается немного медленным. Это позволяет использовать небольшое время простоя между захватами.
Я не могу сказать по вашим спецификациям, но если поле ID всегда увеличивается, а поле времени включает YYYYMMDD для уникальности и также всегда увеличивается, и вы делаете либо поиск по ID, либо поиск по времени, то самым простым решением без использования базы данных будет просто добавить все записи в текстовый или двоичный файл с фиксированным полем (поскольку они генерируются в "отсортированном" порядке) и использовать код для выполнения двоичного поиска нужных записей (например, найти первую запись с интересующим вас ID или временем, затем последовательно пройти через нужный диапазон).
Отвечая на мой собственный вопрос просто как место, где можно указать некоторые детали:
Оказывается (как правильно предложено выше), создание индекса является медленным шагом, и каждый раз, когда я выполняю новую транзакцию вставок, индекс обновляется, что занимает некоторое время. Мое решение: (A) создать таблицу данных (B) вставьте все мои исторические данные (за несколько лет) (C) создать индексы
Теперь все поиски и т. Д. Выполняются очень быстро, а sqlite отлично справляется со своей задачей. Последующие ежедневные обновления теперь занимают несколько секунд, чтобы вставить только 800 записей, но это не проблема, поскольку они запускаются только каждые 10 минут или около того.
Спасибо Роберту Харви и maxwellb за помощь / предложения / ответы выше.