[sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

Hi, I have a problem where I have a large set of (key, value) which I want to sort by value, and then store in a table having (rank, key, value) fields, so that for a given key I can quickly find the rank, or for a given rank range, I can quickly list the keys & values. Since there is no ROW_NUMB

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 9 Jan 2018, at 9:50am, Eric Grange wrote: > then I fill that table with something like > > INSERT INTO RANKED > SELECT key, value > FROM ...something rather complex and big... > ORDER BY value desc > > This works well enough, but as the amount of values to be ranked increase

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

You mean using limit / offset instead ? Even with an index on the VALUE column, queries like select * from ranked order by value limit 10 offset xxx become very slow when xxx is great, while select * from ranked order by rank where rank between xxx and xxx+9 are fast re

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

Isn't this really a repeat of this thread... http://sqlite.1065341.n5.nabble.com/how-into-insert-row-into-middle-of-table-with-integer-primary-key-td98629.html The result of which was, don't try and use the table row order to sort your data. Add a column that defines your sort order and do the s

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 9 Jan 2018, at 10:26am, Eric Grange wrote: > You mean using limit / offset instead ? > > Even with an index on the VALUE column, queries like > >select * from ranked >order by value >limit 10 offset xxx > > become very slow when xxx is great Yeah, to do OFFSET SQLite has to d

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange wrote: > So the order by is used to control the insertion order, so that the RANK > autoinc primary key ends up with natural rank order But then, if your range queries are based on a rank derived from value, why not index value directly? You'd still

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

> Do you actually have a need to find the 4512nd rank ? Yes, this is is used to display and check on "neighbors", ie. keys with similar rank. The other very common query is to show the rank for a given key. In both cases, since things are constantly in flux, the absolute rank and neighbor do not

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On Tue, Jan 9, 2018 at 12:35 PM, Eric Grange wrote: > > But then, if your range queries are based on a rank derived from value, > why > > not index value directly? You'd still get fast range queries based on > values, no? > > You get fast value range queries, but rank range queries become slower

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

The only way to efficiently do this would be to have counting (range) index b-trees. Since you don't, you're stuck with a O(n) implementation, either on reading or writing. So your solution is as good as it gets, save maybe some implementation particularities. However, you may consider a shift in

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

Analogous to the percentile solution (it's actually the same thing), you can use a checkpointing table. This has roughly the complexity of SQRT(n) for both read and write. I.E. say you expect to have 1M records and define order based on value then id. You then make a checkpoint table (first_rank,

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

If you were going to do this entirely in memory (perhaps in C, or some similar language), you would likely use some tree structure where each node keeps track of the number of descendants (direct and indirect) of that node. That allows the operations you describe to occur in O(log(N)) time. Sing

[sqlite] bind blob lifetime

I have cycle like this: ```c const char sql[] = "INSERT INTO test (pk, geom) VALUES (?, ?)"; sqlite3_stmt *stmt; sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL); for (...) { sqlite3_reset (stmt); sqlite3_clear_bindings (stmt); int blob_size = ..; unsign

Re: [sqlite] [EXTERNAL] bind blob lifetime

A bound blob or string is destroyed "after SQLite has finished with it". This should be the case when sqlite3_clear_bindings() is called. Are you sure it is not deleted then? Code reading suggests it should be. Other times are when the parameter is re-bound, or the statement finalized. -Urs

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On 9 Jan 2018, at 11:35am, Eric Grange wrote: > In both cases, since things are constantly in flux, the absolute rank and > neighbor do not really matter > (outside the top ranks), but changes in rank are being looked at. > i.e. having rank 155k or 154k is not really meaningful in itself, but on

[sqlite] SQLite 3.22.0 coming soon

Version 3.22.0 will probably be released soon. Your help in beta-testing is appreciated. Please download the latest "trunk" sources (from https://sqlite.org/src/info/trunk) or a pre-release snapshot (the top-most link at https://sqlite.org/download.html) and verify that you are able to build and

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

Thanks Richard. I'll build the amalgamated version in Release mode/X86 in VS 2015 Pro to see if it completes without complaint. ___ Mark C Edwards Chief Scientist, C2 Systems Engineering & Integration 779 Monika Ct Chubbuck, ID 83202 mark.c.edwa...@leidos.co

Re: [sqlite] SQLite 3.22.0 coming soon

In an attempt to be helpful, two possible typos are: https://www.sqlite.org/draft/cli.html#expert If this option is pass a non-zero argument [change pass -> passed] https://www.sqlite.org/draft/releaselog/3_22_0.html A complex result set bias the decision Maybe " A complex re

Re: [sqlite] SQLite 3.22.0 coming soon

Oh sweet, zipfile extension. Small typo fixes: In https://www.sqlite.org/draft/rtree.html#rtreecheck "on the rtree table named R containing in database S." should be "on the rtree table named R contained in database S." https://www.sqlite.org/draft/cli.html#expert "If this option is pass a non-

Re: [sqlite] SQLite 3.22.0 coming soon

On 9 Jan 2018, at 6:46pm, Richard Hipp wrote: > The latest change summary can be seen at > https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft In … For "clobbering" use "overwriting", if only for the sake of international readers. "

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

The Release build/X86 on Visual Studio 2015 Pro worked. The prior problem with "possible uninitialized variables" has been resolved. 3.22.0 looks like a viable candidate for incorporation in our source base. We use only the amalgamated sqlite3.h, sqlite3ext.h and sqlite3.c files. Thanks! _

Re: [sqlite] SQLite 3.22.0 coming soon

Hello, I could spot two behavior changes in f6355970 while testing GRDB.swift. 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a, :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a" etc.) for all parameters, the last one included. The latest versi

Re: [sqlite] SQLite 3.22.0 coming soon

swarmvtab3 test is still failing... (windows 10, msvc 2017) 10.0.16299.0 swarmvtab3-1.2.3... Error: inconsistent ::dbcache and disk swarmvtab3-1.3.3... Error: inconsistent ::dbcache and disk On Tue, Jan 9, 2018 at 11:46 AM, Simon Slavin wrote: > On 9 Jan 2018, at 6:46pm, Richard Hipp wrote:

Re: [sqlite] SQLite 3.22.0 coming soon

On 1/9/18, Gwendal Roué wrote: > 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a, > :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a" > etc.) for all parameters, the last one included. The latest version returns > an empty string for the last par

Re: [sqlite] SQLite 3.22.0 coming soon

> Le 9 janv. 2018 à 21:47, Richard Hipp a écrit : > > On 1/9/18, Gwendal Roué wrote: >> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a, >> :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a" >> etc.) for all parameters, the last one included. T

Re: [sqlite] SQLite 3.22.0 coming soon

On Tue, Jan 9, 2018 at 1:19 PM, Gwendal Roué wrote: > > > Le 9 janv. 2018 à 21:47, Richard Hipp a écrit : > > > > On 1/9/18, Gwendal Roué wrote: > >> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT > :a, > >> :b`, the sqlite3_column_name used to return the parameter name

Re: [sqlite] SQLite 3.22.0 coming soon

Dear SQLite developers and eventual PowerPC users, using a likely outdated compiler: powerpc-apple-darwin9-gcc-4.0.1 a compile error occors: shell.c:10062: error: conflicting types for ‘integerValue’ shell.c:9169: error: previous implicit declaration of ‘integerValue’ was here i

Re: [sqlite] SQLite 3.22.0 coming soon

On 1/9/18, E.Pasma wrote: > Dear SQLite developers and eventual PowerPC users, > using a likely outdated compiler: > powerpc-apple-darwin9-gcc-4.0.1 > a compile error occors: > shell.c:10062: error: conflicting types for ‘integerValue’ > shell.c:9169: error: previous implicit dec

[sqlite] SQLite 3.22.0 coming soon

All of the minor issues mentioned by prior emails in this thread should now be fixed. Thanks to everybody for proof-reading and testing! Fresh source code is now available on trunk (https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot" link on the https://sqlite.org/download.html

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

Release mode/x86 Visual Studio 2015 Prono problems with the new snapshot ___ Mark C Edwards Chief Scientist, C2 Systems Engineering & Integration 779 Monika Ct Chubbuck, ID 83202 mark.c.edwa...@leidos.com Mobile: 208-241-7982 -Original Message-

Re: [sqlite] SQLite 3.22.0 coming soon

No more glitch with sqlite3_column_name :-) Thanks Richard, Gwendal > Le 10 janv. 2018 à 02:06, Richard Hipp a écrit : > > All of the minor issues mentioned by prior emails in this thread > should now be fixed. Thanks to everybody for proof-reading and > testing! > > Fresh source code is now