Why operations with in-memory DB are much faster than with on-disk DB?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Why operations with in-memory DB are much faster than with on-disk DB?

Yuri
I noticed that my DB import process is much slower when run on the DB on
disk, vs. in memory. It reads files and runs a massive amount of
inserts/updates.


Why is this? Is there any way to speed it with disk without using
in-memory DB?


Yuri


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why operations with in-memory DB are much faster than with on-disk DB?

Simon Slavin-3
On 19 Oct 2018, at 5:55am, Yuri <[hidden email]> wrote:

> I noticed that my DB import process is much slower when run on the DB on disk, vs. in memory. It reads files and runs a massive amount of inserts/updates.

Memory access is much faster than disk access.  If you're using actual spinning hard disks, your computer has to wait for the disk to be spun to the right place before it can read or write data.  That could take 1/5000th of a second.  By contrast, memory access is almost instant.

> Why is this? Is there any way to speed it with disk without using in-memory DB?

If you are importing into an empty database and you don't care about corruption due to power-failure, it would be safe to do

    PRAGMA synchronous = OFF;
    PRAGMA journal_mode = OFF;

before the import.  If you do this, make sure you close the file after the import.  When it is reopened these settings will have been reset to the default.  For an explanation of what these do, see

<https://sqlite.org/pragma.html>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why operations with in-memory DB are much faster than with on-disk DB?

Clemens Ladisch
In reply to this post by Yuri
Yuri wrote:
> I noticed that my DB import process is much slower when run on the DB on disk, vs. in memory.

It's possible that you forgot to wrap a single transaction around all changes.
Otherwise, you get an automatic transaction for every command, which requires
disk synchronization every time.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users