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