Incredible SQLite speed

Share your advanced PureBasic knowledge/code with the community.
User avatar
Janni
Enthusiast
Enthusiast
Posts: 127
Joined: Mon Feb 21, 2022 5:58 pm
Location: Norway

Incredible SQLite speed

Post by Janni »

I'm just learning myself SQL with PB and tried to import a text file with 561.476 lines (splitted into 3 columns).
After some 15 minutes I stopped the job and by then it had imported around 183.000 lines.

Then I found a tip from grabiller in 2012 in this forum where you can use BEGIN and COMMIT with SQLite

Code: Select all

DatabaseUpdate( dbid, "BEGIN;" )

DatabaseUpdate( dbid, "INSERT INTO ../.." )
DatabaseUpdate( dbid, "INSERT INTO ../.." )
; ../..

DatabaseUpdate( dbid, "COMMIT;" )
After this change, the job of importing 561.476 rows took under 6 seconds! :shock:
This is totally amazing to me on my old computer....
Spec: Linux Mint 20.3 Cinnamon, i7-3770K, 16GB RAM, RTX 2070 Super
User avatar
Paul
PureBasic Expert
PureBasic Expert
Posts: 1243
Joined: Fri Apr 25, 2003 4:34 pm
Location: Canada
Contact:

Re: Incredible SQLite speed

Post by Paul »

Another SQLite speedup trick is to put these two commands before you start Inserting data

Code: Select all

DatabaseUpdate(db,"PRAGMA Journal_Mode=OFF;")
DatabaseUpdate(db,"PRAGMA synchronous=0;")
It turns Inserting hundreds of thousands of lines of data from many minutes into a few seconds.
Image Image
User avatar
spikey
Enthusiast
Enthusiast
Posts: 581
Joined: Wed Sep 22, 2010 1:17 pm
Location: United Kingdom

Re: Incredible SQLite speed

Post by spikey »

Janni wrote: Fri Aug 05, 2022 11:30 pm After some 15 minutes I stopped the job and by then it had imported around 183.000 lines.
This is because SQLite automatically embeds most commands inside a separate transaction if you don't explicitly begin one, see https://www.sqlite.org/lang_transaction.html. So what you were originally doing was:

Code: Select all

BEGIN
  INSERT INTO
COMMIT
BEGIN
  INSERT INTO
COMMIT...
and you changed this to:

Code: Select all

BEGIN
  INSERT INTO
  INSERT INTO...
COMMIT  
The speed increase comes from not having to do as much post transaction tidy up overall. Begin a transaction before you write to the database, unless the change is trivial. As you've discovered its quicker but it also gives you the option to rollback in the event something causes you to change your mind further on.
Paul wrote: Sat Aug 06, 2022 2:09 am

Code: Select all

DatabaseUpdate(db,"PRAGMA Journal_Mode=OFF;")
DatabaseUpdate(db,"PRAGMA synchronous=0;")
Just for the record read https://www.sqlite.org/pragma.html carefully before mucking about with journal_mode or synchronous. Its, possibly, ok to use these when commissioning a new database or migrating from one database to another offline where a fully consistent backup of the source database is available and the 'server' is protected by a UPS. But ... the journal is there to stop you from looking stupid in the event of a failure and switching it off makes you vulnerable to every transient on the power supply or who knows what else... Don't do this if your database is important!
Last edited by spikey on Sat Aug 06, 2022 3:16 pm, edited 1 time in total.
Rinzwind
Enthusiast
Enthusiast
Posts: 636
Joined: Wed Mar 11, 2009 4:06 pm
Location: NL

Re: Incredible SQLite speed

Post by Rinzwind »

journal_mode
"The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt. Without a journal, there is no way for a statement to unwind partially completed operations following a constraint error. This might also leave the database in a corrupted state. For example, if a duplicate entry causes a CREATE UNIQUE INDEX statement to fail half-way through, it will leave behind a partially created, and hence corrupt, index."

synchronous
"OFF (0)
With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF."
Post Reply