First one is expensive and s cond is inefficient. Instead get the max and min and split the numbers into appropriate batch. So if it is 345 to 25636 then 345 - 1345, 1346-2345 and so on and so forth. Why is the second querry inefficient because the IN clause is much slower than doing a between on the primary key
On Thu, Aug 8, 2024, 9:23 AM Guido Brugnara <g...@leader.it> wrote: > Il 08/08/24 15:35, Vincent Veyron ha scritto: > > On Thu, 8 Aug 2024 13:11:18 +0200 > Guido Brugnara <g...@leader.it> <g...@leader.it> wrote: > > Hi Guido, > > > > Special feature of the project is the use of HTML::Mason also for SQL > code generation templates for efficient paging of very large recordset > (even millions of rows). > > > > Excuse me for hijacking this thread, but I'm curious how this works? > > I have this problem in my accounting application, which I solved by adding > > OFFSET $offset LIMIT 1000 > > to the query, plus links in the web page that point to the next/previous > offset (LIMIT is set at 1000 for browsers, which slow down after that). > > It works fine for a few thousand rows, it serves the required slice very > fast; but I do have to fetch the whole recordset every time so I suspect > fetching a million rows to only return 1000 won't be efficient. > > So, how do you a page an ordered recordset of a million rows? do you use a > cursor? > > > > I evaluate the query but returning only Primary Keys [*] using > OFFSET/LIMIT limiting to a multiple of rows to display but not too large, > which I save in cache. > > Then I run the queries using a subset of cached PKs like ... WHERE IN > (pkn, pkn+1, pkn+2, ...) that returns all columns, but only che current > page to display. > > Subsequent request will use the cached keys until they are in cache, or it > call [*] with different range .. and so on. > bye > gdo > > >
