CODEWITHSUNDEEP
pythonsqlite
user3643087
user3643087

Reputation: 35

Make sqlite3 column update faster

I have written a Python code that iteratively reads data from a sqlite database, does some calculations on it and writes the results back to the database as new columns and so on. However, the part involving writing to the database by updating a column feels to get slower and slower by time. Is there any alternative way that I can do the same thing but faster?

A relevant part of my code is brought below. In this section of the code, two new columns column_I1_ and column_I2_ are made, each ending by the iteration number, like column_I1_1, column_I2_1, and then the new results is written into these columns from corresponding lists. The problem is, considering the high number of iterations and the time-consuming nature of calculations, plus high number of rows (here defined by the num value, approximately 11000 rows), this works too slow...

Is there another way of doing this instead of using update? because I think for updating, it has to copy that column each time it adds a new value and this causes the slow down. I used to use Excel for this and was much faster but since there is a 256 column limit for spreadsheet I had to switch to Database.

cur.execute("alter table C add column_I1_%d integer"%iter) #makes a new column indexed by iteration number
con.commit()
for e in range(0,num): # num is a given number as input
    cur.execute("UPDATE C SET column_I1_%d=? WHERE Id=%d"%(iter,e+1),(w[e],)) # w is a list containing some results
con.commit()
    #
cur.execute("alter table C add column_I2_%d integer"%iter)  
con.commit()
for f in range(num,(2*num)):         
    cur.execute("UPDATE C SET column_I2_%d=? WHERE Id=%d"%(iter,f-num+1),(w[f],))
con.commit()

.....

Your comments are much appreciated. Plus, I am relatively a Python newbie, so please go easy on me! :)

Upvotes: 0

Views: 1128

Answers (2)

CL.
CL.

Reputation: 180310

To speed up lookups on the Id column, create an index on it:

cur.execute("CREATE INDEX MyLittleIndex ON C(Id)");

Alternatively, if the values in the Id column are unique, declare this column as the primary key (which automatically creates an index):

cur.execute("CREATE TABLE C(Id PRIMARY KEY)");

If the values are integers, declaring this column as INTEGER PRIMARY KEY will be a little more efficient:

cur.execute("CREATE TABLE C(Id INTEGER PRIMARY KEY)");

Upvotes: 2

fwu
fwu

Reputation: 369

You may consider to call executemany for one time instead of calling execute in a loop.

For example, consider the code below,

for e in range(0,num): # num is a given number as input
    cur.execute("UPDATE C SET column_I1_%d=? WHERE Id=%d"%(iter,e+1),(w[e],))

It can be transformed to following,

cur.executemany("UPDATE C SET column_I1_%d=? WHERE Id=?"%(iter), zip(w, range(num)))

Upvotes: 0

Related Questions