使用事務提升sqlite insert的效能

標籤：ios   sqlite   事務   insert performance   插入慢   

昨天發現sqlite插入效能很低，搜尋了一下發現，其實sqlite的插入可以做到每秒50000條，但是處理事務的速度慢：

(19) INSERT is really slow - I can only do few dozen INSERTs per second

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

sqlite FAQ#19

我原本的代碼沒有使用事務，所以每條insert語句都預設為一個事務。解決的辦法是加上事務，執行SQL的時間就從10秒縮短到了0.07秒

發現了這個以後，我就嘗試把可能的地方都加上事務，但是原本程式有一處邏輯，是執行一大堆insert，如果主鍵衝突就自然無視。但是如果把這堆sql變成事務，就會影響正確資料的插入，所以又把insert語句改成insert or ignore：

insert or ignore into test (id, key) values (20001, 'kyfxbl');

然後再放到一個事務裡，效率大大提升