Like this it works.

OK, building the temporary table takes some seconds with a table of 10 mio records, but the queries are really fast then, 10 to 30 ms!!

Thanks so much guys for helping me with this :)
Tom


Am 24.05.2017 um 18:42 schrieb Keith Medcalf:
On Wednesday, 24 May, 2017 07:21

  >  there is no system in existence that will do
I was working a lot with Valentina-DB and they have a cursor class:

var cursor=database.SqlSelect( "SELECT... WHERE... ORDER...");

then you can just get any the ListView wants, forward and backwards, very
fast:

cursor.Position = rownumber;

I'm quiet new to SQLite and was surprised, that its so difficult to write
this
kind of cursor.

  > Another way is to Query to a temporary table with an automatic
incremented
  > This is extremely fast, only the initial query will take some time.
yeah, this might work, but imagine how much time and memory this would
cost
for 10mio records...

This is exactly how things that "pretend" to have cursors work.  Except they have the 
support "built-in" to either the client or the server.  Basically, you do the following:

pragma journal_mode=WAL;
begin;
drop table if exists temp.myPhonyCursor;
create temporary table if not exists myPhonyCursor as
SELECT table.RowID as tableRowID FROM TABLE WHERE <conditions on what to include in 
cursor> ORDER BY <how you want it sorted>
... your queries to retrieve rows go here -- proceed to drop/commit when you 
are done with the cursor ...
drop table if exists temp.myPhonyCursor;
commit;

Now, whenever you want to retrieve some data, you can do something like:

SELECT table.* FROM myPhonyCursor, table where table.RowID == 
myPhonyCursor.tableRowID and myPhonyCursor.RowID between @StartingRow and 
@EndingRow order by myPhonyCursor.RowID;  -- for a forwards read and

SELECT table.* FROM myPhonyCursor, table where table.RowID == 
myPhonyCursor.tableRowID and myPhonyCursor.RowID between @StartingRow and 
@EndingRow order by myPhonyCursor.RowID desc; -- to see the rows in reverse 
order.

of course, for this to be effective you need to be either (a) the only user of the database or (b) 
have to wrap the whole thing in a transaction and be using WAL mode in order to achieve repeatable 
read isolation across multiple queries or you are liable to have result rows "disappear" 
or "appear out of order".

if you want "page numbers", zero based, then you can do the following:

SELECT table.* FROM myPhonyCursor, table where table.RowID == 
myPhonyCursor.tableRowID and myPhonyCursor.RowID > (@pageNumber * @PageSize) 
order by myPhonyCursor.RowID  limit @PageSize;

When you need to change the sort order or whatever you simply regenerate 
myPhonyCursor.

If the temp tables are in memory and you have the appropriate indexes to 
process the ordered query, generating the myPhonyCursor table is quite fast, 
even for millions of rows.





_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to