Thanks guys, I have already tried WAL and it does speed things up, but I still need the daily processing to be done asynchronously. The computations are complex, involving around 16 inputs from the player table and resulting in 8 outputs. This is core to the game and not a simple score leaderboard. So my options appear to be:
1. Hope that someone addresses my other thread about async I/O not working for me 2. Create my own mechanism for asynchronous DB transactions 3. Deal with writes that fail due to BUSY - just handle the critical ones, drop the others 4. Experiment with using a separate DB for the leaderboard results, since a separate table won't work 5. Switch to NoSQL 6. Look at the feasibility of holding results in memory and incrementally injecting them into the player table 7. Some other ideas? Cheers Tom On Fri, Jul 25, 2014 at 7:13 AM, Simon Slavin-3 [via SQLite] < ml-node+s1065341n76956...@n5.nabble.com> wrote: > > On 24 Jul 2014, at 3:21pm, Tom <[hidden email] > <http://user/SendEmail.jtp?type=node&node=76956&i=0>> wrote: > > > My application presently does all the DB operations on the main thread. > But > > once a day I need it do the player leaderboards, which could be 500K > players > > or so, hence several minutes. I can't have the main thread blocking for > any > > significant length of time - a second or so at most. Hence my desire to > do > > that one long operation in a separate thread and not have other DB > writes > > block. > > Even 500K players shouldn't be taking SQLite "a few minutes" unless there > are no good indexes to use. Are the scores saved in your player table ? > Do you have indexes suitable for the commands you are executing ? > > > I don't suppose temporary tables would make any difference? I.e. use a > > temporary table for the LB results. Would that allow concurrent writes? > (to > > temporary table, and to normal table, from different threads). > > It still won't do what you want. Dr Hipp's suggestion of WAL mode is > probably your best bed. With this you can have many readers and one writer > all working at the same time. > > > What about using 2 databases? > > It would probably be better to try figuring out good SELECTs and indexes > first. > > Simon. > _______________________________________________ > sqlite-users mailing list > [hidden email] <http://user/SendEmail.jtp?type=node&node=76956&i=1> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > > http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76956.html > To unsubscribe from Does SQLite lock the entire DB for writes, or lock by > table?, click here > <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=76921&code=dG9tQG1lYW5mb3guY29tfDc2OTIxfDg0MjQ3NTc4OQ==> > . > NAML > <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76961.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
