Thank you Dennis for your answer. I proceed with playing with the issue.
To whom it might be interesting here are timing results for a simple
function demonstrating the problem (please see the code bellow).
sqlite version | flash card type | time from (1) to (2) [milliseconds]
-------------------+----------------------------+------------------------------------------------------
3.4.2 | SD SanDisk (fast) | 4335
3.4.2 | CF Toshiba (slow) | 4401
3.5.6 | SD SanDisk (fast) | 43993
3.5.6 | CF Toshiba (slow) | 79568
If i comment the UPDATE query (lines from (A) to (B) the results are
the following:
sqlite version | flash card type | time from (1) to (2) [milliseconds]
-------------------+----------------------------+------------------------------------------------------
3.4.2 | SD SanDisk (fast) | 2025
3.4.2 | CF Toshiba (slow) | 2099
3.5.6 | SD SanDisk (fast) | 2119
3.5.6 | CF Toshiba (slow) | 2310
Here is the test function. Compiler options were all default, no
specific SQLITE or compiler flags used.
8<--------------------------------------------
const char* const g_ddlCreateSchema =
"CREATE TABLE tblParent("
"keyParent INTEGER PRIMARY KEY AUTOINCREMENT,"
"fkeySelectedChild INTEGER NOT NULL,"
"CreateTimeInMs INTEGER NOT NULL);"
"CREATE TABLE tblChildren("
"keyChild INTEGER PRIMARY KEY AUTOINCREMENT,"
"fkeyParent INTEGER,"
"CreateTimeInMs INTEGER NOT NULL,"
"UpdateTimeInMs INTEGER NOT NULL DEFAULT 0);";
const char* const g_sqlInsertChild = "INSERT INTO
tblChildren(CreateTimeInMs) VALUES (?);";
const char* const g_sqlInsertParent = "INSERT INTO
tblParent(fkeySelectedChild, CreateTimeInMs) VALUES (?,?);";
const char* const g_sqlUpdateChild = "UPDATE tblChildren SET
fkeyParent=?, UpdateTimeInMs=? WHERE keyChild=?;";
void test_gen(LPCWSTR aDbFileName)
{
bool ok = true;
DeleteFile(aDbFileName);
sqlite3* db = 0;
int rc = sqlite3_open16(aDbFileName, &db);
ok = ok && (rc == SQLITE_OK);
ok = ok && (db != 0);
ok = ok && (SQLITE_OK == sqlite3_exec(db, g_ddlCreateSchema, 0, 0, 0));
ok = ok && (SQLITE_OK == sqlite3_exec(db, "BEGIN;", 0, 0, 0));
sqlite3_stmt *stmt_InsertChild = 0, *stmt_InsertParent = 0,
*stmt_UpdateChild = 0;
ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlInsertChild, -1,
&stmt_InsertChild, 0));
ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlInsertParent,
-1, &stmt_InsertParent, 0));
ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlUpdateChild, -1,
&stmt_UpdateChild, 0));
DWORD startTime = ::GetTickCount();
/*(1)*/
for(size_t i = 0; ok && i < 2000; ++i)
{
DWORD currentTime = ::GetTickCount();
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertChild, 1,
currentTime - startTime));
ok = ok && (SQLITE_DONE == sqlite3_step(stmt_InsertChild));
ok = ok && (SQLITE_OK == sqlite3_reset(stmt_InsertChild));
int new_child_id = ok ? (int)sqlite3_last_insert_rowid(db) : 0;
currentTime = ::GetTickCount();
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertParent, 1,
new_child_id));
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertParent, 2,
currentTime - startTime));
ok = ok && (SQLITE_DONE == sqlite3_step(stmt_InsertParent));
ok = ok && (SQLITE_OK == sqlite3_reset(stmt_InsertParent));
int new_parent_id = ok ? (int)sqlite3_last_insert_rowid(db) : 0;
currentTime = ::GetTickCount();
/*(A)*/
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 1,
new_parent_id));
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 2,
currentTime - startTime));
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 3,
new_child_id));
ok = ok && (SQLITE_DONE == sqlite3_step(stmt_UpdateChild));
ok = ok && (SQLITE_OK == sqlite3_reset(stmt_UpdateChild));
/*(B)*/
}
DWORD currentTime = ::GetTickCount();
/*(2)*/
ok = ok && (SQLITE_OK == sqlite3_finalize(stmt_InsertChild));
ok = ok && (SQLITE_OK == sqlite3_finalize(stmt_InsertParent));
ok = ok && (SQLITE_OK == sqlite3_finalize(stmt_UpdateChild));
ok = ok && (SQLITE_OK == sqlite3_exec(db, "COMMIT;", 0, 0, 0));
ok = (SQLITE_OK == sqlite3_close(db));
}
8<--------------------------------------------
As you can see in this test the version of 3.5.6 can be about 20 times
slower then 3.4.2. I'm wondering is not it a bug? Should I submit a
ticket?
So far I've tried several tricks with compiler flags and PRAGMAs with
no effect. What else can I try before getting to hack into SQLITE
source?
Please help me.
Regards,
Dima Dat'ko
On Mon, Mar 17, 2008 at 6:27 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Dima Dat'ko wrote:
>
> > Sorry for the long introduction. Here is the question. Am I right
> > there is no option defined to have the stmtjrnl file in memory or in
> > some specified path other then the same folder as the db? It's
> > critical to prevent the db corruption in all kinds of software and
> > hardware fails. If I manage stmtjrnl file to be created in memory
> > instead of the slow flash card and the file disappear after a power
> > brake on the device will it result in unrecoverable corruption of the
> > data in the db?
> >
>
> You are correct. SQLite requires the journal file to exist in the same
> directory as the database file itself. In memory databases don't have
> journal files at all.
>
> > Any other advice for my problem?
> >
>
> You could make a customized version of SQLite that keeps the journal at
> some other location. It would need to check that other location on
> startup, so that it can restore the database file using the journal
> entries in case there is a hot journal file left after a crash.
>
> Whatever process opens the database after a crash must have access to
> both the database file and the journal to do this restoration and avoid
> database corruption. This is why they are stored in the same directory
> by default. If there is a possibility of the user removing the flash
> card after a crash, and inserting it into the PC to open the database,
> then the journal must be on the flash card to do the rollback. If it
> must be on the flash card, it might as well be in the same directory.
>
> If you don't store the journal on the falsh card, your custom SQLite
> would have to have a mechanism to open the database and thereby rollback
> any uncommitted changes to ensure that the database is valid. The users
> must do this before removing the flash card with the database from the
> device.
>
> I would suggest using a modified SQLite only as a last resort.
>
> HTH
> Dennis Cote
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
