Sqlite will use different strategies for ASC and desc ordering and result set sizes. Perhaps one is creating a temp btree to order the results. I think explain query plan might help show exactly what sqlite is contributing to the memory consumption without the need for as much speculation. Not intended as a critical comment, just a thought in passing.
On Thu, May 31, 2018, 9:22 AM x <tam118...@hotmail.com> wrote: > Yes, I think you’re right. I suppose maybe it recognises the desc sequence > from page access. > > > > ________________________________ > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on > behalf of Andy Ling <andy.l...@s-a-m.com> > Sent: Thursday, May 31, 2018 3:57:27 PM > To: 'SQLite mailing list' > Subject: Re: [sqlite] This is driving me nuts > > > While that makes sense Clemens it again fails to explain why it does no > > caching when desc is stepped through first. I am at least satisfied that > it’s not > > something I should dwell on or, more the point, it isn’t down to a bug > in my > > code which is why I got embroiled in it in the first place. > > > > My understanding from what Clemens said, which might clarify. > > When ascending pages are read sequentially, so sequential mode is used and > the pages get saved in the cache, but when descending, pages are read in > reverse order which makes the cache think it's random so it doesn't keep > them. > > So if you do ascending first the cache gets filled and eats up memory. > Then when you do descending it gradually deletes all the pages from the > cache > and frees up the memory. If you do it the other way round, descending > uses one page worth of memory then ascending eats up enough for all the > pages. > > Andy Ling > > --------------------------------------------------------------------------------------- > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
