Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Венцислав Русев]

Thank you for your advice Simon and Keith.

We strive to make the migration process faster, because our technicians 
are responsible of changing the program version of each embedded device 
and then migrating its database, not the end user.


I'm creating the indexes last as you said, but haven't used the analyze 
command. I will try it.


I found the problem to be in the way I'm compiling the source code. A 
forgotten option "-fsanitize=address" while compiling caused this slow down.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Simon Slavin]

On 12 Jun 2017, at 4:20pm, Simon Slavin  wrote:

> Please add the ANALYZE command after your existing VACUUM.

Before.  Not after.  Do ANALYZE, then VACUUM.  It might make no difference but 
technically it may yield a faster result.  Or a smaller file.  Something good.

> This can noticeably speed up any future operations on your database.

Yup.  Any big change or conversion of data should end up with ANALYZE; VACUUM .

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Keith Medcalf]

On Monday, 12 June, 2017 08:53, Венцислав Русев  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, )*;
> 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

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Simon Slavin]

On 12 Jun 2017, at 3:53pm, Венцислав Русев  wrote:

> To migrate a DB from version 3 to version 7 the C program does the following:

This migration is a one-time process, right ?  Each customer has to do it only 
once, then never again.  It not like they have to wait through it every day.  
You could just put up a "This will take a long time but just once." message.

A lot of this comes down to how unacceptably slow your current method is.  If 
it’s almost fast enough, then it’s probably not worth doing much work on it.  
If it’s so slow your customers are complaining, then it’s worth putting 
programming time into it.

> 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, )*;
>   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);

Speedup 1:

If "several thousand" is more than ten thousand, try using several transactions 
limited to ten thousand rows each.

Do you have lots of indexes ?  When importing data it’s faster to do

CREATE the tables (or copy a database which has empty tables in)
INSERT the rows
CREATE the indexes

Than to do the INSERTing last.

Please add the ANALYZE command after your existing VACUUM.  This can noticeably 
speed up any future operations on your database.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Hick Gunter]

"many (sometimes several thousand) statments" sounds like it could be heavy on 
memory requirements.

Are you inserting one row per statement or all rows in one statement? The 
latter would be really hard on memory because SQLite will have to parse the 
whole statement and generate a gigantic SQL program all in one go, instead of 
row by row.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ? ?
Gesendet: Montag, 12. Juni 2017 16:53
An: SQLite mailing list 
Betreff: [sqlite] Can I increase the performance of sqlite3_exec() or maybe 
change it to prepare+step?

Hello,

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, )*;
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.

How can I increase the performance of my program so that it reaches the 
performance of the command line tool?

My first bet is to prepare each individual statement and then execute it. 
Should it be faster than sqlite3_exec?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Венцислав Русев]

Hello,

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, )*;
   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.


How can I increase the performance of my program so that it reaches the 
performance of the command line tool?


My first bet is to prepare each individual statement and then execute 
it. Should it be faster than sqlite3_exec?


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users