Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

Hello, Thanks for your advice! It works pretty well when I dropped the rowid and set the other field as primary key. I also enable wal mode at the cost of a little bit read performance. Overall, it works really well now. Really appreciate your help. -- Sent from: http://sqlite.1065341.n5.nabble

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

Thanks for the advice! I could never thought about this. Really learnt a lot. It's a customized Android phone type device. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://ma

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

> I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS = 15;". Not zipvfs specific, but that kind of update can be quite inefficient if the record is large (in terms of bytes, or records per database page) as your table declaration hints to. This will be especially prominen

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

On 9 Sep 2017, at 9:11pm, Teg wrote: > Is it a phone or tablet? I'd wonder if the CPU is going into thermal > throttle mode. As I understand it most phones and tablets can't keep > the CPU's running full speed without eventually throttling the CPU. I would never have thought of that unless

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

Hello Yue, Is it a phone or tablet? I'd wonder if the CPU is going into thermal throttle mode. As I understand it most phones and tablets can't keep the CPU's running full speed without eventually throttling the CPU. To me the fact uncompressed, while moving much more data, is more than 2 tim

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

Thanks for the advice. I'll try them now. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

Are your updates sorted by DS? If your queries are sorted then sequential queries are more likely to hit the same db pages while searching the index, resulting in higher cache usage and fewer decompression operations. This would have less benefit if your 100k DS values of the updates are randoml

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

Yue Wu wrote: > Hello, > As mentioned in the subject, our goal is to improve performance regarding > to batch sql updates. > The update sql as follow, > >> UPDATE ITEM SET FIELD4 =? WHERE DS=? > > We run 100,000 updates in a single transaction. The zipvfs version takes > about 20 min while uncomp