[sqlite] random rows
I need to get some random rows from a large(ish) table. The following seems to be the most straight forward - but not the fastest. SELECT * FROM table ORDER BY random() limit 200 Is there a faster/better approach? cheers, Torsten ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: database locked on select
> Do the select and updates run inside a explicit transaction or they > run in individual implicit transactions? > implicit - does that make a big difference in this case? If you really want a single query you could write something like: > > WITH data(id, c1, c2 /*, ... */) AS (VALUES > (123, 'abc', 'xyz' /*, ... */), > (456, 'xxx', 'yyy' /*, ... */), > (789, 'xyz', 'xyz' /*, ... */) > /* ... */ > ) UPDATE tab > SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id = > tab.id) > WHERE id IN (SELECT id FROM data); > > But for that again means all the data (or the single query) needs to be built up in memory. cheers, Torsten ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: database locked on select
Yes, manually buffering the resultset or buffering the updates is of course a possible workaround. But I would like to avoid that as much as possible. Another approach is to use limit/offset and then page through the resultset to control the amount of buffering needed. But this just feels like a lot of complexity for such simple task. Anyway - since WAL mode seems to work I will stick with that for now. cheers, Torsten On Mon, May 28, 2018 at 9:10 PM Simon Slavin wrote: > On 28 May 2018, at 7:56pm, Torsten Curdt wrote: > > > Just to clarify: I have a single thread - so intermixing the stepping > > through a resultset and doing an update requires WAL mode but should be > > fine. Correct? > > Yes, this should work fine. Obviously, one thread is not going to be > trying to do two database accesses at the same time, especially since your > software design uses the result from one SELECT row in order to figure out > what UPDATE to issue. > > You could, course, build up an array of pairs in memory while doing the > SELECT, then consult the array to create all the UPDATE commands once you > have finalized the SELECT. If you do do this don't forget to surround the > UPDATE commands with BEGIN;...COMMIT; . > > One technique I have used in a similar situation was to write all my > UPDATE commands to a long text buffer. This was in an unusual situation > where I needed to get the SELECT done as quickly as possible to avoid > locking up lab equipment. So the software figured out all the UPDATE > commands and concatenated them in a text variable: > > UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;... > > The buffer could get as big as half a megabyte or so. Then I > sqlite3_reset() the SELECT command. Then I submit the entire piece of text > as one long parameter to sqlite3_exec(). Worked perfectly, very quickly, > and didn't take up much more memory than storing the parameters in an array. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: database locked on select
Just to clarify: I have a single thread - so intermixing the stepping through a resultset and doing an update requires WAL mode but should be fine. Correct? On Mon, May 28, 2018 at 11:01 AM Hick Gunter wrote: > As long as you are and remain in serialized mode, you can re-use the same > connection from different threads. > > If you can guarantee that each thread will have it's own, personal, > connection, you may achieve a performance increase by using multithread > mode. > > In single thread mode, all the checks are turned off and it is up to you > to make sure that exactly 1 thread does all the calls to SQLite. This mode > is fastest. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Torsten Curdt > Gesendet: Montag, 28. Mai 2018 10:38 > An: sqlite-users@mailinglists.sqlite.org > Betreff: Re: [sqlite] [EXTERNAL] Re: database locked on select > > Thanks for the suggestion but I feel that will just make things more > complex. > Right now I have one function that does it work and then decides whether > to update or not. > That way I would have to split that one function into two (new_data, > needs_update) > which is not so easy. > Plus that also makes things less portable (not this really is a > requirement though). > > WAL mode seems to solve this for me much better. At least I don't see any > drawbacks yet. > I am just wondering if having both statements on the same connection is a > valid way of using this. > > cheers, > Torsten > > On Mon, May 28, 2018 at 10:25 AM Hick Gunter wrote: > > > It is possible to bring an external resource into SQlite by writing > > user-defined functions and/or virtual tables. This would allow > > something > > like: > > > > UPDATE set () = new_data() where > > needs_update(); > > > > With the UDF returning 1 (TRUE) if the current row (identified by the > > arguments) needs an update and the row-valued function new_data() > > returns the new values. > > > > > > -Ursprüngliche Nachricht- > > Von: sqlite-users > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > Im Auftrag von Torsten Curdt > > Gesendet: Montag, 28. Mai 2018 10:04 > > An: sqlite-users@mailinglists.sqlite.org > > Betreff: [EXTERNAL] Re: [sqlite] database locked on select > > > > I have to query an external resource for each row. > > Unfortunately nothing I can do in a single update query. > > Would mean keeping a lot of data manually in memory. > > > > On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży < > > abrozynieprzelozy314...@gmail.com> wrote: > > > > > BTW why not to update all rows by single update query? > > > > > > 2018-05-27 20:30 GMT+02:00, Torsten Curdt : > > > > I am doing a select, then iterate through the resultset and on > > > > each row call update on that row. > > > > I am using the golang driver and ran into the issue that on the > > > > update > > > the > > > > database is still locked from the select. > > > > > > > > https://github.com/mattn/go-sqlite3/issues/569 > > > > > > > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked > > > > and > > > IIUC > > > > these types of updates should be possible since version 3.3.7 > > > > though > > > > - > > > and > > > > I am using 3.19.3. > > > > > > > > Any suggestion on how to track down why the updates fail? > > > > > > > > cheers, > > > > Torsten > > > > ___ > > > > sqlite-users mailing list > > > > sqlite-users@mailinglists.sqlite.org > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use > > > > rs > > > > > > > ___ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > ___ > > Gunter Hick | Software Engineer | Scientific Games International GmbH > > | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 > > | (O)
Re: [sqlite] [EXTERNAL] Re: database locked on select
Thanks for the suggestion but I feel that will just make things more complex. Right now I have one function that does it work and then decides whether to update or not. That way I would have to split that one function into two (new_data, needs_update) which is not so easy. Plus that also makes things less portable (not this really is a requirement though). WAL mode seems to solve this for me much better. At least I don't see any drawbacks yet. I am just wondering if having both statements on the same connection is a valid way of using this. cheers, Torsten On Mon, May 28, 2018 at 10:25 AM Hick Gunter wrote: > It is possible to bring an external resource into SQlite by writing > user-defined functions and/or virtual tables. This would allow something > like: > > UPDATE set () = new_data() where > needs_update(); > > With the UDF returning 1 (TRUE) if the current row (identified by the > arguments) needs an update and the row-valued function new_data() returns > the new values. > > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Torsten Curdt > Gesendet: Montag, 28. Mai 2018 10:04 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] Re: [sqlite] database locked on select > > I have to query an external resource for each row. > Unfortunately nothing I can do in a single update query. > Would mean keeping a lot of data manually in memory. > > On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży < > abrozynieprzelozy314...@gmail.com> wrote: > > > BTW why not to update all rows by single update query? > > > > 2018-05-27 20:30 GMT+02:00, Torsten Curdt : > > > I am doing a select, then iterate through the resultset and on each > > > row call update on that row. > > > I am using the golang driver and ran into the issue that on the > > > update > > the > > > database is still locked from the select. > > > > > > https://github.com/mattn/go-sqlite3/issues/569 > > > > > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked > > > and > > IIUC > > > these types of updates should be possible since version 3.3.7 though > > > - > > and > > > I am using 3.19.3. > > > > > > Any suggestion on how to track down why the updates fail? > > > > > > cheers, > > > Torsten > > > ___ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database locked on select
I have to query an external resource for each row. Unfortunately nothing I can do in a single update query. Would mean keeping a lot of data manually in memory. On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży < abrozynieprzelozy314...@gmail.com> wrote: > BTW why not to update all rows by single update query? > > 2018-05-27 20:30 GMT+02:00, Torsten Curdt : > > I am doing a select, then iterate through the resultset and on each row > > call update on that row. > > I am using the golang driver and ran into the issue that on the update > the > > database is still locked from the select. > > > > https://github.com/mattn/go-sqlite3/issues/569 > > > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and > IIUC > > these types of updates should be possible since version 3.3.7 though - > and > > I am using 3.19.3. > > > > Any suggestion on how to track down why the updates fail? > > > > cheers, > > Torsten > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database locked on select
There is no multi threading. Just a single thread and only one
connection/handle.
While iterating through the resultset I am also trying to execute an update
for every row. Along the lines of:
resultset = db.exec(`select`)
foreach row in resultset {
db.exec(`update`)
}
I don't want to read the whole resultset into memory.
After a lot of searching and trial and error I found that
PRAGMA journal_mode=WAL
seems to do the trick.
I don't fully understand why though.
cheers,
Torsten
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database locked on select
I am doing a select, then iterate through the resultset and on each row call update on that row. I am using the golang driver and ran into the issue that on the update the database is still locked from the select. https://github.com/mattn/go-sqlite3/issues/569 I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and IIUC these types of updates should be possible since version 3.3.7 though - and I am using 3.19.3. Any suggestion on how to track down why the updates fail? cheers, Torsten ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts3 and ranking
I've searching and reading quite a bit in the archives: So far I found the proposal of a "rank" column http://www.nabble.com/Ranking-in-fts.-td11034641.html Also Scott mentioned the internal "dump_terms" and "dumpt_doclist" functions http://www.nabble.com/FTS-statistics-and-stemming-td18298526.html But this all doesn't seem to be the definite answer yet. So is there a way to rank FTS3 results in sqlite? ..or is someone working on this? cheers -- Torsten ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
