Hallo list,
I'm relatively inexperienced when it comes to databases and SQL (but to
programming). I'm using sqlite's (recent version) C API called from a
C++ application.
I'm confronted with the following situation:
Ca. 2500-3000 objects (called 'entity') everyone with 3 properties (REAL
in sqlite notation) depending on 'positions' (x,y,z). (x,y,z) are
INTEGERS, the grid size might slightly vary between the entities.
The database in a first attempt looks like:
EntityTable
key val1 val2 ....
1
2
.
.
2500
MatrixTable
key key_to_entitytable x y z prop1 prop2 prop2
1 1 0 0 0 . . .
. 1 0 0 1 . . .
. 1 0 0 2 . . .
. . . . . . . .
25*10^6 . . . . . . .
My problem is the second table. Appending all 25*10^6 rows takes 40
minutes on a PC with 3 GHz, 1GB memory and sufficient large harddisk.
1)
On a technical level, my usage of the sqlite API might be improper. So
I'll attach the relevant code encapsulating the appends :
The sql() functions utilizes more or less sqlite_exec + error handling,
but has no impact here.
table ... name of the sqlite table
stmtmatrix ... vector of rows ('BindRecord's' - 25 million for the mentioned
case)
void Database::append(const QString& table, const vector<BindRecord>&
stmtmatrix,
bool skipprimarykey /*=true*/)
{
if (stmtmatrix.empty())
return;
QString pstr("insert into ");
pstr += table + " values (";
if (skipprimarykey)
pstr += "NULL, ";
pstr += "?";
for (unsigned i=1; i!=stmtmatrix[0].values.size(); ++i)
{
pstr += ",?";
}
pstr += ")";
int c = 0;
sql("begin");
sqlite3_stmt *stmt;
if (sqlite3_prepare(
db_,
pstr.ascii(), // stmt
-1,
&stmt,
0
)!= SQLITE_OK)
{
printf("\nCould not prepare statement.");
return;
}
for (unsigned i=0; i!=stmtmatrix.size(); ++i) // iterating rows
{
for (unsigned j = 0; j!=stmtmatrix[i].values.size(); ++j)
{
int ERR = sqlite3_bind_text (
stmt,
j+1, // Index of wildcard
stmtmatrix[i].values[j].ascii(),
stmtmatrix[i].values[j].length(), // length of text
SQLITE_STATIC
);
if (ERR != SQLITE_OK)
{
printf("\nCould not prepare statement.");
}
}
sqlite3_step(stmt);
sqlite3_reset(stmt);
++c;
if (c==100000)
{
sql("commit");
sql("begin");
c=0;
}
}
sql("commit");
sqlite3_finalize(stmt);
}
Calling Database::.sql("PRAGMA synchronous = OFF")
before Database::append improves speed, but not beyond the 40 minutes
cited. Can someone elaborate on the code regarding performance ?
2)
Database design. Is this big table a good choice or has anyone
recommendations for better design. For example, splitting the big table
into smaller ones (assigned to a single entity) with ~100000 rows and
later on building database requests utilizing joints ? Or something
completely different ?
Thank you,
Micha
--
