Hi all,
I am using sqlite3 with python3
When I insert each row by row to different tables and then select all columns,
It is really slow.
If I insert all rows at once then everything goes fine.
One thing strange is if I add rows to one table after finishing adding rows to the other table, everything goes fine.
So, the slow down only occurs when I add rows to different tables within one transaction.
One solution is executing 'vacuum' but it takes too much time for my original dataset which takes too much long time for vacuum operation
Here are the sample codes below
```
import sqlite3
import os
import time
os.system('rm -f *.db')
conn1 = sqlite3.connect('db1.db', timeout=1000.0)
conn2 = sqlite3.connect('db2.db', timeout=1000.0)
conn3 = sqlite3.connect('db3.db', timeout=1000.0)
cursor1 = conn1.cursor()
cursor2 = conn2.cursor()
cursor3 = conn3.cursor()
v = ''.join(['a' for _ in range(10000)])
rows = [(str(i),v) for i in range(1000)]
cursor1.execute('CREATE TABLE if not exists Test1(ID text, value text)')
cursor1.execute('CREATE TABLE if not exists Test2(ID text, value text)')
cursor1.execute('CREATE TABLE if not exists Test3(ID text, value text)')
cursor2.execute('CREATE TABLE if not exists Test1(ID text, value text)')
cursor2.execute('CREATE TABLE if not exists Test2(ID text, value text)')
cursor2.execute('CREATE TABLE if not exists Test3(ID text, value text)')
cursor3.execute('CREATE TABLE if not exists Test1(ID text, value text)')
cursor3.execute('CREATE TABLE if not exists Test2(ID text, value text)')
cursor3.execute('CREATE TABLE if not exists Test3(ID text, value text)')
#################
n = 1
for i in range(len(rows)//n+1):
cursor1.executemany('INSERT INTO Test1 VALUES(?,?);', rows[i*n:(i+1)*n])
cursor1.executemany('INSERT INTO Test2 VALUES(?,?);', rows[i*n:(i+1)*n])
cursor1.executemany('INSERT INTO Test3 VALUES(?,?);', rows[i*n:(i+1)*n])
conn1.commit()
#################
for i in range(len(rows)//n+1):
cursor2.executemany('INSERT INTO Test1 VALUES(?,?);', rows[i*n:(i+1)*n])
for i in range(len(rows)//n+1):
cursor2.executemany('INSERT INTO Test2 VALUES(?,?);', rows[i*n:(i+1)*n])
for i in range(len(rows)//n+1):
cursor2.executemany('INSERT INTO Test3 VALUES(?,?);', rows[i*n:(i+1)*n])
conn2.commit()
#################
cursor3.executemany('INSERT INTO Test1 VALUES(?,?);', rows)
cursor3.executemany('INSERT INTO Test2 VALUES(?,?);', rows)
cursor3.executemany('INSERT INTO Test3 VALUES(?,?);', rows)
conn3.commit()
#cursor1.execute('VACUUM;')
#conn1.commit()
#cursor2.execute('VACUUM;')
#conn2.commit()
#cursor3.execute('VACUUM;')
#conn3.commit()
t1 = time.time()
cursor1.execute('SELECT * FROM Test1')
rows = cursor1.fetchall()
t2 = time.time()
print (t2-t1, len(rows))
t1 = time.time()
cursor2.execute('SELECT * FROM Test1')
rows = cursor2.fetchall()
t2 = time.time()
print (t2-t1, len(rows))
t1 = time.time()
cursor3.execute('SELECT * FROM Test1')
rows = cursor3.fetchall()
t2 = time.time()
print (t2-t1, len(rows))
```
output
```
5.287451267242432 1000
0.15462756156921387 1000
0.1925675868988037 1000
```