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 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 -- /**************************************** ** 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
