SQLite User Forum

sqlite >=3.44 - create table is 1000x slower when table name begins with a number
Login
Chronological

sqlite >=3.44 - create table is 1000x slower when table name begins with a number

(1) By mlpranav on 2024-05-02 06:37:57 [source]

Create table is taking 1000-35000x more time when the table name begins with a number.

Code to generate test database

import sqlite3

conn = sqlite3.connect('test1.db')

for i in range(1000):
    print(f"TAB {i}")
    conn.execute(f'CREATE TABLE tab{i} ("value" INT)')
    for j in range(100_000):
        conn.execute(f"INSERT INTO tab{i} (value) VALUES ({j})")

conn.commit()
conn.close()

Timing script

sqlite3 is v3.43.2 and sqlite_new is an alias for v3.45.3.

time sqlite3 test1.db 'CREATE TABLE "012" ("time" TEXT);'
time sqlite3 test1.db 'CREATE TABLE "a012" ("time" TEXT);'

time /opt/homebrew/opt/sqlite/bin/sqlite3 test1.db 'CREATE TABLE "111" ("time" TEXT);'
time /opt/homebrew/opt/sqlite/bin/sqlite3 test1.db 'CREATE TABLE "a111" ("time" TEXT);'

v3.43.2 takes around 20ms for both the tables. v3.45.3 takes around 20ms for "a111" and >10s for "111".

Tested on Arch Linux and macOS.

The delay is even more prominent on larger databases.

(2) By Stephan Beal (stephan) on 2024-05-02 07:44:45 in reply to 1 [link] [source]

Create table is taking 1000-35000x more time when the table name begins with a number.

i'm unable to reproduce any drastic difference on a mid-range intel running Linux Mint (extra spacing added for legibility):

$ sqlite3
SQLite version 3.44.0 2023-10-02 15:37:55
...
sqlite> .timer on

sqlite> CREATE TABLE "012" ("time" TEXT);
Run Time: real 0.000 user 0.000000 sys 0.000219

sqlite> CREATE TABLE "a012" ("time" TEXT);
Run Time: real 0.000 user 0.000000 sys 0.000172

sqlite> CREATE TABLE "013" ("time" TEXT);
Run Time: real 0.000 user 0.000000 sys 0.000118

sqlite> CREATE TABLE "a013" ("time" TEXT);
Run Time: real 0.000 user 0.000000 sys 0.000099

There is an inexplicable slight difference there, but nowhere near 1000x and nowhere near enough samples to be sure of any pattern involving the name.

Same thing with an on-disk db:

$ sqlite3 foo.db
SQLite version 3.44.0 2023-10-02 15:37:55
...
sqlite> .timer on

sqlite> CREATE TABLE "a013" ("time" TEXT);
Run Time: real 0.009 user 0.000690 sys 0.000000

sqlite> CREATE TABLE "013" ("time" TEXT);
Run Time: real 0.009 user 0.000507 sys 0.000000

sqlite> CREATE TABLE "012" ("time" TEXT);
Run Time: real 0.009 user 0.000659 sys 0.000000

sqlite> CREATE TABLE "a012" ("time" TEXT);
Run Time: real 0.009 user 0.000557 sys 0.000000

Similar results are showing up in the trunk version:

$ ./sqlite3 
SQLite version 3.46.0 2024-05-01 16:25:46
...
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on

sqlite> CREATE TABLE "012" ("time" TEXT);
Run Time: real 0.001 user 0.000179 sys 0.000000

sqlite> CREATE TABLE "a012" ("time" TEXT);
Run Time: real 0.001 user 0.000154 sys 0.000000

These results do differ for me on larger databases, noting that the largest i have handy is only 400mb, but they are still well within comfortable ranges:

sqlite> CREATE TABLE "012" ("time" TEXT);
Run Time: real 0.009 user 0.000000 sys 0.001068

sqlite> CREATE TABLE "a012" ("time" TEXT);
Run Time: real 0.013 user 0.000000 sys 0.000559

(3) By mlpranav on 2024-05-02 08:35:15 in reply to 2 [link] [source]

Have you reproduced the same database using the Python script? The delay occurs only on sufficiently large databases.

Please do so and also try v3.45.

(4) By mlpranav on 2024-05-02 08:38:50 in reply to 2 [link] [source]

https://github.com/python/cpython/issues/118448

Kindly also take a look at this thread, multiple users were able to reproduce this delay.

This started as a Python issue but we were able to isolate it to SQLite itself.

It seems like this delay is platform specific and it is the most prominent on macOS. I am unable to reproduce it on Windows, but it's there on macOS and Arch Linux.

(5.1) By Stephan Beal (stephan) on 2024-05-02 10:53:02 edited from 5.0 in reply to 4 [link] [source]

Kindly also take a look at this thread, multiple users were able to reproduce this delay.

Using the 1gb db from your python script, i cannot reproduce this delay using 3.44.0 on Linux Mint 21.3:

$ l test.db
-rw-r--r-- 1 stephan stephan 1060950016 May  2 11:29 test.db

$ time sqlite3 test.db 'CREATE TABLE "a111" ("time" TEXT);'

real	0m0.012s
user	0m0.004s
sys	0m0.000s

$ time sqlite3 test.db 'CREATE TABLE "111" ("time" TEXT);'

real	0m0.012s
user	0m0.004s
sys	0m0.000s

But can reproduce it using the trunk:

$ time ~/f/s/lite/sqlite3 test.db 'CREATE TABLE "a200" ("time" TEXT);'

real	0m0.013s
user	0m0.000s
sys	0m0.004s

$ time ~/f/s/lite/sqlite3 test.db 'CREATE TABLE "200" ("time" TEXT);'

real	0m7.767s
user	0m6.839s
sys	0m0.511s

$ time ~/f/s/lite/sqlite3 test.db 'CREATE TABLE "a201" ("time" TEXT);'

real	0m0.013s
user	0m0.000s
sys	0m0.004s

$ time ~/f/s/lite/sqlite3 test.db 'CREATE TABLE "201" ("time" TEXT);'

real	0m7.263s
user	0m6.698s
sys	0m0.540s

We'll look into this.

(6) By Richard Hipp (drh) on 2024-05-02 12:15:19 in reply to 1 [link] [source]

Issue should now be resolved on trunk. Please recompile your SQLite library using the latest sources and try again.

(7) By mlpranav on 2024-05-02 14:01:44 in reply to 6 [link] [source]

Yes, it is resolved now. Thanks for the fix.

(8) By L Carl (lcarlp) on 2024-05-03 17:10:33 in reply to 6 [link] [source]

I’m totally unfamiliar with the SQLite code but this bug really piqued my curiosity. If anyone has time to summarize the cause, I’d love to read it. Very low priority.

(9) By Stephan Beal (stephan) on 2024-05-03 18:34:19 in reply to 8 [link] [source]

If anyone has time to summarize the cause, I’d love to read it.

A change made last October added pragma integrity_check($TABLENAME) for newly-created tables. When the table name looks like a number, however, it was being interpreted as such, which causes integrity_check() to run across all tables instead of just the intended one. Richard's fix yesterday removes that check, as its results were being ignored anyway, and ensures that integrity_check() will now treat string-type arguments as strings even if they look like numbers.

(10) By L Carl (lcarlp) on 2024-05-04 18:25:04 in reply to 9 [link] [source]

Makes sense, but why was it so much worse on macOS?

(11) By Stephan Beal (stephan) on 2024-05-04 18:50:35 in reply to 10 [link] [source]

but why was it so much worse on macOS?

No clue. Nothing about the change is platform-specific.

(12) By mlpranav on 2024-05-04 21:59:41 in reply to 10 [link] [source]

I guess it wasn't.

The systems I used for testing were using different versions of SQLite, so this issue was not present on Windows.

This initially started as a Python issue, and Python/pandas versions were same on both systems which made me think that this is a macOS specific issue.

(13) By L Carl (lcarlp) on 2024-05-05 15:31:33 in reply to 12 [link] [source]

Thanks. I was just curious because it sounded interesting. I’ve certainly jumped to the wrong conclusion about bugs many times.