Python/SQLite updating columns

Question

I am trying to update a couple of columns in my SQLite database using:

UPDATE db SET Col1 = 0 WHERE Col1 IS NULL;
UPDATE db SET Col2 = 0 WHERE Col2 IS NULL;

However this seems to take a very long time to even update one column. I have resorted to using:

CASE WHEN Col1 IS NULL THEN 0 ELSE Col1 END

in my SELECT queries which works a lot quicker, however is there a reason why the UPDATE method is so slow? (I only have 670K rows in my database)

NOTE: My computer is fairly high-end and when the UPDATE is running, there doesn't seem to be much pressure on my desktops resources.

How long does the update take? Jon's answer is qualitatively correct, but if you have only ~600 rows I would expect a simple update like this to be faster than a person could perceive. There could be something else going on. — Chris Johnson
– Chris Johnson, Commented May 10, 2014 at 11:05

Jon Clements · Accepted Answer · 2014-05-10 10:41:47Z

2

UPDATEing requires starting a transaction, then keeping a write-ahead-log of the changes that the update would make, then ensuring that succeeds before then writing changes back to the main table, and then ensuring all of that completes successfully...

SELECTing is reading then doing a simple "in memory if" to swap the values over for each row... so it's bound to be faster (plus disk reads are almost always a lot faster than disk writes (which a SELECT really doesn't require))...

answered May 10, 2014 at 10:41

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Clements Over a year ago

@Blackholify on a side note, simpler than CASE WHEN ... is to use COALESCE(col1, 0)

Tony Suffolk 66 · Accepted Answer · 2014-05-10 11:27:39Z

0

Do you have the tools available to do an "explain" on your query :to see what (if any) indexes your sql query is using. if your sql query is resulting in a full table scan over 670K rows, then it could be slowish.

if you are using ubuntu or a similiar based linux distribution, Sqliteman will do this for you.

answered May 10, 2014 at 11:27

Tony Suffolk 66

9,7723 gold badges32 silver badges37 bronze badges

1 Comment

Black Over a year ago

Yes I do but I've gone with the CASE WHEN method as there's probably no point in UPDATEing those values in my application.

Collectives™ on Stack Overflow

Python/SQLite updating columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related