Thank you all who supports sqlite!
I'm impressed with your work. It happened so that I read the release
notes of the newest 3.5.7 only recently. I tried my test with this
version and the result is
sqlite version | flash card type | time from (1) to (2) [milliseconds]
-------------------+----------------------------+------------------------------------------------------
3.5.7 | CF Toshiba (slow) | 8242
ten times improvement compared to 3.5.6. still twice slower in this
particular test then 3.4.x but in my application the performance is
good again.
Thanks a lot.
Regards,
Dima Dat'ko
On Wed, Mar 19, 2008 at 3:29 PM, Dima Dat'ko <[EMAIL PROTECTED]> wrote:
> 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
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
