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_NUMBER() function, but there is an autoincrement
feature, and the rank are numbered 1, 2, 3 etc. the strategy I have
been using is to create ranked table like
CREATE RANKED (
RANK INTEGER PRIMARY KEY AUTOINCREMENT,
KEY INTEGER,
VALUE FLOAT
)
(+ an index for the key)
and 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 increases,
this feels wasteful to delete everything and then re-insert
everything just to adjust the RANK column, also I am running into memory
issues as the ORDER BY requires a temporary b-tree
which runs into the gigabyte range in some instances.
I have ways to maintain the KEY and VALUES individually and incrementally,
but approaches I have tried to maintain the RANK
with UPDATE queries ran much slower than deleting and recreating
everything, though this could just be bad implementations
from my part.
Are there any other strategies I could use that could update just the RANK
field and mitigate the temporary B-tree size?
Eric
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
