0

I have a large database and a file with ID-Name pairs in each line, where ID corresponds to the primary key of the table in the database. I need to write all the names from the file into the database using corresponding IDs. Of course, if there is such ID in the table (do not insert a new record if there is no such user with this ID, just UPDATE existing one).

I've written a simple python script for this purpose. I realized that it worked extremely slow. Much slower than it has to be for UPDATE (not even INSERT). ID is a primary key, so it must be indexed automatically and it should not slow down UPDATEs, right?

Then I started experimenting with the script and found that UPDATEs are much faster when IDs are a constant or incrementing for example. The script for experiments are:

import sqlite3
import traceback
import time
import random

conn = sqlite3.connect('database.sqlite')

c = conn.cursor()
c.execute('BEGIN')

PERIOD_SEC = 3

prev_tp = 0
counter = 0

try:
    while True:
        tp = time.time()
        elapsed_sec = tp - prev_tp
        if elapsed_sec > PERIOD_SEC:
            prev_tp = tp

            speed = counter / elapsed_sec

            print('Speed: %.02f/sec' % (speed))
            counter = 0

        c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555))
        counter += 1


    c.execute('COMMIT')
except Exception:
    c.execute('ROLLBACK')
    traceback.print_exc()

The value of username doesn't affect on performance, so let it be always random. In the example above the ID is constant and equals to 555 (the value doesn't matter, the speed is the same also for big values of ID). For this example the speed is (UPDATEs per second):

Speed: 376665.88/sec
Speed: 404738.08/sec
Speed: 404942.04/sec
Speed: 403681.24/sec

If I replace

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555))

with

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', inc))

where inc is increased by 1 after every UPDATE, then the results would be:

Speed: 233977.17/sec
Speed: 229630.93/sec
Speed: 223424.88/sec
Speed: 218353.56/sec

It's worse, but it's still not so bad.

But.. If I use a random ID

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', random.randint(0, 1000000000)))

then the results become disappointing:

Speed: 514.18/sec
Speed: 732.49/sec
Speed: 886.28/sec
Speed: 999.34/sec

More than 100 times slower! I also noticed the bigger range of IDs I choose the slower it becomes, but IDs from that range are always exist in the table.

So my questions are:

  1. Why this happens? I think it may be somehow related to caching, but I don't know if it's normal. I mean in real sutiations IDs are near to random and as I know the performance is not as bad as mine.
  2. How to improve performance? Especially, if I have a non-sorted list of ID-Name pairs. Are there other ways besides sorting it and only then writing to the database?

Thanks in advance!

4
  • Have you tried importing your file to a temporary table and do a mass update from there? Jun 26, 2022 at 19:37
  • Is your table created as without rowID, that is preferred, since you have an integer PK column as "ID". Can you try with prepared statement, that will be efficient. how large is your input file? what is your disk type - is it SSD or HDD? SSD will be faster. Jun 27, 2022 at 5:58
  • @AnandSowmithiran, the table created NOT as without rowID. Do you think this may be the reason? I tried using prepared statements (executemany in python). Actually, it may be 4 times faster, but with random IDs it's still very slow (average 2500 UPDATEs per second(not executemany calls)) The file size is 8 GB. Disk type is SSD.
    – g00dds
    Jun 27, 2022 at 9:29
  • Can you try by creating the table without RowID, and check the performance? Jun 29, 2022 at 18:38

1 Answer 1

1

Rows are stored in pages within the database and sqlite will always read or write whole pages. Default size of page is 4096 bytes, so if each row is 40 bytes of data then a page can contain up to 100 rows.

Also, if ID is an integer primary key, then ID is used as the rowid. This means that the table is stored in pages on disk in ID order and IDs of similar values are stored in the same page.

Why this happens? I think it may be somehow related to caching, but I don't know if it's normal. I mean in real sutiations IDs are near to random and as I know the performance is not as bad as mine.

Even if they are enclosed in a single transaction, every UPDATE is executed singularly. This means that for every UPDATE sqlite has to find the page that contains the row with the specified ID, read it, modify it and then write it to disk. If you update repeatedly the same row, the page is already cached in memory and it will not be flushed to disk until the end of transaction, so there is no need to read or write anything to disk.

If you update the table in increasing ID order, since a single page con contain multiple rows, you are accessing the same page for as much as 100 updates (in the example of 100 rows per page), then every 100 updates you switch to another page and modify it for another number of updates. Even if you updated all the rows of the table, every page would be read and written to disk only once.

If you update the table in random ID order, every update will most probably need to update a row stored on a different page than the previous one, so sqlite needs to read and write a different page every time. This means that if you update the whole table, you could need to read all pages of the table 100 times.

How to improve performance? Especially, if I have a non-sorted list of ID-Name pairs. Are there other ways besides sorting it and only then writing to the database?

You could import your data from your file and insert it in a temporary table. Temporary tables can be stored in memory for maximum performance and the import can be done sequentially without having to check if the record exists or is a new one. Then, you can use a single query to update the main table:

PRAGMA temp_store = MEMORY;    
CREATE TEMPORARY TABLE myfile (ID INTEGER PRIMARY KEY, Name);

... import your file in table myfile

INSERT INTO users (ID, username) 
SELECT ID, Name from myfile WHERE true  -- WHERE true needed to avoid confusion with following ON
ON CONFLICT (ID) DO UPDATE SET username = excluded.username;

The INSERT will merge myfile into users with a single command, so sqlite can optimize the operation. The ON CONFLICT clause of the INSERT will update the row, instead of adding a new one, when there is a duplicate key conflict on ID (more info here: http://www.sqlite.org/draft/lang_upsert.html)

Also note that if the users table has the same columns as myfile (there are no other colums that you want to preserve) the INSERT query can be also be written like this:

INSERT OR REPLACE INTO users (ID, username) 
SELECT ID, Name from myfile

The difference is that if ID already exists in table users, that row will be deleted and the new one will be inserted (possibly deleting other columns non present in myfile).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.