Hi,
I've got an application that creates a database with a large number of records
(millions), and the indexation of the tables is taking a long time. Once the
database is initially created, it is never modified. No records are added or
deleted. So, in the case where a user has sufficient memory, I want to offer
the option to create the entire database in memory, including the indexes, and
then serialize that to disk. I am not sure if this is really going to work or
if it will be more effcient. I am using the "ATTACH DATABASE" command to
create a copy of my ":memory:" database. I can copy copy the tables (see
below), and I can get a list of the indxes (see further below), but I don't
know how to copy the indexes. Also, does anyone know if this method is really
going to be faster (memory db -> disk db) than doing everything with a disk db?
Would the indexes really be copied or just re-created?
//get the list of tables
sql_cmd = "SELECT name FROM SQLITE_MASTER WHERE type = 'table'";
rc = sqlite3_prepare( m_db, sql_cmd.c_str(),(int)sql_cmd.length(), &stmt, &tail
);
std::vector<string> tables;
rc = sqlite3_step(stmt);
while ( rc != SQLITE_DONE && rc != SQLITE_ERROR )
{
tables.push_back((const char*) sqlite3_column_text ( stmt,0 ));
rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
//get the list of indexes
sql_cmd = "SELECT name, tbl_name FROM SQLITE_MASTER WHERE type = 'index'";
rc = sqlite3_prepare( m_db, sql_cmd.c_str(),(int)sql_cmd.length(), &stmt, &tail
);
std::vector<std::pair<string, string>> indexes;
rc = sqlite3_step(stmt);
while ( rc != SQLITE_DONE && rc != SQLITE_ERROR )
{
indexes.push_back(std::pair<string, string>((const char*) sqlite3_column_text
( stmt,0 ), (const char*) sqlite3_column_text ( stmt,1 )));
rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
rc = sqlite3_exec(m_db,"begin;", callback, 0, &zErrMsg);
//copy the tables
for (vector<string>::const_iterator it = tables.begin(); it != tables.end();
it++)
{
string sTable = *it;
sql_cmd = "CREATE TABLE dbdisk." + sTable + " AS SELECT * FROM " + sTable +
";";
rc = sqlite3_exec(m_db,sql_cmd.c_str(), callback, 0, &zErrMsg);
}
//copy the indexes
for (vector<std::pair<string, string>>::const_iterator it = indexes.begin(); it
!= indexes.end(); it++)
{
string sIndex = (*it).first;
string sTable = (*it).second;
//to be filled in
}
rc = sqlite3_exec(m_db,"commit;", callback, 0, &zErrMsg);
____________________________________________________________________________________
Don't get soaked. Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
