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

Reply via email to