Does SQLite perform disk-activity when calling SELECT statements with AutoCommit turned off in perl?

user_3503851 0
Viewed 480

I created a new SQLite database in disk file with AutoCommit turned off using:

my $dsn      = "dbi:SQLite:dbname=folder/path/file.db";
my $user     = "";
my $password = "";
my $dbh = DBI->connect($dsn, $user, $password,{AutoCommit => 0});

#… Doing some processing on the database (Creating tables/Inserting rows/Updating fields)

#… Many query SELECT statements here
    # My question here: Does SQLite read data from memory or disk each time a SELECT statement is performed.

When querying data (using SELECT SQL statements), does SQLite read data from disk file or memory? Does SQLite perform any disk activity (which is lower in performance than memory RAM activity)?

Side-Notes:

The answer of this question will help guide me to choose whether to load DB from disk file to memory first, then process and query data from it, and at end save it back to disk file after finishing, or the other option to use simply the solution of turning off the AutoCommit.

Note: My created database won't get too large, so I don't worry about the issue of getting my database filling the memory.

If SQLite reads data from disk each time a SELECT query statement is called, then this will cause a tremendous performance lag compared to copying DB to memory solution mentioned in my previous question.

Helpful Answer Approaching:
• Performance testing by Schwern (mentioned here) shows that operating and querying on whether an in-memory or in-disk database results the same performance.

perl sqlite
3 Answers

In general, yes, if your database is on disk, then the disk will be accessed to carry out any SELECT unless the necessary data is buffered in memory somewhere (most likely either within SQLite itself or in a disk cache).

But note that phrase, "if your database is on disk". SQLite also supports in-memory databases. If you have enough spare RAM to hold the database, and if you're going to do enough operations on it to make up for the cost of copying it into memory in the first place, this may be worth looking into.

Instead of the setup in the question, you can:

my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');
$dbh->sqlite_backup_from_file('folder/path/file.db');

You will then have an in-memory database which is a copy of your original on-disk database, which you can SELECT against to your heart's content without having to touch the disk (unless it's too big and some of that memory gets swapped out).

If you make any changes to the in-memory database, you'll also need to

$dbh->sqlite_backup_to_file('folder/path/file.db');

before $dbh disconnects if you want to save those changes back to disk.

And keep in mind that this is only safe if no other process might be making changes to the on-disk copy at the same time as you're changing the in-memory copy, since neither one will know about changes made to the other.

Nomad_Dervish_189140

Generally speaking, yes.

SQLite does not load the whole database into memory on connect. Indeed, the database may be much larger than memory -- SQLite databases have a maximum size of roughly 140 TB. SQLite will cache some recently used data in memory, but most queries on a large database will hit the disk (or the OS disk cache).

Admin1

If I understand correctly, you're trying to choose between backup_from_file or reading directly from disk storage. No matter which path you choose, you'll be reading the data from disk. Provided you've got the memory to spare, the read cache associated with a direct read should stick around for any reasonable duration. If your queries use indexes, you may even avoid reading certain blocks entirely for a small bonus.

The downside of this question is that it reeks of premature optimization. Benchmark your approaches and see which one wins in your particular application.

user_18010860
Related