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.
For completeness here’s the ‘ac-dc’ figures for (int i=0; i<5; i++) { for (int j=0; j<10000000; j++) sqlite3_step(asc); std::cout << "After asc " << FreeMBs() << std::endl; for (int j=0; j<10000000; j++) sqlite3_step(desc); std::cout << "After desc " << FreeMBs() << std::endl; } 10mill Ascending - 10mill Descending After asc 12324 After desc 12334 After asc 11391 After desc 11434 After asc 10484 After desc 10484 After asc 9534 After desc 9535 After asc 8585 After desc 8586 Tom ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Clemens Ladisch <clem...@ladisch.de> Sent: Thursday, May 31, 2018 2:06:08 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] This is driving me nuts x wrote: > Why would window’s increase it’s cache size during step ascending then > reduce it during step descending in such a consistent manner? Windows has several different caching strategies. <https://msdn.microsoft.com/en-us/library/windows/desktop/aa363858.aspx> says: | FILE_FLAG_RANDOM_ACCESS | | Access is intended to be random. The system can use this as a hint to | optimize file caching. | | FILE_FLAG_SEQUENTIAL_SCAN | | Access is intended to be sequential from beginning to end. The system | can use this as a hint to optimize file caching. | | This flag should not be used if read-behind (that is, reverse scans) | will be used. | | If none of these flags is specified, the system uses a default general- | purpose caching scheme. SQLite does not specify these flags. Apparently, the default general-purpose caching scheme tries to detect what access pattern the application uses. The rows are stored in rowid order in the file, so the ascending scan looks as if it might be a sequential scan. This means that Windows will prefetch following data, but it does not know if some data will be read again later, so it will keep the data in the cache. The descending scan looks like a reverse scan. By that point, Windows probably assumes that you are definitely using sequential scans, so it thinks the data can be thrown out of the cache after you've read it. Regards, Clemens _______________________________________________ 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