1. The latest SQLite improved REPLACE operation, It's alot faster now. 2.use SAVEPOINT to batch transactions. They'll save many IO.
发自我的 Mobile Phone ________________________________ 发件人: Vince Scafaria<mailto:[email protected]> 发送时间: 2016/12/13 9:24 收件人: [email protected]<mailto:[email protected]> 主题: [sqlite] Disk I/O utilization Our application does a lot of SQLite read/write in a background process. On some environments this hammers the disk I/O (e.g. Task Manager -> Disk). Do you have suggestions on how we might minimize this? Perhaps related, one operation that seems to use a lot of the Disk I/O is when we use INSERT OR REPLACE INTO. We use this as a workaround for performing an UPDATE together with JOIN, which is not allowed by SQLite. I feel as though one way we could minimize disk usage would be if we could use the MSSQL syntax of: UPDATE x SET ValueA = y.ValueA FROM TableOne x JOIN TableTwo y ON y.Id = x.TableTwoId WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,''); Instead we do: INSERT OR REPLACE INTO TableOne ( Id, TableTwoId, ValueA ) SELECT x.Id, x.TableTwoId, y.ValueA FROM TableOne x JOIN TableTwo y ON y.Id = x.TableTwoId WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,''); Our tables all have a primary key on Id. By including Id in the statement, it does a REPLACE. As I understand it, replace includes a behind-the-scenes DELETE which triggers a re-indexing. Maybe that is part of the problem, but I don't see a workaround. Ideas? Thank you! Vince _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
