On Monday, 12 June, 2017 08:53, Венцислав Русев <ven...@proxima-3.com> wrote:
> I am using sqlite C API to migrate a database. Migration consists of
> many SQL statements that are known in advance.
> To migrate a DB from version 3 to version 7 the C program does the
> following:
> 1. disable foreign_keys (PRAGMA foreign_keys = OFF);
> 2. open transaction (BEGIN TRANSACTION);
> 3. execute bunch of statements that migrates the DB to the next version
> using *sqlite3_exec(db, migrate[version], NULL, NULL, &errMsg)*;
> migrate[version] is consisting of many (sometimes several thousand)
> statements;
> 4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check);
> 5. commit transaction (COMMIT TRANSACTION);
> 6. enable foreign_keys again (PRAGMA foreign_keys = ON);
> 7. vacuums db file (vacuum);
> I've realized that using the command line tool the migration takes
> around 8 minutes, but the C program takes around 20 minutes. This time
> is consumed in point number 3 in the previous list.
Interesting. Is the command line tool and the C program using the *same*
version of SQLite?
> How can I increase the performance of my program so that it reaches the
> performance of the command line tool?
Depends what the problem is. I suppose that the migration SQL statements are
all simple static SQL since your call to sqlite3_exec does not have a callback
function. This means that the sqlite3_exec is in effect nothing more than a
loop which does:
while (statements to execute)
{
sqlite3_prepare_v2()
while (sqlite3_step() == SQLITE_ROW);
sqlite3_finalize()
}
since if you do not provide a callback pointer all the result processing is
skipped. The library sqlite3_exec is quite similar to the processing loop
(execute_prepared_stmt) contained in the shell, so I don't see what the
difference in processing time would be, if the versions of the sqlite3 shell
and the sqlite3 engine included in the application are the same.
How are you processing the command batch with the shell? Are you piping in the
input (sqlite3 database.db < commandfile.sql) or reading it with the .read
command? Is the input to the shell only "one sql statement per line" or is it
"all mushed together into a single line"? How about when the application
processes the same commands? Are you passing one command per sqlite3_exec()
invocation or are you passing it the whole multi-line block in one go? Unless
the "input formats" are the same, the comparison is not exactly meaningful ...
If the shell is processing a file which has multiple lines, can you remove all
the line endings and pass it as a single block and see if that takes a
comparable time to the time taken when using sqlite3_exec()?
> My first bet is to prepare each individual statement and then execute
> it. Should it be faster than sqlite3_exec?
Only if the difference is in having sqlite3_prepare_v2 "disassemble" the huge
block of statements which *does* copy the entire passed statement block
multiple times during parsing. Since the command line shell reads "a line at a
time" it does not have to do this, and perhaps this is where the time is being
taken.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
