Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Thomas Flemming]

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

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[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  ORDER BY 
... 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

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[R Smith]

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


Andy Ling


-Original Message-
From: sqlite-users 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
Thomas Flemming

Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf 
Of Thomas

Flemming Sent: Wednesday, 24 May, 2017 02:09
To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] 
SQLite3.Step

fast forward / skipping rows

Hi SQLite Users,


I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 
10

but this is also very slow the more down we go.

Thanks Tom






___ sqlite-users 
mailing list

sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql






___
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] SQLite3.Step fast forward / skipping rows

[Richard Hipp]

On 5/24/17, 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:
>

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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Andy Ling]

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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 14:21
To: sqlite-users@mailinglists.sqlite.org
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 50013 you can query the temp table like 
> this:
> 
> SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;
> 
> where ?1 = current_idx (such as 50013) 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:sqlite-users-boun...@mailinglists.sqlite.org] On 
>>> Behalf Of Thomas Flemming
>>> Sent: Wed 24 May 2017 10:37
>>> To: sql

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Thomas Flemming]

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 50013 you can query the temp table like this:


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

where ?1 = current_idx (such as 50013) 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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming

Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas
Flemming Sent: Wednesday, 24 May, 2017 02:09
To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step
fast forward / skipping rows

Hi SQLite Users,


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

When the user scrolls down the list, each new row is loaded with
SQL

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[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 50013 you can query 
the temp table like this:


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

where ?1 = current_idx (such as 50013) 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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
Thomas Flemming

Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
Thomas

Flemming Sent: Wednesday, 24 May, 2017 02:09
To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step
fast forward / skipping rows

Hi SQLite Users,


I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 
10

but this is also very slow the more down we go.

Thanks Tom






___ sqlite-users mailing 
list

sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql






___
sqlite-users m

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Bart Smissaert]

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  wrote:

> Hi SQLite Users,
>
>
> I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 10 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  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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Thomas Flemming]

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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas
Flemming Sent: Wednesday, 24 May, 2017 02:09
To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step
fast forward / skipping rows

Hi SQLite Users,


I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 10
but this is also very slow the more down we go.

Thanks Tom






___ sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
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  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

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Hick Gunter]

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:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Flemming
Gesendet: Mittwoch, 24. Mai 2017 10:09
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] SQLite3.Step fast forward / skipping rows

Hi SQLite Users,


I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 10 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  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


___
 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: h...@scigames.at

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas
>> Flemming Sent: Wednesday, 24 May, 2017 02:09 
>> To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step
>> fast forward / skipping rows
>> 
>> Hi SQLite Users,
>> 
>> 
>> I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 10
>> but this is also very slow the more down we go.
>> 
>> Thanks Tom
>> 
>> 

> 
> ___ sqlite-users mailing list 
> sqlite-users@mailinglists.sqlite.org 
> 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  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
---
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Thomas Flemming]

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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas
Flemming Sent: Wednesday, 24 May, 2017 02:09 
To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step

fast forward / skipping rows

Hi SQLite Users,


I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 10

but this is also very slow the more down we go.

Thanks Tom






___ sqlite-users mailing list 
sqlite-users@mailinglists.sqlite.org 
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  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

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Thomas Flemming
> Sent: Wednesday, 24 May, 2017 02:09
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] SQLite3.Step fast forward / skipping rows
> 
> Hi SQLite Users,
> 
> 
> I have a SELECT query, which returns some 10 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 10 ? I tried SELECT ... OFFSET 10 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  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



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