So according to your answers and others, this limitation is always there even the document said "obsolete" ? Just want to double confirm.
Thanks! Qiulang At 2016-03-03 11:06:08, "Darren Duncan" <darren at darrenduncan.net> wrote: >On 2016-03-02 6:48 PM, ?? wrote: >>> A better way that is very similar is to use WHERE and LIMIT instead. > > >> I know that but as I just replied to this thread, if I do that I will then >> have to record each columns I use for each table I want scroll cursor. So >> from the implementation point of view, using LIMIT & OFFSET is easier. >> >> Qiulang > >You have to record the columns anyway in order to know what you're sorting >your >results by, this is just reuse. Or if not, then maybe you should bite the >bullet and record that extra info. > >If you don't want to do that in order to simplify things, then you live with >the >limitations and sluggishness of LIMIT and OFFSET, as those limitations are >systemic to LIMIT and OFFSET. > >-- Darren Duncan > >> At 2016-03-03 10:42:37, "Darren Duncan" <darren at darrenduncan.net> wrote: >>> On 2016-03-02 5:02 AM, ?? wrote: >>>> Here... said "Do not try to implement a scrolling window using LIMIT and >>>> OFFSET. Doing so will become sluggish as the user scrolls down toward the >>>> bottom of the list.?. But the page also said "This information is obsolete? >>>> ... talks about LIMIT & OFFSET, without mentioning that is a bad idea. So >>>> my question is can I do that or not (will it become sluggish if I do that) >>>> ? >>> >>> Using LIMIT and OFFSET is generally a bad idea, not just on performance but >>> also >>> logically. >>> >>> A better way that is very similar is to use WHERE and LIMIT instead. >>> >>> Assuming that you are going through pages consecutively, you know what rows >>> you've already seen, particularly in the prior page. >>> >>> Whatever columns you are sorting your result by, take the last row just >>> seen and >>> the query for the next page is found by saying WHERE > or < etc the field >>> values >>> for that last row. >>> >>> So you're sure to get the rows just after the ones you just saw, and later >>> pages >>> shouldn't be any slower than earlier ones. >>> >>> This approach is also resilient to arbitrary changes to the database between >>> page views so you don't either repeat rows or skip rows due to offset >>> mismatch. >>> >>> -- Darren Duncan > >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
