SQLite3.Step fast forward / skipping rows

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite3.Step fast forward / skipping rows

tf@qvgps.com
Hi SQLite Users,


I have a SELECT query, which returns some 100000 records and is displayed in a
scrollable ListView.

When the user scrolls down the list, each new row is loaded with SQLite3.Step().

The problem is, when the user scrolls fast with the scroll-slider, lots of
rows are skipped, but SQLite still needs to load them all with SQLite3.Step
until it reaches the row which is actually needed. This is very slow.

Is there a way to skip all these unnecessary rows? For example going directly
from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000 but this is
also very slow the more down we go.

Thanks
Tom


--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Keith Medcalf

You need to write your application like a telephone directory.  To get to the page with the "Smithson" entry on it, you do not read all the entries starting from the begining until you get there -- you turn directly to the page you want by doing a search.

Surely you have a unique key for the list?

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Thomas Flemming
> Sent: Wednesday, 24 May, 2017 02:09
> To: [hidden email]
> Subject: [sqlite] SQLite3.Step fast forward / skipping rows
>
> Hi SQLite Users,
>
>
> I have a SELECT query, which returns some 100000 records and is displayed
> in a
> scrollable ListView.
>
> When the user scrolls down the list, each new row is loaded with
> SQLite3.Step().
>
> The problem is, when the user scrolls fast with the scroll-slider, lots of
> rows are skipped, but SQLite still needs to load them all with
> SQLite3.Step
> until it reaches the row which is actually needed. This is very slow.
>
> Is there a way to skip all these unnecessary rows? For example going
> directly
> from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000 but this is
> also very slow the more down we go.
>
> Thanks
> Tom
>
>
> --
> /****************************************
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  [hidden email]
> **   +264 (0)81 3329923
> **   +49  (0)6182 8492599
> ***************************************/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

tf@qvgps.com
Almost, but when you compare to a telephone directory, then the use case of
fast scrolling down in a listbox would be going directly to page 800 and not
going to  "Smithson".

And yes, there is a unique key, but this doesn't help, because the list can
also be sorted to various columns.


Am 24.05.2017 um 10:27 schrieb Keith Medcalf:

> You need to write your application like a telephone directory.  To get to
> the page with the "Smithson" entry on it, you do not read all the entries
> starting from the begining until you get there -- you turn directly to the
> page you want by doing a search.
>
> Surely you have a unique key for the list?
>
> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>> -----Original Message----- From: sqlite-users
>> [mailto:[hidden email]] On Behalf Of Thomas
>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>> To:[hidden email] Subject: [sqlite] SQLite3.Step
>> fast forward / skipping rows
>>
>> Hi SQLite Users,
>>
>>
>> I have a SELECT query, which returns some 100000 records and is
>> displayed in a scrollable ListView.
>>
>> When the user scrolls down the list, each new row is loaded with
>> SQLite3.Step().
>>
>> The problem is, when the user scrolls fast with the scroll-slider, lots
>> of rows are skipped, but SQLite still needs to load them all with
>> SQLite3.Step until it reaches the row which is actually needed. This is
>> very slow.
>>
>> Is there a way to skip all these unnecessary rows? For example going
>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000
>> but this is also very slow the more down we go.
>>
>> Thanks Tom
>>
>>

>
> _______________________________________________ sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Andy Ling-2
Then when you detect a jump you'll need to use a new search to "jump" to the page you want. Something like

SELECT ....  WHERE sortedColumn > lastValue ORDER BY sortedColumn

And make sure you have indexes on all the columns that you can sort by.

Andy Ling


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 10:37
To: [hidden email]
Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows

Almost, but when you compare to a telephone directory, then the use case of
fast scrolling down in a listbox would be going directly to page 800 and not
going to  "Smithson".

And yes, there is a unique key, but this doesn't help, because the list can
also be sorted to various columns.


Am 24.05.2017 um 10:27 schrieb Keith Medcalf:

> You need to write your application like a telephone directory.  To get to
> the page with the "Smithson" entry on it, you do not read all the entries
> starting from the begining until you get there -- you turn directly to the
> page you want by doing a search.
>
> Surely you have a unique key for the list?
>
> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>> -----Original Message----- From: sqlite-users
>> [mailto:[hidden email]] On Behalf Of Thomas
>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>> To:[hidden email] Subject: [sqlite] SQLite3.Step
>> fast forward / skipping rows
>>
>> Hi SQLite Users,
>>
>>
>> I have a SELECT query, which returns some 100000 records and is
>> displayed in a scrollable ListView.
>>
>> When the user scrolls down the list, each new row is loaded with
>> SQLite3.Step().
>>
>> The problem is, when the user scrolls fast with the scroll-slider, lots
>> of rows are skipped, but SQLite still needs to load them all with
>> SQLite3.Step until it reaches the row which is actually needed. This is
>> very slow.
>>
>> Is there a way to skip all these unnecessary rows? For example going
>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000
>> but this is also very slow the more down we go.
>>
>> Thanks Tom
>>
>>

>
> _______________________________________________ sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---------------------------------------------------------------------------------------
This email has been scanned for email related threats and delivered safely by Mimecast.
For more information please visit http://www.mimecast.com
---------------------------------------------------------------------------------------

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Hick Gunter
In reply to this post by tf@qvgps.com
The trick is to have a way to identify the first/current row and use that in the WHERE clause.

e.g. SELECT ... FROM customers WHERE customer_id >= last_displayed LIMIT window_size

If your select statement is a complex join without any usable key, you will have to resort to storing the results

    CREATE TEMP TABLE select_results AS SELECT ...

And displaying only those within the current window

    SELECT * FROM select_results WHERE rowid >= starting_line LIMIT window_size;


As an optimization, you can populate the temporary table in the background while displaying the first page of results.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thomas Flemming
Gesendet: Mittwoch, 24. Mai 2017 10:09
An: [hidden email]
Betreff: [sqlite] SQLite3.Step fast forward / skipping rows

Hi SQLite Users,


I have a SELECT query, which returns some 100000 records and is displayed in a scrollable ListView.

When the user scrolls down the list, each new row is loaded with SQLite3.Step().

The problem is, when the user scrolls fast with the scroll-slider, lots of rows are skipped, but SQLite still needs to load them all with SQLite3.Step until it reaches the row which is actually needed. This is very slow.

Is there a way to skip all these unnecessary rows? For example going directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000 but this is also very slow the more down we go.

Thanks
Tom


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


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

tf@qvgps.com
In reply to this post by Andy Ling-2
Yes, but this would still be slow, because lastValue is lets say page 50 in
the telephone directory, but I need to go to page 800.
So this query would still return all pages from 50 to 800, which I dont need.



Am 24.05.2017 um 10:45 schrieb Andy Ling:

> Then when you detect a jump you'll need to use a new search to "jump" to the page you want. Something like
>
> SELECT ....  WHERE sortedColumn > lastValue ORDER BY sortedColumn
>
> And make sure you have indexes on all the columns that you can sort by.
>
> Andy Ling
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Flemming
> Sent: Wed 24 May 2017 10:37
> To: [hidden email]
> Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows
>
> Almost, but when you compare to a telephone directory, then the use case of
> fast scrolling down in a listbox would be going directly to page 800 and not
> going to  "Smithson".
>
> And yes, there is a unique key, but this doesn't help, because the list can
> also be sorted to various columns.
>
>
> Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
>> You need to write your application like a telephone directory.  To get to
>> the page with the "Smithson" entry on it, you do not read all the entries
>> starting from the begining until you get there -- you turn directly to the
>> page you want by doing a search.
>>
>> Surely you have a unique key for the list?
>>
>> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>> -----Original Message----- From: sqlite-users
>>> [mailto:[hidden email]] On Behalf Of Thomas
>>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>>> To:[hidden email] Subject: [sqlite] SQLite3.Step
>>> fast forward / skipping rows
>>>
>>> Hi SQLite Users,
>>>
>>>
>>> I have a SELECT query, which returns some 100000 records and is
>>> displayed in a scrollable ListView.
>>>
>>> When the user scrolls down the list, each new row is loaded with
>>> SQLite3.Step().
>>>
>>> The problem is, when the user scrolls fast with the scroll-slider, lots
>>> of rows are skipped, but SQLite still needs to load them all with
>>> SQLite3.Step until it reaches the row which is actually needed. This is
>>> very slow.
>>>
>>> Is there a way to skip all these unnecessary rows? For example going
>>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000
>>> but this is also very slow the more down we go.
>>>
>>> Thanks Tom
>>>
>>>
>
>>
>> _______________________________________________ sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Bart Smissaert
In reply to this post by tf@qvgps.com
I might be wrong, but to me this sounds like an application coding problem
to do with your listview.
You will need to code in such a way that the listview doesn't get updated
when it doesn' t have to.
Does the data come directly from the DB or is there an intermediate eg an
array?

RBS


On Wed, May 24, 2017 at 9:09 AM, Thomas Flemming <[hidden email]> wrote:

> Hi SQLite Users,
>
>
> I have a SELECT query, which returns some 100000 records and is displayed
> in a scrollable ListView.
>
> When the user scrolls down the list, each new row is loaded with
> SQLite3.Step().
>
> The problem is, when the user scrolls fast with the scroll-slider, lots of
> rows are skipped, but SQLite still needs to load them all with SQLite3.Step
> until it reaches the row which is actually needed. This is very slow.
>
> Is there a way to skip all these unnecessary rows? For example going
> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000 but
> this is also very slow the more down we go.
>
> Thanks
> Tom
>
>
> --
> /****************************************
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  [hidden email]
> **   +264 (0)81 3329923
> **   +49 (0)6182 8492599
> ***************************************/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

R Smith
In reply to this post by tf@qvgps.com
You are asking the DB to give you all the 8000...+ results, sort them
and then you opt to only look at some of them, there is no way this can
ever be fast in any system, you need to rethink how you ask for information.

First things first, you should never be using the sqlite (or any other
database's) STEP to support user scrolling, you should be using it to
load the results you want to see, and then in a different method show
those results to the user. What if the user wants to move up by one
line? You can't un-step in a database.

There are many ways this can be overcome, first with dynamic listviews:

The way to set up a dynamic listview is to get a query of the ID's of
the entire list of possible values, sorted and so on, that you might
want to display into your own list object or array. Then populate the
listview with the ID's only and determine which are visible, for the
visible ones, load the data from a query using only those ID's, perhaps
something like:

SELECT v1, v2.... FROM MyMainTable WHERE ID IN (7001, 7002, 7003...for
all IDs visible...);

and set them tot he screen. If the user scrolls loads, you update only
when needed, perhaps using a time difference function or such, and when
the view "settles" load those results that are visible. Almost all
programming systems with visual components like "Listview" has a
function or callback that can tell you the current visible items AND
whether the visible index/count changed or not. It is often enough to
catch this and simply update the visible items when such a change happens.

Another way is to Query to a temporary table with an automatic
incremented primary key, and simply read from that table the paginated
values, i.e. if your listview scrolls to line 500000013 you can query
the temp table like this:

SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;

where ?1 = current_idx (such as 500000013) and ?2 = current_idx +
page_items_count as defined in your software;

This is extremely fast, only the initial query will take some time.

What you can't do is query an insane amount of rows EVERY time the user
moves the cursor or scrolls the page, there is no system in existence
that will do that quick, ever.

Good luck!
Ryan


On 2017/05/24 11:53 AM, Thomas Flemming wrote:

> Yes, but this would still be slow, because lastValue is lets say page
> 50 in the telephone directory, but I need to go to page 800.
> So this query would still return all pages from 50 to 800, which I
> dont need.
>
>
>
> Am 24.05.2017 um 10:45 schrieb Andy Ling:
>> Then when you detect a jump you'll need to use a new search to "jump"
>> to the page you want. Something like
>>
>> SELECT ....  WHERE sortedColumn > lastValue ORDER BY sortedColumn
>>
>> And make sure you have indexes on all the columns that you can sort by.
>>
>> Andy Ling
>>
>>
>> -----Original Message-----
>> From: sqlite-users
>> [mailto:[hidden email]] On Behalf Of
>> Thomas Flemming
>> Sent: Wed 24 May 2017 10:37
>> To: [hidden email]
>> Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows
>>
>> Almost, but when you compare to a telephone directory, then the use
>> case of
>> fast scrolling down in a listbox would be going directly to page 800
>> and not
>> going to  "Smithson".
>>
>> And yes, there is a unique key, but this doesn't help, because the
>> list can
>> also be sorted to various columns.
>>
>>
>> Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
>>> You need to write your application like a telephone directory.  To
>>> get to
>>> the page with the "Smithson" entry on it, you do not read all the
>>> entries
>>> starting from the begining until you get there -- you turn directly
>>> to the
>>> page you want by doing a search.
>>>
>>> Surely you have a unique key for the list?
>>>
>>> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>>> -----Original Message----- From: sqlite-users
>>>> [mailto:[hidden email]] On Behalf Of
>>>> Thomas
>>>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>>>> To:[hidden email] Subject: [sqlite] SQLite3.Step
>>>> fast forward / skipping rows
>>>>
>>>> Hi SQLite Users,
>>>>
>>>>
>>>> I have a SELECT query, which returns some 100000 records and is
>>>> displayed in a scrollable ListView.
>>>>
>>>> When the user scrolls down the list, each new row is loaded with
>>>> SQLite3.Step().
>>>>
>>>> The problem is, when the user scrolls fast with the scroll-slider,
>>>> lots
>>>> of rows are skipped, but SQLite still needs to load them all with
>>>> SQLite3.Step until it reaches the row which is actually needed.
>>>> This is
>>>> very slow.
>>>>
>>>> Is there a way to skip all these unnecessary rows? For example going
>>>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET
>>>> 100000
>>>> but this is also very slow the more down we go.
>>>>
>>>> Thanks Tom
>>>>
>>>>
>>
>>>
>>> _______________________________________________ sqlite-users mailing
>>> list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql
>>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

tf@qvgps.com
Hi Ron,

 >  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...

Tom


Am 24.05.2017 um 13:20 schrieb R Smith:

> You are asking the DB to give you all the 8000...+ results, sort them and then
> you opt to only look at some of them, there is no way this can ever be fast in
> any system, you need to rethink how you ask for information.
>
> First things first, you should never be using the sqlite (or any other
> database's) STEP to support user scrolling, you should be using it to load the
> results you want to see, and then in a different method show those results to
> the user. What if the user wants to move up by one line? You can't un-step in
> a database.
>
> There are many ways this can be overcome, first with dynamic listviews:
>
> The way to set up a dynamic listview is to get a query of the ID's of the
> entire list of possible values, sorted and so on, that you might want to
> display into your own list object or array. Then populate the listview with
> the ID's only and determine which are visible, for the visible ones, load the
> data from a query using only those ID's, perhaps something like:
>
> SELECT v1, v2.... FROM MyMainTable WHERE ID IN (7001, 7002, 7003...for all IDs
> visible...);
>
> and set them tot he screen. If the user scrolls loads, you update only when
> needed, perhaps using a time difference function or such, and when the view
> "settles" load those results that are visible. Almost all programming systems
> with visual components like "Listview" has a function or callback that can
> tell you the current visible items AND whether the visible index/count changed
> or not. It is often enough to catch this and simply update the visible items
> when such a change happens.
>
> Another way is to Query to a temporary table with an automatic incremented
> primary key, and simply read from that table the paginated values, i.e. if
> your listview scrolls to line 500000013 you can query the temp table like this:
>
> SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;
>
> where ?1 = current_idx (such as 500000013) and ?2 = current_idx +
> page_items_count as defined in your software;
>
> This is extremely fast, only the initial query will take some time.
>
> What you can't do is query an insane amount of rows EVERY time the user moves
> the cursor or scrolls the page, there is no system in existence that will do
> that quick, ever.
>
> Good luck!
> Ryan
>
>
> On 2017/05/24 11:53 AM, Thomas Flemming wrote:
>> Yes, but this would still be slow, because lastValue is lets say page 50 in
>> the telephone directory, but I need to go to page 800.
>> So this query would still return all pages from 50 to 800, which I dont need.
>>
>>
>>
>> Am 24.05.2017 um 10:45 schrieb Andy Ling:
>>> Then when you detect a jump you'll need to use a new search to "jump" to
>>> the page you want. Something like
>>>
>>> SELECT ....  WHERE sortedColumn > lastValue ORDER BY sortedColumn
>>>
>>> And make sure you have indexes on all the columns that you can sort by.
>>>
>>> Andy Ling
>>>
>>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:[hidden email]] On
>>> Behalf Of Thomas Flemming
>>> Sent: Wed 24 May 2017 10:37
>>> To: [hidden email]
>>> Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows
>>>
>>> Almost, but when you compare to a telephone directory, then the use case of
>>> fast scrolling down in a listbox would be going directly to page 800 and not
>>> going to  "Smithson".
>>>
>>> And yes, there is a unique key, but this doesn't help, because the list can
>>> also be sorted to various columns.
>>>
>>>
>>> Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
>>>> You need to write your application like a telephone directory.  To get to
>>>> the page with the "Smithson" entry on it, you do not read all the entries
>>>> starting from the begining until you get there -- you turn directly to the
>>>> page you want by doing a search.
>>>>
>>>> Surely you have a unique key for the list?
>>>>
>>>> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>>>> -----Original Message----- From: sqlite-users
>>>>> [mailto:[hidden email]] On Behalf Of Thomas
>>>>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>>>>> To:[hidden email] Subject: [sqlite] SQLite3.Step
>>>>> fast forward / skipping rows
>>>>>
>>>>> Hi SQLite Users,
>>>>>
>>>>>
>>>>> I have a SELECT query, which returns some 100000 records and is
>>>>> displayed in a scrollable ListView.
>>>>>
>>>>> When the user scrolls down the list, each new row is loaded with
>>>>> SQLite3.Step().
>>>>>
>>>>> The problem is, when the user scrolls fast with the scroll-slider, lots
>>>>> of rows are skipped, but SQLite still needs to load them all with
>>>>> SQLite3.Step until it reaches the row which is actually needed. This is
>>>>> very slow.
>>>>>
>>>>> Is there a way to skip all these unnecessary rows? For example going
>>>>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000
>>>>> but this is also very slow the more down we go.
>>>>>
>>>>> Thanks Tom
>>>>>
>>>>>
>>>
>>>>
>>>> _______________________________________________ sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql
>>>
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Andy Ling-2
Whilst this might make writing your application easier, when you think about what has to happen
"under the hood" it can't really be any quicker. The database still has to read all the rows
that satisfy your WHERE clause and store them somewhere while it sorts them based on
your ORDER BY clause, then count through to row "rowNumber" to give you the row you asked for.

Not much different to the suggestion already made to create a temporary table.

Andy Ling


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 14:21
To: [hidden email]
Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows

Hi Ron,

 >  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...

Tom


Am 24.05.2017 um 13:20 schrieb R Smith:

> You are asking the DB to give you all the 8000...+ results, sort them and then
> you opt to only look at some of them, there is no way this can ever be fast in
> any system, you need to rethink how you ask for information.
>
> First things first, you should never be using the sqlite (or any other
> database's) STEP to support user scrolling, you should be using it to load the
> results you want to see, and then in a different method show those results to
> the user. What if the user wants to move up by one line? You can't un-step in
> a database.
>
> There are many ways this can be overcome, first with dynamic listviews:
>
> The way to set up a dynamic listview is to get a query of the ID's of the
> entire list of possible values, sorted and so on, that you might want to
> display into your own list object or array. Then populate the listview with
> the ID's only and determine which are visible, for the visible ones, load the
> data from a query using only those ID's, perhaps something like:
>
> SELECT v1, v2.... FROM MyMainTable WHERE ID IN (7001, 7002, 7003...for all IDs
> visible...);
>
> and set them tot he screen. If the user scrolls loads, you update only when
> needed, perhaps using a time difference function or such, and when the view
> "settles" load those results that are visible. Almost all programming systems
> with visual components like "Listview" has a function or callback that can
> tell you the current visible items AND whether the visible index/count changed
> or not. It is often enough to catch this and simply update the visible items
> when such a change happens.
>
> Another way is to Query to a temporary table with an automatic incremented
> primary key, and simply read from that table the paginated values, i.e. if
> your listview scrolls to line 500000013 you can query the temp table like this:
>
> SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;
>
> where ?1 = current_idx (such as 500000013) and ?2 = current_idx +
> page_items_count as defined in your software;
>
> This is extremely fast, only the initial query will take some time.
>
> What you can't do is query an insane amount of rows EVERY time the user moves
> the cursor or scrolls the page, there is no system in existence that will do
> that quick, ever.
>
> Good luck!
> Ryan
>
>
> On 2017/05/24 11:53 AM, Thomas Flemming wrote:
>> Yes, but this would still be slow, because lastValue is lets say page 50 in
>> the telephone directory, but I need to go to page 800.
>> So this query would still return all pages from 50 to 800, which I dont need.
>>
>>
>>
>> Am 24.05.2017 um 10:45 schrieb Andy Ling:
>>> Then when you detect a jump you'll need to use a new search to "jump" to
>>> the page you want. Something like
>>>
>>> SELECT ....  WHERE sortedColumn > lastValue ORDER BY sortedColumn
>>>
>>> And make sure you have indexes on all the columns that you can sort by.
>>>
>>> Andy Ling
>>>
>>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:[hidden email]] On
>>> Behalf Of Thomas Flemming
>>> Sent: Wed 24 May 2017 10:37
>>> To: [hidden email]
>>> Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows
>>>
>>> Almost, but when you compare to a telephone directory, then the use case of
>>> fast scrolling down in a listbox would be going directly to page 800 and not
>>> going to  "Smithson".
>>>
>>> And yes, there is a unique key, but this doesn't help, because the list can
>>> also be sorted to various columns.
>>>
>>>
>>> Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
>>>> You need to write your application like a telephone directory.  To get to
>>>> the page with the "Smithson" entry on it, you do not read all the entries
>>>> starting from the begining until you get there -- you turn directly to the
>>>> page you want by doing a search.
>>>>
>>>> Surely you have a unique key for the list?
>>>>
>>>> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>>>> -----Original Message----- From: sqlite-users
>>>>> [mailto:[hidden email]] On Behalf Of Thomas
>>>>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>>>>> To:[hidden email] Subject: [sqlite] SQLite3.Step
>>>>> fast forward / skipping rows
>>>>>
>>>>> Hi SQLite Users,
>>>>>
>>>>>
>>>>> I have a SELECT query, which returns some 100000 records and is
>>>>> displayed in a scrollable ListView.
>>>>>
>>>>> When the user scrolls down the list, each new row is loaded with
>>>>> SQLite3.Step().
>>>>>
>>>>> The problem is, when the user scrolls fast with the scroll-slider, lots
>>>>> of rows are skipped, but SQLite still needs to load them all with
>>>>> SQLite3.Step until it reaches the row which is actually needed. This is
>>>>> very slow.
>>>>>
>>>>> Is there a way to skip all these unnecessary rows? For example going
>>>>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET 100000
>>>>> but this is also very slow the more down we go.
>>>>>
>>>>> Thanks Tom
>>>>>
>>>>>
>>>
>>>>
>>>> _______________________________________________ sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql
>>>
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---------------------------------------------------------------------------------------
This email has been scanned for email related threats and delivered safely by Mimecast.
For more information please visit http://www.mimecast.com
---------------------------------------------------------------------------------------

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Richard Hipp-3
In reply to this post by tf@qvgps.com
On 5/24/17, Thomas Flemming <[hidden email]> wrote:

> Hi Ron,
>
>  >  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:
>

What is happening behind the scenes is that the entire query is run
and the results are stored in memory.  Then the cursor can easily go
forward or backwards in this memory buffer.

That approach works great as long as your result set isn't too large.
But if you do a huge query, you can blow out memory.

SQLite only loads a single row of the result into memory at a time.
This saves on memory usage, but has the disadvantage that you can't go
backwards.

You can write a wrapper class around the core SQLite APIs that works
like that other DB and pulls the entire result set into memory, then
lets you scroll forwards and backwards.

Another work-around is to load the query results into a TEMP table like this:

    CREATE TEMP TABLE res AS SELECT ....;

Then if you want to view (say) the 100th through the 110th rows of the
result, run:

    SELECT * FROM res WHERE rowid BETWEN 100 AND 110;

Remember to "DROP TABLE res" when you are done.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

R Smith
In reply to this post by tf@qvgps.com


On 2017/05/24 3:21 PM, Thomas Flemming wrote:

> Hi Ron,
>
> >  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.

SQLite is the database engine, not a data class. The thing you talk
about, Valentina DB and that cursor statement actually invokes a data
class that does a LOT of processing and background information handling
for you - much like the things we've been suggesting in point of fact.

It's a bit like asking an engine manufacturer why the engine you've
purchased doesn't come with a rear-passenger-seat, because the previous
car you drove had a great rear-passenger-seat. SQLite is only the
engine, lots of systems however have wrapping data classes that will do
a lot of processing for you, much like what you are used to.
system.data.sqlite if you are .net MSVS fan or such, DBX for any of the
Delphi, C++, Visual C, C# stuff, JAVA has many wrappers, so do Python,
PHP etc. etc.

Perhaps you are looking for one of these, if you specify your
development environment, lots of people here will be able to suggest the
best database wrappers and data handlers for you.

>
> > 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...

I have no need to imagine, I use this often, it is very much lighter
than you think - even for hundreds of millions of rows. If you store to
a temporary table, sqlite will manage by itself how much of stuff stay
in memory and how much spills to disk etc. Either way, you querying that
temp table will be extremely fast and your users will have a very speedy
experience. In the words of my favourite dentist: "You won't a feel a
thing..."

>
> Tom



>
>
> Am 24.05.2017 um 13:20 schrieb R Smith:
>> You are asking the DB to give you all the 8000...+ results, sort them
>> and then you opt to only look at some of them, there is no way this
>> can ever be fast in any system, you need to rethink how you ask for
>> information.
>>
>> First things first, you should never be using the sqlite (or any
>> other database's) STEP to support user scrolling, you should be using
>> it to load the results you want to see, and then in a different
>> method show those results to the user. What if the user wants to move
>> up by one line? You can't un-step in a database.
>>
>> There are many ways this can be overcome, first with dynamic listviews:
>>
>> The way to set up a dynamic listview is to get a query of the ID's of
>> the entire list of possible values, sorted and so on, that you might
>> want to display into your own list object or array. Then populate the
>> listview with the ID's only and determine which are visible, for the
>> visible ones, load the data from a query using only those ID's,
>> perhaps something like:
>>
>> SELECT v1, v2.... FROM MyMainTable WHERE ID IN (7001, 7002,
>> 7003...for all IDs visible...);
>>
>> and set them tot he screen. If the user scrolls loads, you update
>> only when needed, perhaps using a time difference function or such,
>> and when the view "settles" load those results that are visible.
>> Almost all programming systems with visual components like "Listview"
>> has a function or callback that can tell you the current visible
>> items AND whether the visible index/count changed or not. It is often
>> enough to catch this and simply update the visible items when such a
>> change happens.
>>
>> Another way is to Query to a temporary table with an automatic
>> incremented primary key, and simply read from that table the
>> paginated values, i.e. if your listview scrolls to line 500000013 you
>> can query the temp table like this:
>>
>> SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;
>>
>> where ?1 = current_idx (such as 500000013) and ?2 = current_idx +
>> page_items_count as defined in your software;
>>
>> This is extremely fast, only the initial query will take some time.
>>
>> What you can't do is query an insane amount of rows EVERY time the
>> user moves the cursor or scrolls the page, there is no system in
>> existence that will do that quick, ever.
>>
>> Good luck!
>> Ryan
>>
>>
>> On 2017/05/24 11:53 AM, Thomas Flemming wrote:
>>> Yes, but this would still be slow, because lastValue is lets say
>>> page 50 in the telephone directory, but I need to go to page 800.
>>> So this query would still return all pages from 50 to 800, which I
>>> dont need.
>>>
>>>
>>>
>>> Am 24.05.2017 um 10:45 schrieb Andy Ling:
>>>> Then when you detect a jump you'll need to use a new search to
>>>> "jump" to the page you want. Something like
>>>>
>>>> SELECT ....  WHERE sortedColumn > lastValue ORDER BY sortedColumn
>>>>
>>>> And make sure you have indexes on all the columns that you can sort
>>>> by.
>>>>
>>>> Andy Ling
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: sqlite-users
>>>> [mailto:[hidden email]] On Behalf Of
>>>> Thomas Flemming
>>>> Sent: Wed 24 May 2017 10:37
>>>> To: [hidden email]
>>>> Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows
>>>>
>>>> Almost, but when you compare to a telephone directory, then the use
>>>> case of
>>>> fast scrolling down in a listbox would be going directly to page
>>>> 800 and not
>>>> going to  "Smithson".
>>>>
>>>> And yes, there is a unique key, but this doesn't help, because the
>>>> list can
>>>> also be sorted to various columns.
>>>>
>>>>
>>>> Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
>>>>> You need to write your application like a telephone directory.  To
>>>>> get to
>>>>> the page with the "Smithson" entry on it, you do not read all the
>>>>> entries
>>>>> starting from the begining until you get there -- you turn
>>>>> directly to the
>>>>> page you want by doing a search.
>>>>>
>>>>> Surely you have a unique key for the list?
>>>>>
>>>>> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>>>>> -----Original Message----- From: sqlite-users
>>>>>> [mailto:[hidden email]] On Behalf
>>>>>> Of Thomas
>>>>>> Flemming Sent: Wednesday, 24 May, 2017 02:09
>>>>>> To:[hidden email] Subject: [sqlite]
>>>>>> SQLite3.Step
>>>>>> fast forward / skipping rows
>>>>>>
>>>>>> Hi SQLite Users,
>>>>>>
>>>>>>
>>>>>> I have a SELECT query, which returns some 100000 records and is
>>>>>> displayed in a scrollable ListView.
>>>>>>
>>>>>> When the user scrolls down the list, each new row is loaded with
>>>>>> SQLite3.Step().
>>>>>>
>>>>>> The problem is, when the user scrolls fast with the
>>>>>> scroll-slider, lots
>>>>>> of rows are skipped, but SQLite still needs to load them all with
>>>>>> SQLite3.Step until it reaches the row which is actually needed.
>>>>>> This is
>>>>>> very slow.
>>>>>>
>>>>>> Is there a way to skip all these unnecessary rows? For example going
>>>>>> directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET
>>>>>> 100000
>>>>>> but this is also very slow the more down we go.
>>>>>>
>>>>>> Thanks Tom
>>>>>>
>>>>>>
>>>>
>>>>>
>>>>> _______________________________________________ sqlite-users
>>>>> mailing list
>>>>> [hidden email]
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql
>>>>
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

Keith Medcalf
In reply to this post by tf@qvgps.com
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3.Step fast forward / skipping rows

tf@qvgps.com
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
> [hidden email]
> 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  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users